fmm
Registered User.
- Local time
- Today, 07:46
- Joined
- Mar 15, 2001
- Messages
- 76
I'm having a strange problem with a totals query.
I have an address table containing (among others) the following fields:
RecID (the link with the master table),
Generation, and
id1, an autonumber field.
Every time a new record is added for a given RecID, the generation number is increased over the previous record (the 2nd record for RecID = 123 would generally get a Generation of 2).
I built the table by adding the original addresses (all with a Generation = 1), and then added revised addresses (Generation = 2).
Since I want my query to work in the general case I did not want the query tied to a specific Generation number. I created a totals query that had "Group By" in the totals line for RecID, and "Last" for every other field. The only field indexed is RecID. This has worked for me in the past.
This time it did not. The query pulled some records with Generation = 2 (good) and others with Generation = 1 (bad).
If I look at the 'bad' results, the record put in second (and I'm sure this is the case because id1 is always higher on a record with Geeration = 2 than for the record with Generation = 1) appears first.
Am I mistaken about the use of 'Last' in a totals query? I had assumed that it would give you the last record entered after the effects of any indexes had been accounted for. In other words, if there are no indexes in effect, the query would return the last record entered for each RecID.
Thanks in advance.
[This message has been edited by fmm (edited 08-13-2001).]
I have an address table containing (among others) the following fields:
RecID (the link with the master table),
Generation, and
id1, an autonumber field.
Every time a new record is added for a given RecID, the generation number is increased over the previous record (the 2nd record for RecID = 123 would generally get a Generation of 2).
I built the table by adding the original addresses (all with a Generation = 1), and then added revised addresses (Generation = 2).
Since I want my query to work in the general case I did not want the query tied to a specific Generation number. I created a totals query that had "Group By" in the totals line for RecID, and "Last" for every other field. The only field indexed is RecID. This has worked for me in the past.
This time it did not. The query pulled some records with Generation = 2 (good) and others with Generation = 1 (bad).
If I look at the 'bad' results, the record put in second (and I'm sure this is the case because id1 is always higher on a record with Geeration = 2 than for the record with Generation = 1) appears first.
Am I mistaken about the use of 'Last' in a totals query? I had assumed that it would give you the last record entered after the effects of any indexes had been accounted for. In other words, if there are no indexes in effect, the query would return the last record entered for each RecID.
Thanks in advance.
[This message has been edited by fmm (edited 08-13-2001).]
