Syntax for complex nested pricing query

[keliie]

Hello,

As an Access novice, I'm still getting up to speed on proper syntax for
nesting queries in SQL. I have 3 queries, the last two of which build
on each of the former queries. It took me forever to get the right
dynaset from these queries; however, it is quite likely that they are
overcomplicated.

My question is how does one reference the original query when the
second query includes both the original query as well as tables which
are "new" to the query (see SQL below).

Secondarily, is it better practice to build such a complex query in SQL
or simply copy, rename, and modify the original query for forms which
use the query? Note here that this generic pricing query will be
further modified (in multiple Access forms) using criteria inputed by
the user via combo boxes.

qryzGenericPriceSQL1:
SELECT tblInvoiceDetails.Item_Description_ID,
Max(tblInvoiceMaster.Invoice_Date) AS MaxOfInvoice_Date,
tblItemDetails.Item_Unit_of_Measure,
tblItemDetails.Menu_Unit_of_Measure, tblItemDetails.Menu_UOM_Conversion
FROM tblItemDetails INNER JOIN (tblInvoiceMaster INNER JOIN
tblInvoiceDetails ON tblInvoiceMaster.Invoice_ID =
tblInvoiceDetails.Invoice_ID) ON tblItemDetails.Item_Description_ID =
tblInvoiceDetails.Item_Description_ID
GROUP BY tblInvoiceDetails.Item_Description_ID,
tblItemDetails.Item_Unit_of_Measure,
tblItemDetails.Menu_Unit_of_Measure,
tblItemDetails.Menu_UOM_Conversion;

qryzGenericPriceSQL2:
SELECT qryzGenericPriceSQL1.Item_Description_ID,
Max(tblInvoiceDetails.Invoice_Item_ID) AS MaxOfInvoice_Item_ID,
qryzGenericPriceSQL1.Item_Unit_of_Measure,
qryzGenericPriceSQL1.Menu_Unit_of_Measure,
qryzGenericPriceSQL1.Menu_UOM_Conversion
FROM (qryzGenericPriceSQL1 INNER JOIN tblInvoiceMaster ON
qryzGenericPriceSQL1.MaxOfInvoice_Date = tblInvoiceMaster.Invoice_Date)
INNER JOIN tblInvoiceDetails ON (tblInvoiceMaster.Invoice_ID =
tblInvoiceDetails.Invoice_ID) AND
(qryzGenericPriceSQL1.Item_Description_ID =
tblInvoiceDetails.Item_Description_ID)
GROUP BY qryzGenericPriceSQL1.Item_Description_ID,
qryzGenericPriceSQL1.Item_Unit_of_Measure,
qryzGenericPriceSQL1.Menu_Unit_of_Measure,
qryzGenericPriceSQL1.Menu_UOM_Conversion;

qryzGenericPriceSQL3:
SELECT qryzGenericPriceSQL2.Item_Description_ID,
tblInvoiceDetails.Quantity_Purchased,
tblInvoiceDetails.Item_Total_Price,
[Item_Total_Price]/[Quantity_Purchased] AS LastPrice_Per_Unit,
qryzGenericPriceSQL2.Item_Unit_of_Measure,
qryzGenericPriceSQL2.Menu_Unit_of_Measure,
qryzGenericPriceSQL2.Menu_UOM_Conversion,
qryzGenericPriceSQL2.MaxOfInvoice_Item_ID AS Invoice_Item_ID
FROM tblInvoiceMaster INNER JOIN (qryzGenericPriceSQL2 INNER JOIN
tblInvoiceDetails ON (qryzGenericPriceSQL2.MaxOfInvoice_Item_ID =
tblInvoiceDetails.Invoice_Item_ID) AND
(qryzGenericPriceSQL2.Item_Description_ID =
tblInvoiceDetails.Item_Description_ID)) ON tblInvoiceMaster.Invoice_ID
= tblInvoiceDetails.Invoice_ID
GROUP BY qryzGenericPriceSQL2.Item_Description_ID,
tblInvoiceDetails.Quantity_Purchased,
tblInvoiceDetails.Item_Total_Price,
[Item_Total_Price]/[Quantity_Purchased],
qryzGenericPriceSQL2.Item_Unit_of_Measure,
qryzGenericPriceSQL2.Menu_Unit_of_Measure,
qryzGenericPriceSQL2.Menu_UOM_Conversion,
qryzGenericPriceSQL2.MaxOfInvoice_Item_ID;

Thanks in advance for any help you can provide.

Kelii

 

[keliie]

Gary,

Thanks for the response, I appreciate your effort. I'm actually
attempting to consolidate the three queries into one master query with
2 nested queries. I understand the basic syntax for doing this from
previous posts in this group, but I don't understand how to create the
nested queries given the complexity of the query that I'm working with.
Any additional help would be appreciated.

Best,

Kelii

 

[keliie]

Gary,

Thanks for the response. The addition of SQL in the field section of
the query is exactly what I was looking for .

The queries you provided worked with less complexity than the original
queries after some minor mods on my part (excellent work given zero
access to table structure).

Anyway, thanks again, I really appreciate your help; my home brew app
has benefited.

Kelii