Hi All,
I wrote the following query and it works well for some "error inventory"
reporting. Basically, it looks at query LL11 and "sums" the results of
a series of IIf functions. The LL11 query asks for today's date and the
last business date as inputs from the user, then based on the type of
error (business or IT) and the difference between the ]error date] field
and today's date entered by the user, it creates an array of 1's and 0's
which are then "summed" in the LL12 query which is as follows:
SELECT LL11_Inventory_Source_for_calculations.todaysdate,
Sum(LL11_Inventory_Source_for_calculations.NewInventory) AS
SumOfNewInventory,
Sum(LL11_Inventory_Source_for_calculations.BusinessInventory) AS
SumOfBusinessInventory,
Sum(LL11_Inventory_Source_for_calculations.PMHSInventory) AS
SumOfPMHSInventory,
Sum(LL11_Inventory_Source_for_calculations.TotalErr00to02days) AS
SumOfTotalErr00to02days,
Sum(LL11_Inventory_Source_for_calculations.TotalErr03to07days) AS
SumOfTotalErr03to07days,
Sum(LL11_Inventory_Source_for_calculations.TotalErr08to14days) AS
SumOfTotalErr08to14days,
Sum(LL11_Inventory_Source_for_calculations.TotalErrGT15days) AS
SumOfTotalErrGT15days
FROM LL11_Inventory_Source_for_calculations
GROUP BY LL11_Inventory_Source_for_calculations.todaysdate;
In case it matters, LL11 query is as follows:
SELECT [enter today's date] AS todaysdate, IIf(([Oustanding CED-MHS
Errors Inv]![Error Date]>=[enter last business date]) And ([Oustanding
CED-MHS Errors Inv]![Status]="MHS" Or [Oustanding CED-MHS Errors
Inv]![Status]="o" Or [Oustanding CED-MHS Errors Inv]![Status]="EA" Or
[Oustanding CED-MHS Errors Inv]![Status]="V" Or [Oustanding CED-MHS
Errors Inv]![Status]="E"),1,0) AS NewInventory, IIf(([Oustanding CED-MHS
Errors Inv]![Status]="o" Or [Oustanding CED-MHS Errors
Inv]![Status]="EA" Or [Oustanding CED-MHS Errors Inv]![Status]="v" Or
[Oustanding CED-MHS Errors Inv]![Status]="e"),1,0) AS BusinessInventory,
IIf(([Oustanding CED-MHS Errors Inv]![Status])="mhs",1,0) AS
PMHSInventory, IIf((DateDiff("d",[Oustanding CED-MHS Errors Inv]![Error
Date],[todaysdate])>=0 And (DateDiff("d",[Oustanding CED-MHS Errors
Inv]![Error Date],[todaysdate])<=2)),1,0) AS TotalErr00to02days,
IIf((DateDiff("d",[Oustanding CED-MHS Errors Inv]![Error
Date],[todaysdate])>=3 And (DateDiff("d",[Oustanding CED-MHS Errors
Inv]![Error Date],[todaysdate])<=7)),1,0) AS TotalErr03to07days,
IIf((DateDiff("d",[Oustanding CED-MHS Errors Inv]![Error
Date],[todaysdate])>=8 And (DateDiff("d",[Oustanding CED-MHS Errors
Inv]![Error Date],[todaysdate])<=14)),1,0) AS TotalErr08to14days,
IIf((DateDiff("d",[Oustanding CED-MHS Errors Inv]![Error
Date],[todaysdate])>=15),1,0) AS TotalErrGT15days
FROM [Oustanding CED-MHS Errors Inv]
WHERE (((IIf(([Oustanding CED-MHS Errors Inv]![Error Date]>=[enter last
business date]) And ([Oustanding CED-MHS Errors Inv]![Status]="MHS" Or
[Oustanding CED-MHS Errors Inv]![Status]="o" Or [Oustanding CED-MHS
Errors Inv]![Status]="EA" Or [Oustanding CED-MHS Errors
Inv]![Status]="V" Or [Oustanding CED-MHS Errors
Inv]![Status]="E"),1,0))<>0)) OR (((IIf(([Oustanding CED-MHS Errors
Inv]![Status]="o" Or [Oustanding CED-MHS Errors Inv]![Status]="EA" Or
[Oustanding CED-MHS Errors Inv]![Status]="v" Or [Oustanding CED-MHS
Errors Inv]![Status]="e"),1,0))<>0)) OR (((IIf(([Oustanding CED-MHS
Errors Inv]![Status])="mhs",1,0))<>0));
The problem is that I want to record the error inventory in a table. (I
purge resolved errors once a month, so I can never go back and compare
June to July inventory unless I save the values somewhere). I thought
that I could just make the LL12 query into an "append" query:
INSERT INTO InventoryMasterTable ( inventorydate, SumOfNewInventory,
SumOfBusinessInventory, SumOfPMHSInventory, SumOfTotalErr00to02days,
SumOfTotalErr03to07days, SumOfTotalErr08to14days, SumOfTotalErrGT15days
)
SELECT LL11_Inventory_Source_for_calculations.todaysdate,
Sum(LL11_Inventory_Source_for_calculations.NewInventory) AS
SumOfNewInventory,
Sum(LL11_Inventory_Source_for_calculations.BusinessInventory) AS
SumOfBusinessInventory,
Sum(LL11_Inventory_Source_for_calculations.PMHSInventory) AS
SumOfPMHSInventory,
Sum(LL11_Inventory_Source_for_calculations.TotalErr00to02days) AS
SumOfTotalErr00to02days,
Sum(LL11_Inventory_Source_for_calculations.TotalErr03to07days) AS
SumOfTotalErr03to07days,
Sum(LL11_Inventory_Source_for_calculations.TotalErr08to14days) AS
SumOfTotalErr08to14days,
Sum(LL11_Inventory_Source_for_calculations.TotalErrGT15days) AS
SumOfTotalErrGT15days
FROM LL11_Inventory_Source_for_calculations
GROUP BY LL11_Inventory_Source_for_calculations.todaysdate;
But I get an error that reads: "Couldn't append all the rows to the
table. MS Access set 1 field to null for type conversion failure."
When I go look at the InventoryMasterTable, I find that the
[inventorydate] field is null. This is what I don't understand. It is
a date field in the table. Why doesn't the date get inserted into the
table?
Thanks,
Pete Bradshaw
Fulfillment Business Services Production Support
CIGNA HealthCare
860.226.3019 (N:572.3019)
mailto:[EMAIL PROTECTED]
------------------------------------------------------------------------------CONFIDENTIALITY
NOTICE: If you have received this email in error, please immediately notify
the sender by e-mail at the address shown. This email transmission may contain
confidential information. This information is intended only for the use of the
individual(s) or entity to whom it is intended even if addressed incorrectly.
Please delete it from your files if you are not the intended recipient. Thank
you for your compliance. Copyright 2005 CIGNA
==============================================================================
[Non-text portions of this message have been removed]
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
