Totals Query nightmare!

[davesmith202]

I have 3 fields: EventID, Selection, LatestTaken, Odds - all sorted in that order in Query1.

Then, I have a totals query called Query2. This is based on Query1. It contains EventID (Group By), Selection (Group BY), LatestTaken (First), Odds (First).

Am I correct in thinking that this will produce a dataset showing:

Grouped by EventID, then grouped by Selection, then showing the first value of LatestTaken in Query1 and then the first value of Odds from Query1?

For some reason, it seems to group EventID fine, group Selection fine, FirstOfLatestTaken (which is a date) is incorrect. Does First or Last not work with dates?

Thanks,

Dave

 

[Galaxiom]

First and Last refer to the First and Last Records which are not necessarily in any particular order.

You want Max and Min which refer to the highest (chronologically latest in the case of date) and lowest (chronologically earliest) values in the field.

 

[SOS]

And if you want more information about the FIRST and LAST functions. (the information here says Access 97 but it is good for all versions)

 

[davesmith202]

Thanks for the max/min explanation. That really helped. It has let me show the correct first 3 columns. However, I am having a problem with the last column, Odds. I want to show the first 2 columns grouped, the next column showing the max date (all working up to this point). But how do I display the Odds value next to each record that would be matched to the max date? Does that make sense?

I thought if I choose First or Min or Last or Max, that would show it, but it doesn't! Any idea?

SELECT Query1.EVENT_ID, Query1.SELECTION, Max(Query1.LATEST_TAKEN) AS MaxOfLATEST_TAKEN, First(Query1.ODDS) AS FirstOfODDS
FROM Query1
GROUP BY Query1.EVENT_ID, Query1.SELECTION
ORDER BY Query1.EVENT_ID, Query1.SELECTION, Max(Query1.LATEST_TAKEN);

 

[Galaxiom]

Not totally clear on your structure but this is what i think will work..

Join the table with the odds to the result of the query with the "totals" (Max, Min). Join as many fields as required to select the unique record in this table that match the results in totals query.