Syntax for complex nested pricing query

  • Thread starter Thread starter keliie
  • Start date Start date
K

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
 
I'm going to make some (possibly
erroneous) assumptions....

-- it looks like you wanted "latest price"
for a specific "item"

qryLatestItemInvDate:

SELECT
D.Item_Description_ID AS ItemDescID,
Max(M.Invoice_Date) AS LatestItemInvDate
FROM
tblInvoiceMaster AS M
INNER JOIN
tblInvoiceDetails AS D
ON M.Invoice_ID = D.Invoice_ID
GROUP BY
D.Item_Description_ID;

qryLatestItemInvoicePrice

SELECT
D.Item_Description_ID,
D.Invoice_ID AS LatestInvID,
q.LatestItemInvDate,
[D].[Item_Total_Price]/[D].[Quantity_Purchased] AS LatestPrice
FROM
(tblInvoiceMaster AS M
INNER JOIN
tblInvoiceDetails AS D
ON
M.Invoice_ID = D.Invoice_ID)
INNER JOIN
qryLatestItemInvDate AS q
ON
(q.ItemDescID = D.Item_Description_ID)
AND
(M.Invoice_Date = q.LatestItemInvDate);

this last query will give you

-- the latest price for a specific item
-- the invoice it occurred on
-- the date of that invoice

assumptions suck...but I could imagine a master form
based on following query

SELECT
D.Invoice_Item_ID,
D.Invoice_ID,
M.Invoice_Date,
D.Item_Description_ID,
D.Quantity_Purchased,
D.Item_Total_Price,
D.Quantity_Purchased/D.Item_Total_Price AS ItemPrice
FROM
tblInvoiceMaster AS M
INNER JOIN
tblInvoiceDetails AS D
ON
M.Invoice_ID = D.Invoice_ID
ORDER BY
D.Invoice_ID,
D.Item_Description_ID;

a subform based on tblItemDetails
w/ master/child link on Item_Description_ID

and another subform based on "qryLatestItemInvoicePrice"
w/ master/child link on Item_Description_ID

you could imagine you easily providing your users
with methods to filter master form for

-- invoice date(s)
-- specific invoice
-- specific item

or, I could have completely misunderstood...
 
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
 
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.
Click to expand...
I'm sorry....I fade in and out of the newsgroup and don't always read
previous posts, so I may have missed earlier discussions.

In this conversation, you posed the following:

"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."


For me, a query is a tool for displaying my data in a form or report
for my users. A SELECT query never "stands alone." One does not
ever show the query itself to the user, only the results in a form or
report. It's purpose is driven by what the user needs to do to get
their job done.

-- create new data
-- edit/update previous data
-- drill down on old data to show in meaningful ways

I don't know your data or what your users want, but assumed
from what I could gather from your post that your users would
not be able to accomplish the first 2 things above from your
complex query.

To create new data and to edit/update previous data with your
table stucture (or the structure I *think* you have) typically involves
forms with subforms...not a hairy complex query.

There is no reason (that is evident to me...I could be wrong)
for any group by's on the editable/updateable portion of your
data if the latest price for an item was what you
were trying to accomplish. That single value can be obtained
in "divide-and-conquer" queries like I suggested, then included
in your results in several ways:

-- a subform on a main form if you wanted all 3 values

-- a textbox on a form whose source is a domain function
that returns only the latest price from the final query
I provided

-- an "overall query" that either

1) includes your 3 tables and the final query I provided

2) includes your 3 tables and uses a correlated subquery
on the final query I provided to get the latest price

Did you test the 2 queries I provided?

If the final query did provide "latest item price," then I would think
that for an "overall query" for say some report the most efficient
would be number 1 above.

Start with a simple SELECT query with your 3 tables.
You have created this complex query so I will assume you
can easily do that. Verify that is working correctly.

Then, in query design of this query, right-mouse click
in an empty space in window where your tables are,
then choose "Add Table" from menu.

Choose to add query "qryLatestItemInvoicePrice"

Create a join line between table and query.
Drag-and-drop tblInvoiceDetails.Item_Description_ID
over on qryLatestItemInvoicePrice.Item_Description_ID

Then, double-click on field qryLatestItemInvoicePrice.LatestPrice
to add it to the grid below.

Does this "overall query" perform as you expected?

If not, try using correlated subquery.

Start again with query using your 3 tables in a simple
SELECT query (no group by's).

On a "Field" row in a blank column of the grid, type
the following all as one line (will break up here in post):

ItemLatestPrice: (SELECT q.LatestPrice FROM
qryLatestItemInvoicePrice AS q
WHERE q.Item_Description_ID = tblInvoiceDetails.Item_Description_ID)

another alternative is an even slower domain function...
(again, all one continuous line but will break up here)

LatestPrice: DLookUp("LatestPrice","qryLatestItemInvoicePrice",
"[Item_Description_ID]=" & tblInvoiceDetails.Item_Description_ID)

I have mostly replied to your second question and made
some assumptions about your data...

Apologies if I have misunderstood...

Plus, I have not given advice about your table structure...
but, maybe that is too late now...
 
Gary,

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

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
 
Back
Top