Help with Max function!

[washingtons]

I'm trying to link a table to a query called M Gate Status. Each product has a certain status that matches it and I'd like my query to show the most recent status for each product. I've created an M Gate Status # column on the MGS table and want to select the maximum number, but I can't figure out the max function to put in my query. I'd like to do this without using VBA.

Thanks in advance!

 

[neileg]

Click the sigma button (backwards E) in the query design window to make an aggregate query (sometimes called a totals query). Set the aggregate function to Group By except for the rightmost column which should be your date, and make this Max.

 

[boblarson]

Click the sigma button (backwards E) in the query design window to make an aggregate query (sometimes called a totals query). Set the aggregate function to Group By except for the rightmost column which should be your date, and make this Max.

Since I already had it, here's a picture:

 

[washingtons]

I tried this and when I look at the query in datasheet view it's still showing all 5 records instead of selecting the max.

 

[boblarson]

Can you post a screen shot of it in design view and also in datasheet view?

 

[washingtons]

it's not pasting in the reply...can i email it to you?

 

[boblarson]

it's not pasting in the reply...can i email it to you?

It doesn't paste in the reply. You click on the GO ADVANCED button below the quick reply window and then it will take you to the advanced options. Then, you scroll down the page to find the MANAGE ATTACHMENTS button.

 

[washingtons]

It's telling me upload file failed.

 

[boblarson]

What type of file are you trying to post?

 

[washingtons]

I pasted the picture of the screen into paint so its a bmp.

 

[Brianwarnock]

Are you selecting only the product and status fields, if other fields are selected and grouped on then multiple record selection is possible.

Brian

 

[washingtons]

I've got the fields ID, product, carrier, m gate status, and m gate status #. The first four have group by selected under them and m gate status # has Max selected.

 

[boblarson]

To get the max, get rid of the product, carrier and m gate status and just have the ID and status number (I would get rid of the # in the names as that is a special character with meaning in Access - it is a date delimiter).

Then, create another query with all of the fields you had and link the table to the other query which pulls the max.

 

[Brianwarnock]

As he wants the max per product surely he will have to have just those 2 fields? Then his 2nd query will have to join on both fields to the table.

Brian

 

[neileg]

I agree with Bob & Brian, but here's an explanation of what is going on:

When you create an aggregate query, it groups together records that are absolutely identical in the Group By fields. So if one of your fields has a different value in each record then they won't get grouped together. To stop this happening, you omit these non-identical fields from the query. If you need these fields then you create a second query that takes the output from the first query and matches it back to the original records.