Creating a query that shows only one line for each product

[LisaT]

I have a sales database which records sales. I want to be able to get a list of all the products and the date the product was last sold.

What I have been able to do is to get a list of all the products and the dates that they were sold but it shows every instance of the product and each date it was sold. I am only interested in finding the last date that each product was sold.

Does anybody know a solution

Reagrds,

Lisa Tree

 

[MichaelRed]

You will probably want to provide a bit more info. A sample table for the input and (desired) output would be nice. In general, the soloution to your inquiry would be to generate a series of layered queries, such that you obtained a set of records which shoh only the product key and the last date sold, and another which totaled the sales by product. Join these on the product ID and you should have the desired results set.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[LisaT]

It all sounds a bit more complicated than what I was after. I just need to know the products that haven't sold in the last six months so that I can get rid of them and stock more stuff that does sell. If I got a report on each product and the date they sold I could just search through the list and find what I am after.

I used to be an old DOS programmer and have worked with other databases and if I were doing it again I would follow this sort of procedure:

For each product
search the order table going backwards in date until I found a matching record. then put that product name in the list with the date beside it.

Am I making any sense???

 

[Quehay]

(You mentioned two different objectives in your posts (1) Latest Sale per Item and (2) Those that haven't sold)

For (1) Try this with a Summary Query (click the Sigma icon):

Field | Field

ItemName | DateSold

GroupBy | MAX

If you want a list of those that haven't sold just go back to the original Select query with an Is Null criterion below SaleDate.

 

[braindead2]

Make a query include the product Id or name include date sold use the max(function to get last date sold)
This is query I use to look at last time an Item was used.

SELECT USETBL.Item, Max(USETBL.Date) AS Lastused FROM USETBL
GROUP BY USETBL.Item;

 

[Quehay]

I forgot to add above: if you're looking for the parts/items whose DateSold is Null be sure to do an outer join on the PartID (this is your join field between tbl_parts and tbl_orders right?) This the "Show all records from table x and only those from y where the join field is equal" option when you right-click on the join line.