K
Kelii
Hi everyone,
This is a FIFO inventory valuation query, that in theory should work,
but doesn't. I need help figuring out why and determing how to fix it.
My query contains a subquery, which in turn contains another subquery.
The subquery's subquery contains a reference (in the WHERE clause) to
the primary query. Upon running the query, Access prompts the user for
these criteria (i.e., the subquery's subquery references to the primary
query). After these prompts, Access shuts down; my suspicion is that
these references to the primary query is what is causing Access to blow
up.
As a secondary question, is there a more efficient approach to building
this query? I don't think that anyone would want to dig into this piece
of my confusion, but thoughts are welcome.
Background/structure:
tblInventoryMaster
Inventory_ID (1 to M link to tblInventoryDetails)
Inventory_Date
Inventory_Location
tblInventoryDetails
Inventory_ID (M to 1 link to tblInventoryMaster)
Item_Description_ID (M to 1 link to tblItemDetails)
Inventory_Item_Count
tblItemDetails
Item_Description_ID (1 to M link to both tblInventoryDetails and
tblInvoiceDetails)
Item_Unit_Of_Measure
Various other stuff
tblInvoiceMaster
Invoice_ID (1 to M link to tblInvoiceDetails)
Company_Location
Invoice_Date
Various other stuff
tblInvoiceDetails
Invoice_ID (M to 1 link to tblInvoiceMaster)
Invoice_Item_ID
Item_Description_ID (M to 1 link to tblItemDetails)
Quantity_Purchased
Item_Total_Price
Current state of query (note some of the where references refer to the
display form):
SELECT
RS7.Item_Category, RS7.Item_Type, RS7.Item_Location,
RS6.Item_Description_ID, RS6.Inventory_Item_Count,
RS7.Item_Unit_of_Measure,
(
SELECT
SUM(InventoryValue)
FROM
(
SELECT
RS3.Invoice_Date, RS3.Invoice_ID, RS4.Quantity_Purchased,
RS4.Item_Total_Price, RS4.Item_Total_Price/RS4.Quantity_Purchased AS
UnitPrice,
(
SELECT
SUM(RS2.Quantity_Purchased)
FROM
tblInvoiceMaster RS1 INNER JOIN tblInvoiceDetails RS2 ON RS1.Invoice_ID
= RS2.Invoice_ID
WHERE
RS1.Company_Location =
[Forms]![frmInventoryReportMaster]![Company_Location_Select] AND
RS2.Item_Description_ID = RS6.Item_Description_ID AND
RS1.Invoice_Date >= RS5.Invoice_Date AND
RS1.Invoice_Date <=
[Forms]![frmInventoryReportMaster]![Inventory_End_Date]
)
AS RunTotal,
IIf(RS6.Inventory_Item_Count-[RunTotal]>0,[Quantity_Purchased],
RS6.Inventory_Item_Count -([RunTotal]-[Quantity_Purchased])) AS
Remainder, IIf([Remainder]>0,[Remainder]*[UnitPrice],0) AS
InventoryValue
FROM
tblInvoiceMaster RS3 INNER JOIN tblInvoiceDetails RS4 ON RS3.Invoice_ID
= RS4.Invoice_ID
WHERE
RS3.Company_Location =
[Forms]![frmInventoryReportMaster]![Company_Location_Select] AND
RS4.Item_Description_ID = RS6.Item_Description_ID AND
RS3.Invoice_Date<=[Forms]![frmInventoryReportMaster]![Inventory_End_Date]
ORDER BY
RS3.Invoice_Date DESC , RS3.Invoice_ID DESC
)
) AS TotalInventoryValue
FROM
tblItemDetails RS7 INNER JOIN (tblInventoryMaster RS5 INNER JOIN
tblInventoryDetails RS6 ON RS5.Inventory_ID = RS6.Inventory_ID) ON
RS7.Item_Description_ID = RS6.Item_Description_ID
WHERE
RS5.Company_Location=[Forms]![frmInventoryReportMaster]![Company_Location_Select]
AND
RS5.Inventory_Date=[Forms]![frmInventoryReportMaster]![Inventory_End_Date]
GROUP BY
RS7.Item_Category, RS7.Item_Type, RS7.Item_Location,
RS6.Item_Description_ID, RS6.Inventory_Item_Count,
RS7.Item_Unit_of_Measure
ORDER BY
RS7.Item_Category, RS7.Item_Type, RS7.Item_Location,
RS6.Item_Description_ID;
This is a FIFO inventory valuation query, that in theory should work,
but doesn't. I need help figuring out why and determing how to fix it.
My query contains a subquery, which in turn contains another subquery.
The subquery's subquery contains a reference (in the WHERE clause) to
the primary query. Upon running the query, Access prompts the user for
these criteria (i.e., the subquery's subquery references to the primary
query). After these prompts, Access shuts down; my suspicion is that
these references to the primary query is what is causing Access to blow
up.
As a secondary question, is there a more efficient approach to building
this query? I don't think that anyone would want to dig into this piece
of my confusion, but thoughts are welcome.
Background/structure:
tblInventoryMaster
Inventory_ID (1 to M link to tblInventoryDetails)
Inventory_Date
Inventory_Location
tblInventoryDetails
Inventory_ID (M to 1 link to tblInventoryMaster)
Item_Description_ID (M to 1 link to tblItemDetails)
Inventory_Item_Count
tblItemDetails
Item_Description_ID (1 to M link to both tblInventoryDetails and
tblInvoiceDetails)
Item_Unit_Of_Measure
Various other stuff
tblInvoiceMaster
Invoice_ID (1 to M link to tblInvoiceDetails)
Company_Location
Invoice_Date
Various other stuff
tblInvoiceDetails
Invoice_ID (M to 1 link to tblInvoiceMaster)
Invoice_Item_ID
Item_Description_ID (M to 1 link to tblItemDetails)
Quantity_Purchased
Item_Total_Price
Current state of query (note some of the where references refer to the
display form):
SELECT
RS7.Item_Category, RS7.Item_Type, RS7.Item_Location,
RS6.Item_Description_ID, RS6.Inventory_Item_Count,
RS7.Item_Unit_of_Measure,
(
SELECT
SUM(InventoryValue)
FROM
(
SELECT
RS3.Invoice_Date, RS3.Invoice_ID, RS4.Quantity_Purchased,
RS4.Item_Total_Price, RS4.Item_Total_Price/RS4.Quantity_Purchased AS
UnitPrice,
(
SELECT
SUM(RS2.Quantity_Purchased)
FROM
tblInvoiceMaster RS1 INNER JOIN tblInvoiceDetails RS2 ON RS1.Invoice_ID
= RS2.Invoice_ID
WHERE
RS1.Company_Location =
[Forms]![frmInventoryReportMaster]![Company_Location_Select] AND
RS2.Item_Description_ID = RS6.Item_Description_ID AND
RS1.Invoice_Date >= RS5.Invoice_Date AND
RS1.Invoice_Date <=
[Forms]![frmInventoryReportMaster]![Inventory_End_Date]
)
AS RunTotal,
IIf(RS6.Inventory_Item_Count-[RunTotal]>0,[Quantity_Purchased],
RS6.Inventory_Item_Count -([RunTotal]-[Quantity_Purchased])) AS
Remainder, IIf([Remainder]>0,[Remainder]*[UnitPrice],0) AS
InventoryValue
FROM
tblInvoiceMaster RS3 INNER JOIN tblInvoiceDetails RS4 ON RS3.Invoice_ID
= RS4.Invoice_ID
WHERE
RS3.Company_Location =
[Forms]![frmInventoryReportMaster]![Company_Location_Select] AND
RS4.Item_Description_ID = RS6.Item_Description_ID AND
RS3.Invoice_Date<=[Forms]![frmInventoryReportMaster]![Inventory_End_Date]
ORDER BY
RS3.Invoice_Date DESC , RS3.Invoice_ID DESC
)
) AS TotalInventoryValue
FROM
tblItemDetails RS7 INNER JOIN (tblInventoryMaster RS5 INNER JOIN
tblInventoryDetails RS6 ON RS5.Inventory_ID = RS6.Inventory_ID) ON
RS7.Item_Description_ID = RS6.Item_Description_ID
WHERE
RS5.Company_Location=[Forms]![frmInventoryReportMaster]![Company_Location_Select]
AND
RS5.Inventory_Date=[Forms]![frmInventoryReportMaster]![Inventory_End_Date]
GROUP BY
RS7.Item_Category, RS7.Item_Type, RS7.Item_Location,
RS6.Item_Description_ID, RS6.Inventory_Item_Count,
RS7.Item_Unit_of_Measure
ORDER BY
RS7.Item_Category, RS7.Item_Type, RS7.Item_Location,
RS6.Item_Description_ID;