Problem with select query using date parameters

[JTQ911]

Below is an example of my table "Current Status"

Manufacturer ID___SN________Current Location__Status Date______Time
A________________1____________Area 1________1/6/2008_______3:15
A________________1____________Area 2________1/7/2008_______2:10 PM
A________________1____________Area 3________1/8/2008_______1:01 PM
B________________2____________Area 3________1/2/2008_______5:00 PM
B________________2____________Area 2________1/3/2008_______3:00 PM
B________________2____________Area 4________1/4/2008_______12:47 PM

How can I design a query that will return each products latest currrent location by date, aka, the third and sixth record???? Thank you, I am relativley new to access and am struggling with this.

 

[pablavo]

Could you give a little more info as to what you want? do you want to extract records with whatever date you specify?

 

[JTQ911]

I have the user enter Manufacturer ID and SN, so in this case, if they entered "A" for manufacturer ID and "1" for SN, three results would be displayed. I only want ONE record to be displayed, that being the latest record based on the Status Date


Thanks

 

[pablavo]

If you want the lastest date for that set of records you could use the MAX function.
Change your Select query to an aggregate using the totals option within the view menu and where it says Total, change it to MAX under your date field.

Hope that's what you were looking for.

 

[JTQ911]

I just do that for "Date" i did that, and all three records still show up??? What could I possibly be doing wrong? Thanks again

 

[pablavo]

As far as I'm aware, each field from left to right takes precedence in the aggregate. So, if you have several fields within your query, set them up like this:

Manufacturer ID(GroupBy)
SN(GroupBy)
Current Location(GroupBy)
Status Date(Max)
Time(Max)
In that order within your query


Set your parameters within the ManID and the SN so that you get a parameter dialog box asking for these.

Try it and see if that works