Problem with grouping on the last date.

[Frank Martin]

My problem is to construct a product price list.

The price list will be constructed from tables and will have only three
columns:
1/ ProductID
2/ LastPrice
3/ Date of last price.

I have two tables with the required data:

tblSuppCost (which contains the supplier list and their products & prices,
and one product can have several suppliers each with the same or different
prices.)

tblSuppTxns (contains the date of all supplier transactions.)

If I form a query with the above tables and try for the price list using the
"last" grouping, it works except when I have purchased the same product from
different suppliers, and I cannot reduce the grouping any further.

How can I remove the Suppliers from the query and have only the products,
prices and dates?

Please help, Frank

 

[John Vinson]

My problem is to construct a product price list.

The price list will be constructed from tables and will have only three
columns:
1/ ProductID
2/ LastPrice
3/ Date of last price.

I have two tables with the required data:

tblSuppCost (which contains the supplier list and their products & prices,
and one product can have several suppliers each with the same or different
prices.)

tblSuppTxns (contains the date of all supplier transactions.)

If I form a query with the above tables and try for the price list using the
"last" grouping, it works except when I have purchased the same product from
different suppliers, and I cannot reduce the grouping any further.

How can I remove the Suppliers from the query and have only the products,
prices and dates?

Change the Last to Max - the "Last" operator in a Totals query is
really misleading, as it returns the last record *in disk storage
order*, an order over which you have no control. The Last record will
sometimes be the most recent record but you cannot count on it!

I'm not certain how to deal with the Suppliers question, since you do
not indicate how your are including the suppliers in the query. Could
you please open the Query in SQL view and post the SQL text here?

John W. Vinson[MVP]

 

[Frank Martin]

Change the Last to Max - the "Last" operator in a Totals query is
really misleading, as it returns the last record *in disk storage
order*, an order over which you have no control. The Last record will
sometimes be the most recent record but you cannot count on it!

I'm not certain how to deal with the Suppliers question, since you do
not indicate how your are including the suppliers in the query. Could
you please open the Query in SQL view and post the SQL text here?

John W. Vinson[MVP]

Thank you. The folowing are the queries in SQL.

SQL before the totals query applied:-

SELECT SuppCost.ProductID, SuppCost.ItemCost, SuppTxns.ItemDate
FROM SuppTxns INNER JOIN SuppCost ON SuppTxns.SuppID = SuppCost.SuppID
ORDER BY SuppCost.ProductID;



SQL after the total query applied, (ItemDate grouped on "Max"):-

SELECT SuppCost.ProductID, SuppCost.ItemCost, Max(SuppTxns.ItemDate) AS
MaxOfItemDate
FROM SuppTxns INNER JOIN SuppCost ON SuppTxns.SuppID = SuppCost.SuppID
GROUP BY SuppCost.ProductID, SuppCost.ItemCost
ORDER BY SuppCost.ProductID;

As previously mentioned we can buy the same product from several suppliers,
and the above totals query will successfully produce the last date of
purchase, but will do it for each of the suppliers for the same product.

What we need is a last product price whichever supplier this was purchases
from. That is, we want to make the supplier irrelevant.

Can this be done by a query?