Looping through a recordset.

  • Thread starter Thread starter Frank Martin
  • Start date Start date
F

Frank Martin

I have Access2000 with WindowsXP.
I have made a totals query to form a supplier cost list from two tables
tblSuppCost and tblSuppTxns.

This query gives me the last (max) date when products were purchased.

But there is more than one supplier for each product, so the query can give
more than one "last date" if there is more than one particular product
supplier.

I want to remove the earlier purchase date from the dynaset if a product is
shown with more than one supplier.

This would involve comparing dates/suppliers for each record and then
removing the earlier date when there is more than one supplier per
particular product.

Can someone help me with a suitable starting method.
 
Frank

Rather than looping through a recordset, you might find using a query to be
faster. I may not have understood your description of your data, but it
sounds like you could create a "Totals" query that uses GroupBy on Product
and finds the Maximum DatePurchased.
 
I have Access2000 with WindowsXP.
I have made a totals query to form a supplier cost list from two tables
tblSuppCost and tblSuppTxns.

This query gives me the last (max) date when products were purchased.

But there is more than one supplier for each product, so the query can give
more than one "last date" if there is more than one particular product
supplier.

I want to remove the earlier purchase date from the dynaset if a product is
shown with more than one supplier.

This would involve comparing dates/suppliers for each record and then
removing the earlier date when there is more than one supplier per
particular product.

Can someone help me with a suitable starting method.
Click to expand...
No looping or removal is necessary!

Instead, use a Subquery. If you could post the SQL of your totals
query, someone should be able to suggest a way to modify it to return
only the desired record.

John W. Vinson[MVP]
 
John Vinson said:
No looping or removal is necessary!

Instead, use a Subquery. If you could post the SQL of your totals
query, someone should be able to suggest a way to modify it to return
only the desired record.

John W. Vinson[MVP]
Click to expand...

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.

Regards Frank
 
Back
Top