Maximum Value

[uk]

I have a table called Sales, in it I have fields: Customer number, Customer Name, Item purchased, Vaule of Sale, Date of Sale.

I want to create a query where I can Enter a customer Number and it will give me the most recent purchase info. I am guessing ussing the 'max' function is the way to do it but no luck so far!

Any Ideas?

 

[SydneyGeek]

Try this:
In the query, sort Descending on your ID field. This will give last orders first.
In the top of the design area, there is a Top Values dropdown. You can enter a number like 1 or 5, or 15%, to filter the last n values. Now filter for the client in question. You should end up with the last n orders for that client.

Denis

 

[uk]

SydneyGeek

thanks I had tried that but, it isnt as robost as what I need, I need a fixed query I can drop the customer name into and the latest sales data will come out without having to sort the table etc.

 

[SydneyGeek]

OK, you'll need a form for searching. Create a blank form. Call it frmSearch. On it, place a Combo linked to ClientID, but showing Client Name for ease of use. Call this combo, cmbClient.

In the query, in Design view, find the ClientID field. In the Criteria row, type this: [Forms]![frmSearch]![cmbClient]
close and save the query. The query could be called qryLastOrders.

Back to the search form. In Design view, with the Control Wizard on, drag a Command button onto the form. In the list of possible actions, find Open (ir is it Run?) a query. Pick your query (qryLastOrders) from the list of options. OK. Save.

Open the form in Datasheet view. Pick a client from the list, click the button, voila.

Denis

 

[uk]

sounds cool, what If I want to see the latest sales info for all my customers at once?

 

[SydneyGeek]

sounds cool, what If I want to see the latest sales info for all my customers at once?

Then you'll need a different query. Build a query with Client Name, OrderID, and other details that you might need. Click the Sigma button at the top of Design view to get the Totals row. Most of the fields will have Group By. Change the option for OrderID to Max. Save as qryLatestOrders.

Denis