Matching results in query based on periodically updated field

[keliie]

Hello,

I'm building a query that shows the total sales value of our company's
widgets over time. I have the following tables:
1. A table with the various widget types
2. A table with units sold for each widget type by date
3. A table with prices for each widget type (including date price was
entered)

My problem is that my query is returning multiple results for units
sold each time the price is updated. For example:
- Assume Widget A is sold on Day 1 and Day 2
- Assume Widget A's price is updated on Day 1 (i.e., Price 1), and
again on Day 2 (i.e., Price 2)

Query results show the following records:
- Widget A sales on Day 1 at Price 1
- Widget A sales on Day 1 at Price 2
- Widget A sales on Day 2 at Price 1
- Widget A sales on Day 2 at Price 2

Having only a basic understanding of Queries, I understand the problem
that I've encountered, however I haven't been able to solve it. I've
tried using a combination of criteria (e.g., Price Date >= Sale Date),
Total functions (e.g., Last as opposed to Group By), selecting only
distinct rows, and modification of join properties. However, I haven't
had any success.

I know there must be an easy answer, can someone point me in the right
direction?

Thanks,

Kelii

SQL:
SELECT tblMenuMaster.Menu_Category, tblMenuMaster.Menu_Type,
tblMenuMaster.Menu_Description_ID, tblSalesMaster.Sale_Date,
tblSalesDetails.Menu_Item_Count_Sold, tblMenuPrice.Menu_Price_Date,
tblMenuPrice.Menu_Item_Price
FROM (tblMenuMaster INNER JOIN tblMenuPrice ON
tblMenuMaster.Menu_Description_ID = tblMenuPrice.Menu_Description_ID)
INNER JOIN (tblSalesMaster INNER JOIN tblSalesDetails ON
tblSalesMaster.Sales_ID = tblSalesDetails.Sales_ID) ON
tblMenuMaster.Menu_Description_ID = tblSalesDetails.Menu_Description_ID
GROUP BY tblMenuMaster.Menu_Category, tblMenuMaster.Menu_Type,
tblMenuMaster.Menu_Description_ID, tblSalesMaster.Sale_Date,
tblSalesDetails.Menu_Item_Count_Sold, tblMenuPrice.Menu_Price_Date,
tblMenuPrice.Menu_Item_Price
HAVING (((tblMenuMaster.Menu_Description_ID)="458 THE PAGODA") AND
((tblMenuPrice.Menu_Price_Date)<=[Sale_Date]));

 

[Vincent Johns]

I claim that my suggestion here is relatively "easy" (depending on how
you look at it). It's perhaps longer than it needs to be, but I broke
the Query into pieces that would be easy to understand and edit, and I
hope the results make sense.

To begin with, I set up some example Tables.

Since I usually reserve field names ending in "_ID" for key values, and
since I decided to set a different key in the first Table (you didn't
specify which fields were intended to be primary keys for their
respective Tables), I renamed [Menu_Description_ID] to [Menu_Description].

[tblMenuMaster] Table Datasheet View:

tblMenu Menu_ Menu_Type Menu_Description
Master_ID Category
----------- -------- --------- ------------------
-1803305685 Entrée Executive 444 SHRIMP SCAMPI
640447535 Dessert Executive 458 THE PAGODA

[tblMenuPrice] Table Datasheet View:

tblMenu Menu_Price Menu_Item tblMenuMaster_ID
Price_ID _Date _Price
---------- --------- --------- ----------------
116565572 3/5/2006 $8.00 640447535
1289018728 3/1/2006 $12.00 -1803305685
1997033973 3/15/2006 $10.00 640447535

In the following Table, record -798936239 is intentionally dated before
any records in the [tblMenuPrice] Table, so it will not appear in the
results.

[tblSalesDetails] Table Datasheet View:

tblSales Menu_Item_ Sales_ID tblMenu
Details_ID Count_Sold Master_ID
----------- ---------- ----------- -----------
-1132871059 5 -868623460 -1803305685
-1109556637 10 -1068164310 640447535
-798936239 20 464445350 640447535
-631732718 10 -193934530 640447535
307649353 20 -193934530 -1803305685
719118992 30 1743613415 640447535

I would have called the primary key of this Table [tblSalesMaster_ID],
to help make it obvious which Table it belonged to, but it looked as if
[Sales_ID] was the name you chose for the primary key, so I left it alone.

[tblSalesMaster] Table Datasheet View:

Sales_ID Sale_Date
----------- ---------
-1068164310 3/8/2006
-868623460 3/12/2006
-193934530 3/10/2006
464445350 3/1/2006
1743613415 3/20/2006

With the Tables populated, I set up a Query to identify, for each
transaction, the date on which the price for that transaction became
effective.

[Q_01MenuDate] SQL:

SELECT MM.tblMenuMaster_ID, SM.Sale_Date,
SD.Menu_Item_Count_Sold, Max(MP.Menu_Price_Date)
AS ChangeDate
FROM tblSalesMaster AS SM
INNER JOIN ((tblMenuMaster AS MM
INNER JOIN tblMenuPrice AS MP
ON MM.tblMenuMaster_ID = MP.tblMenuMaster_ID)
INNER JOIN tblSalesDetails AS SD
ON MM.tblMenuMaster_ID = SD.tblMenuMaster_ID)
ON SM.Sales_ID = SD.Sales_ID
WHERE (((MP.Menu_Price_Date)<=[Sale_Date]))
GROUP BY MM.tblMenuMaster_ID, SM.Sale_Date,
SD.Menu_Item_Count_Sold
ORDER BY SM.Sale_Date;

[Q_01MenuDate] Query Datasheet View:

tblMenuMaster_ID Sale_Date Menu_Item_ ChangeDate
Count_Sold
---------------- --------- ---------- ----------
640447535 3/8/2006 10 3/5/2006
-1803305685 3/10/2006 20 3/1/2006
640447535 3/10/2006 10 3/5/2006
-1803305685 3/12/2006 5 3/1/2006
640447535 3/20/2006 30 3/15/2006

The next Query builds on this by fetching the sale price, as of that
date, from the [tblMenuPrice] Table. The [Sale_Date] field is actually
no longer needed, but I included it to make the results easier to read
(and, presumably, to audit).

[Q_02SalesPrices] SQL:

SELECT Q01.tblMenuMaster_ID, Q01.Sale_Date,
Q01.Menu_Item_Count_Sold, MP.Menu_Item_Price
FROM Q_01MenuDate AS Q01
INNER JOIN tblMenuPrice AS MP
ON Q01.ChangeDate = MP.Menu_Price_Date
WHERE (((MP.Menu_Price_Date)<=[Sale_Date]))
GROUP BY Q01.tblMenuMaster_ID,
Q01.Sale_Date, Q01.Menu_Item_Count_Sold,
MP.Menu_Item_Price;


[Q_02SalesPrices] Query Datasheet View:

tblMenuMaster_ID Sale_Date Menu_Item_ Menu_Item
Count_Sold _Price
---------------- --------- ---------- ---------
-1803305685 3/10/2006 20 $12.00
-1803305685 3/12/2006 5 $12.00
640447535 3/8/2006 10 $8.00
640447535 3/10/2006 10 $8.00
640447535 3/20/2006 30 $10.00

The next Query multiplies the number of items sold by the unit price and
adds the results. It also filters the results so that only the item
"458 THE PAGODA" is listed.

[Q_03TotalSales]

SELECT MM.Menu_Category, MM.Menu_Type,
MM.Menu_Description,
Sum([Q02]![Menu_Item_Count_Sold]
*[Q02]![Menu_Item_Price]) AS Revenue
FROM Q_02SalesPrices AS Q02
INNER JOIN tblMenuMaster AS MM
ON Q02.tblMenuMaster_ID = MM.tblMenuMaster_ID
GROUP BY MM.Menu_Category, MM.Menu_Type,
MM.Menu_Description
HAVING (((MM.Menu_Description)="458 THE PAGODA"))
ORDER BY MM.Menu_Description;

[Q_03TotalSales] Query Datasheet View:

Menu_Category Menu_Type Menu_Description Revenue
------------- --------- ---------------- -------
Dessert Executive 458 THE PAGODA $460.00



-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Hello,

I'm building a query that shows the total sales value of our company's
widgets over time. I have the following tables:
1. A table with the various widget types
2. A table with units sold for each widget type by date
3. A table with prices for each widget type (including date price was
entered)

My problem is that my query is returning multiple results for units
sold each time the price is updated. For example:
- Assume Widget A is sold on Day 1 and Day 2
- Assume Widget A's price is updated on Day 1 (i.e., Price 1), and
again on Day 2 (i.e., Price 2)

Query results show the following records:
- Widget A sales on Day 1 at Price 1
- Widget A sales on Day 1 at Price 2
- Widget A sales on Day 2 at Price 1
- Widget A sales on Day 2 at Price 2

Having only a basic understanding of Queries, I understand the problem
that I've encountered, however I haven't been able to solve it. I've
tried using a combination of criteria (e.g., Price Date >= Sale Date),
Total functions (e.g., Last as opposed to Group By), selecting only
distinct rows, and modification of join properties. However, I haven't
had any success.

I know there must be an easy answer, can someone point me in the right
direction?

Thanks,

Kelii

SQL:
SELECT tblMenuMaster.Menu_Category, tblMenuMaster.Menu_Type,
tblMenuMaster.Menu_Description_ID, tblSalesMaster.Sale_Date,
tblSalesDetails.Menu_Item_Count_Sold, tblMenuPrice.Menu_Price_Date,
tblMenuPrice.Menu_Item_Price
FROM (tblMenuMaster INNER JOIN tblMenuPrice ON
tblMenuMaster.Menu_Description_ID = tblMenuPrice.Menu_Description_ID)
INNER JOIN (tblSalesMaster INNER JOIN tblSalesDetails ON
tblSalesMaster.Sales_ID = tblSalesDetails.Sales_ID) ON
tblMenuMaster.Menu_Description_ID = tblSalesDetails.Menu_Description_ID
GROUP BY tblMenuMaster.Menu_Category, tblMenuMaster.Menu_Type,
tblMenuMaster.Menu_Description_ID, tblSalesMaster.Sale_Date,
tblSalesDetails.Menu_Item_Count_Sold, tblMenuPrice.Menu_Price_Date,
tblMenuPrice.Menu_Item_Price
HAVING (((tblMenuMaster.Menu_Description_ID)="458 THE PAGODA") AND
((tblMenuPrice.Menu_Price_Date)<=[Sale_Date]));

 

[keliie]

Vincent,

You're a rockstar, thanks for the detailed response. I'm going to spend
a good portion of the day deciphering and implementing your suggestion.
I'll let you know how it goes.

Best,

Kelii

 

[Vincent Johns]

Kelii,

Yes, I'll be interested to hear how you make out with it.

Something I didn't mention is that, almost always, I set a Lookup
property on each foreign key (such as [tblMenuPrice].[tblMenuMaster_ID])
to make the Table Datasheet View (much) easier to read and debug. I
didn't do that in my previous message because I wasn't sure if I guessed
correctly about which fields were keys.

Had I defined Lookup properties in the examples I showed you, the two
Tables with foreign keys would have looked like the following, instead
of showing those raw key values (which are usually meaningless to human
beings):

[tblMenuPrice] Table Datasheet View (with Lookups):

tblMenu Menu_Price Menu_Item tblMenuMaster_ID
Price_ID _Date _Price
---------- --------- --------- ----------------
116565572 3/5/2006 $8.00 458 THE PAGODA
1289018728 3/1/2006 $12.00 444 SHRIMP SCAMPI
1997033973 3/15/2006 $10.00 458 THE PAGODA


[tblSalesDetails] Table Datasheet View (with Lookups):

tblSales Menu_Item_ Sales_ID tblMenu
Details_ID Count_Sold Master_ID
----------- ---------- ----------- -----------
-1132871059 5 3/12/2006 444 SHRIMP SCAMPI
-1109556637 10 3/8/2006 458 THE PAGODA
-798936239 20 3/1/2006 458 THE PAGODA
-631732718 10 3/10/2006 458 THE PAGODA
307649353 20 3/10/2006 444 SHRIMP SCAMPI
719118992 30 3/20/2006 458 THE PAGODA

Some people dislike using Lookup properties; the most compelling reason
I've seen is that it's easy to be fooled into thinking that the lookup
value you see is what's stored in the Table, but it's not -- the key
value is still what's in the Table. (I didn't change the contents of
these Tables at all, only how their Datasheets look.) Also, Lookups are
useful mainly in Table or Query Datasheet Views, not in Forms or
Reports, and your users will likely not see the Datasheet Views.

Anyway, there is no need to use Lookup properties, but if you like what
you see, I'll be happy to provide details.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Vincent,

You're a rockstar, thanks for the detailed response. I'm going to spend
a good portion of the day deciphering and implementing your suggestion.
I'll let you know how it goes.

Best,

Kelii

I claim that my suggestion here is relatively "easy" (depending on how
you look at it). It's perhaps longer than it needs to be, but I broke
the Query into pieces that would be easy to understand and edit, and I
hope the results make sense.

To begin with, I set up some example Tables.

Since I usually reserve field names ending in "_ID" for key values, and
since I decided to set a different key in the first Table (you didn't
specify which fields were intended to be primary keys for their
respective Tables), I renamed [Menu_Description_ID] to [Menu_Description].

[tblMenuMaster] Table Datasheet View:

tblMenu Menu_ Menu_Type Menu_Description
Master_ID Category
----------- -------- --------- ------------------
-1803305685 Entrée Executive 444 SHRIMP SCAMPI
640447535 Dessert Executive 458 THE PAGODA

[tblMenuPrice] Table Datasheet View:

tblMenu Menu_Price Menu_Item tblMenuMaster_ID
Price_ID _Date _Price
---------- --------- --------- ----------------
116565572 3/5/2006 $8.00 640447535
1289018728 3/1/2006 $12.00 -1803305685
1997033973 3/15/2006 $10.00 640447535

In the following Table, record -798936239 is intentionally dated before
any records in the [tblMenuPrice] Table, so it will not appear in the
results.

[tblSalesDetails] Table Datasheet View:

tblSales Menu_Item_ Sales_ID tblMenu
Details_ID Count_Sold Master_ID
----------- ---------- ----------- -----------
-1132871059 5 -868623460 -1803305685
-1109556637 10 -1068164310 640447535
-798936239 20 464445350 640447535
-631732718 10 -193934530 640447535
307649353 20 -193934530 -1803305685
719118992 30 1743613415 640447535

I would have called the primary key of this Table [tblSalesMaster_ID],
to help make it obvious which Table it belonged to, but it looked as if
[Sales_ID] was the name you chose for the primary key, so I left it alone.

[tblSalesMaster] Table Datasheet View:

Sales_ID Sale_Date
----------- ---------
-1068164310 3/8/2006
-868623460 3/12/2006
-193934530 3/10/2006
464445350 3/1/2006
1743613415 3/20/2006

With the Tables populated, I set up a Query to identify, for each
transaction, the date on which the price for that transaction became
effective.

[Q_01MenuDate] SQL:

SELECT MM.tblMenuMaster_ID, SM.Sale_Date,
SD.Menu_Item_Count_Sold, Max(MP.Menu_Price_Date)
AS ChangeDate
FROM tblSalesMaster AS SM
INNER JOIN ((tblMenuMaster AS MM
INNER JOIN tblMenuPrice AS MP
ON MM.tblMenuMaster_ID = MP.tblMenuMaster_ID)
INNER JOIN tblSalesDetails AS SD
ON MM.tblMenuMaster_ID = SD.tblMenuMaster_ID)
ON SM.Sales_ID = SD.Sales_ID
WHERE (((MP.Menu_Price_Date)<=[Sale_Date]))
GROUP BY MM.tblMenuMaster_ID, SM.Sale_Date,
SD.Menu_Item_Count_Sold
ORDER BY SM.Sale_Date;

[Q_01MenuDate] Query Datasheet View:

tblMenuMaster_ID Sale_Date Menu_Item_ ChangeDate
Count_Sold
---------------- --------- ---------- ----------
640447535 3/8/2006 10 3/5/2006
-1803305685 3/10/2006 20 3/1/2006
640447535 3/10/2006 10 3/5/2006
-1803305685 3/12/2006 5 3/1/2006
640447535 3/20/2006 30 3/15/2006

The next Query builds on this by fetching the sale price, as of that
date, from the [tblMenuPrice] Table. The [Sale_Date] field is actually
no longer needed, but I included it to make the results easier to read
(and, presumably, to audit).

[Q_02SalesPrices] SQL:

SELECT Q01.tblMenuMaster_ID, Q01.Sale_Date,
Q01.Menu_Item_Count_Sold, MP.Menu_Item_Price
FROM Q_01MenuDate AS Q01
INNER JOIN tblMenuPrice AS MP
ON Q01.ChangeDate = MP.Menu_Price_Date
WHERE (((MP.Menu_Price_Date)<=[Sale_Date]))
GROUP BY Q01.tblMenuMaster_ID,
Q01.Sale_Date, Q01.Menu_Item_Count_Sold,
MP.Menu_Item_Price;


[Q_02SalesPrices] Query Datasheet View:

tblMenuMaster_ID Sale_Date Menu_Item_ Menu_Item
Count_Sold _Price
---------------- --------- ---------- ---------
-1803305685 3/10/2006 20 $12.00
-1803305685 3/12/2006 5 $12.00
640447535 3/8/2006 10 $8.00
640447535 3/10/2006 10 $8.00
640447535 3/20/2006 30 $10.00

The next Query multiplies the number of items sold by the unit price and
adds the results. It also filters the results so that only the item
"458 THE PAGODA" is listed.

[Q_03TotalSales]

SELECT MM.Menu_Category, MM.Menu_Type,
MM.Menu_Description,
Sum([Q02]![Menu_Item_Count_Sold]
*[Q02]![Menu_Item_Price]) AS Revenue
FROM Q_02SalesPrices AS Q02
INNER JOIN tblMenuMaster AS MM
ON Q02.tblMenuMaster_ID = MM.tblMenuMaster_ID
GROUP BY MM.Menu_Category, MM.Menu_Type,
MM.Menu_Description
HAVING (((MM.Menu_Description)="458 THE PAGODA"))
ORDER BY MM.Menu_Description;

[Q_03TotalSales] Query Datasheet View:

Menu_Category Menu_Type Menu_Description Revenue
------------- --------- ---------------- -------
Dessert Executive 458 THE PAGODA $460.00



-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Hello,

I'm building a query that shows the total sales value of our company's
widgets over time. I have the following tables:
1. A table with the various widget types
2. A table with units sold for each widget type by date
3. A table with prices for each widget type (including date price was
entered)

My problem is that my query is returning multiple results for units
sold each time the price is updated. For example:
- Assume Widget A is sold on Day 1 and Day 2
- Assume Widget A's price is updated on Day 1 (i.e., Price 1), and
again on Day 2 (i.e., Price 2)

Query results show the following records:
- Widget A sales on Day 1 at Price 1
- Widget A sales on Day 1 at Price 2
- Widget A sales on Day 2 at Price 1
- Widget A sales on Day 2 at Price 2

Having only a basic understanding of Queries, I understand the problem
that I've encountered, however I haven't been able to solve it. I've
tried using a combination of criteria (e.g., Price Date >= Sale Date),
Total functions (e.g., Last as opposed to Group By), selecting only
distinct rows, and modification of join properties. However, I haven't
had any success.

I know there must be an easy answer, can someone point me in the right
direction?

Thanks,

Kelii

SQL:
SELECT tblMenuMaster.Menu_Category, tblMenuMaster.Menu_Type,
tblMenuMaster.Menu_Description_ID, tblSalesMaster.Sale_Date,
tblSalesDetails.Menu_Item_Count_Sold, tblMenuPrice.Menu_Price_Date,
tblMenuPrice.Menu_Item_Price
FROM (tblMenuMaster INNER JOIN tblMenuPrice ON
tblMenuMaster.Menu_Description_ID = tblMenuPrice.Menu_Description_ID)
INNER JOIN (tblSalesMaster INNER JOIN tblSalesDetails ON
tblSalesMaster.Sales_ID = tblSalesDetails.Sales_ID) ON
tblMenuMaster.Menu_Description_ID = tblSalesDetails.Menu_Description_ID
GROUP BY tblMenuMaster.Menu_Category, tblMenuMaster.Menu_Type,
tblMenuMaster.Menu_Description_ID, tblSalesMaster.Sale_Date,
tblSalesDetails.Menu_Item_Count_Sold, tblMenuPrice.Menu_Price_Date,
tblMenuPrice.Menu_Item_Price
HAVING (((tblMenuMaster.Menu_Description_ID)="458 THE PAGODA") AND
((tblMenuPrice.Menu_Price_Date)<=[Sale_Date]));

 

[keliie]

Success.

Riffing on your suggestion, I went the route of creating two queries to
generate the data.

Query 1:
SELECT tblSalesMaster.Company_Location,
tblMenuMaster.Menu_Description_ID, tblSalesMaster.Sale_Date,
tblSalesDetails.Menu_Item_Count_Sold, Max(tblMenuPrice.Menu_Price_Date)
AS Change_Date
FROM (tblMenuMaster LEFT JOIN tblMenuPrice ON
tblMenuMaster.Menu_Description_ID = tblMenuPrice.Menu_Description_ID)
INNER JOIN (tblSalesMaster INNER JOIN tblSalesDetails ON
tblSalesMaster.Sales_ID = tblSalesDetails.Sales_ID) ON
tblMenuMaster.Menu_Description_ID = tblSalesDetails.Menu_Description_ID
WHERE (((tblMenuPrice.Menu_Price_Date)<=[Sale_Date]))
GROUP BY tblSalesMaster.Company_Location,
tblMenuMaster.Menu_Description_ID, tblSalesMaster.Sale_Date,
tblSalesDetails.Menu_Item_Count_Sold
HAVING (((tblMenuMaster.Menu_Description_ID)="458 THE PAGODA"));

Query 2:
SELECT DISTINCT qrySalesTrendBuild.Company_Location,
qrySalesTrendBuild.Menu_Description_ID, qrySalesTrendBuild.Sale_Date,
qrySalesTrendBuild.Menu_Item_Count_Sold,
qrySalesTrendBuild.Change_Date, tblMenuPrice.Menu_Item_Price,
Nz([qrySalesTrendBuild]![Menu_Item_Count_Sold])*Nz([Menu_Item_Price])
AS Ext_Sales_Value
FROM qrySalesTrendBuild INNER JOIN ((tblMenuMaster INNER JOIN
tblMenuPrice ON tblMenuMaster.Menu_Description_ID =
tblMenuPrice.Menu_Description_ID) INNER JOIN (tblSalesMaster INNER JOIN
tblSalesDetails ON tblSalesMaster.Sales_ID = tblSalesDetails.Sales_ID)
ON tblMenuMaster.Menu_Description_ID =
tblSalesDetails.Menu_Description_ID) ON (qrySalesTrendBuild.Change_Date
= tblMenuPrice.Menu_Price_Date) AND
(qrySalesTrendBuild.Menu_Description_ID =
tblMenuMaster.Menu_Description_ID);

While this is not exactly your suggested solution, I believe it is
quite close and uses most of the techniques you've suggested.

Thanks again for your help, after reading your post it took me about 30
minutes to get to this end product.

Best,

Kelii

 

[Vincent Johns]

Kelii,

Congratulations! (And now that you have worked through this, I
expect that you'll never have much trouble with this type of problem
again.) Thanks for letting me know how you came out.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.