more that 1 record

[folkie]

I have a query that has, among other fields, name, sprg (short for subprogram), date, and a calculated field called InsJan. There will be 1 record for each name/sprg combination. If there’s only 1 sprg for a person, I don’t have a problem with InsJan. But if there’s more than 1 sprg for an person, InsJan is being calculated for each name/sprg record as if there was only 1 record for that name. Those dollars are being duplicated because they’re showing up on every record. I want all the records except the latest sprg record for a person to be 0 in InsJan.

Is there a way for my query to look at all the records for a person and for those records other than the last date, have 0 in InsJan? For the latest record for a person, InsJan would be calculated normally.

One thing I just thought of was to have 2 queries – 1 gives me the last sprg record for each person. All these would have 1 sprg for each person and I could calculate InsJan the way I’ve been doing it. The other query would give me all but the last sprg records for a person. Here, InsJan would always be zero.

If you have ideas on how to do any of that, as long as I get to my ultimate goal, I’d appreciate it.

 

[dynamictiger]

I think from what you have said I would go with the two query approach. One returning just the last record and do something to calculate the dollar value. The other with the same fields but not returning the last entry. Then create a union query of the two by going to SQL view and copying the two seperate SQL Statements into one new query with the word UNION in between the two statements.

 

[Pat Hartman]

Make a report. What you're talking about is "pretty-printing". That is best done with a report.

 

[folkie]

I thought I figured out how to start the 2 query theory until I realized my test coincidentally had the smallest sprg # as the earlier records. I used "min" and "max" in the 2 queries and things seemed okay, but I think they were okay because of the coincidence.

I then changed my test records to have a bigger sprg # first (in chronological order). While just working on the query to give me the records with the first sprg, I figured I should use first (to get the earliest sprg) instead of min (to get the smallest sprg). However, I can't get the records I want. I have a month field which shows me the month for each sprg per person. I've played with the sprg field and the month fields using first and min and “group by” in various combinations, but can't figure it out. Sometimes I get one record per SSN, but it's not the correct one (ie, it’s not the first sprg). Sometimes I get one record with the (correct) earliest month, but it has the (wrong) latter sprg. When I use TopValue = 1, I just get one record in the recordset, instead of 1 record per SSN.

Any suggestions would be helpful.

 

[folkie]

Never mind for now. My "first" and "last" queries now seem to work by using "first" for the month and sprg for the "first" query, and by using "last" for the month and sprg for the "last" query.

I may have problems combining (I guess, appending) the records from the "first" query, the "last" query, and then all the other records (people with more than 2 sprgs, and people with just 1 sprg).

I'll probably post a question for those.

 

[Pat Hartman]

Take a look at what I wrote about these functions in this post before you decide that your's are working.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=42291&highlight=first+min