Ridiculous query - blows up database, need help figuring out why

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;
 
K

Kelii

Jerry,

Fair point, will take your advice and backup today. I'll also look into
the webpages you referenced and see if i can "de"-corrupt the back end
of the application.

Any thoughts you may have re: referencing the primary query from a
subquery's subquery would be useful, assuming that makes sense.

Best,

Kelii
 
A

Allen Browne

Kelii, any chance of breaking the query down into stacked queries (i.e. a
query that uses another query as in input "table")?

That would mean your lower level subqueries were now only one step down from
the main query, so they can refer to those fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Kelii said:
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;
Click to expand...
 
K

Kelii

Allen,

Thank you for the reply.

I made an attempt along the lines you suggest early on, but was unable
to make any progress. The problem stems from the fact that the subquery
2 layers down references fields 2 layers up. For example (keep in mind
this is displayed as a datasheet):

SELECT rs1.var1,
SELECT rs2.var1,
SELECT rs3.var1
FROM table1 as rs3
WHERE rs3.var1 = rs1.var1
FROM ....
WHERE ...
FROM ...
WHERE ...;

I believe the reference in the subquery's subquery (i.e., WHERE
rs3.var1 = rs1.var1) is the cause of the database "blow up" and shut
down. That said, I am able to reference the query from the subquery, so
I am very much unsure of myself. Furthermore, I believe that this
reference prevents me from creating separate queries as you suggest.

Any thoughts would be of help.

Kelii
 
A

Allen Browne

The issue is that you can refer to a field in a subquery's parent query, but
you cannot go back another generation to refer to a field in its
grand-parent query.

The solution is therefore to redesign the query so you don't need to go back
2 generations. It might be possible to do that by using JOINs instead of
subqueries, by using 2 parallel subqueries instead of a child and
grandchild, by combining data from the tables into one query and using that
as a source for another query that has one level of subquery, etc.

At worst, you will need a temporary table that contains the crucial fields
from the first 2 queries, so you can then refer to those fields in the final
query's subquery.
 
K

Kelii

Allen,

This is very good and very bad news. Very good, b/c you confirmed my
suspicion, and now I have a path forward. Very bad, b/c I think that
path will take me at least another two days to complete and debug.

I'll post my final query (assuming I can complete it) here when its
complete and working for future reference should anyone care.

Thanks again for all your help,

Kelii
 
K

Kelii

As I promised should anyone be interested in the future. This query
seems to work after some testing against manual calculations.

SELECT Sum(FifoDetail.InventoryValue) AS FifoValue,
FifoDetail.Item_Description_ID
FROM [SELECT
RS3.Invoice_Date, RS3.Invoice_ID, RS4.Item_Description_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=RS4.Item_Description_ID AND
RS1.Invoice_Date>= RS3.Invoice_Date AND
RS1.Invoice_Date<=[Forms]![frmInventoryReportMaster]![Inventory_End_Date]
) AS RunTotal,
IIf(RS7.Inventory_Item_Count-[RunTotal]>0,RS4.Quantity_Purchased,RS7.Inventory_Item_Count-([RunTotal]-RS4.Quantity_Purchased))
AS Remainder, IIf([Remainder]>0,[Remainder]*[UnitPrice],0) AS
InventoryValue, RS7.Inventory_Item_Count
FROM
(tblItemDetails RS5 INNER JOIN (tblInvoiceMaster AS RS3 INNER JOIN
tblInvoiceDetails AS RS4 ON RS3.Invoice_ID = RS4.Invoice_ID) ON
RS5.Item_Description_ID = RS4.Item_Description_ID) INNER JOIN
(tblInventoryMaster RS6 INNER JOIN tblInventoryDetails RS7 ON
RS6.Inventory_ID = RS7.Inventory_ID) ON RS5.Item_Description_ID =
RS7.Item_Description_ID
WHERE
RS3.Invoice_Date<=[Forms]![frmInventoryReportMaster]![Inventory_End_Date]
AND
RS3.Company_Location=[Forms]![frmInventoryReportMaster]![Company_Location_Select]
AND
RS6.Company_Location=[Forms]![frmInventoryReportMaster]![Company_Location_Select]
AND
RS6.Inventory_Date=[Forms]![frmInventoryReportMaster]![Inventory_End_Date]
ORDER BY
RS3.Invoice_Date DESC , RS3.Invoice_ID DESC
]. AS FifoDetail
GROUP BY FifoDetail.Item_Description_ID;
 
Top