Need Query Help bad--Max Date is messing with the records pulled

[Madmax]

Hello All

Okay, What i have giong on is complicated to me. so let me start from beginning.

CACID is relationshiped BTW.
Table--Test Records
Fields on table--CACID(id of person), Date of test, Score



Table--Personnel
Fields on table--CACID, Last Name ETC...

Query 1-- total field active
Field 1--CACID, Group By
Field 2--Date of Test, Max

Query 2--Tables involved, Query 1, personnel, and test records
Field 1--CAID
Field 2--max date
Field 3--last name
Field 4--ETC of other names
Field 5--Score--From test records table.

Now the problem. If I run query 2. I get the max date on all records but each time someone made a different score i get the record added again.

If I remove score from field 5, I get each person and the last time they tested(Which is correct) Why though, will it up let me have the score they made on that maxdated test? and is there a work around?

[pbaldy]

It sounds like this is what you're trying to do?

http://www.baldyweb.com/LastValue.htm

[Madmax]

Lol I cant look at your website. the USAF has it blocked lol

I found a work around. I put two more fields under personnel and have them update each time the test is taken with a new last date and score. Any other information I need I can manipulate in the future. As of right now this makes it functional enough to use next week and allow me to work on other projects.

I will look at your website when I get home today still for future reference.

Thanks to your quick reply,

[pbaldy]

Tell them my daughter is a Captain, so open it up!!

Here's the text of it:

Let's say you have a table with vehicles, dates, and various other information. You want the information from the last entry for each vehicle. One solution involves two queries. The first gets the most recent entry for each car (this is named qryMaxDates):

SELECT Max(VehicleMiles.DorDate) AS MaxDate, VehicleMiles.CarNum
FROM VehicleMiles
GROUP BY VehicleMiles.CarNum

Then in a second query you join the original table and qryMaxDates to get the other information associated with that last record:

SELECT VehicleMiles.*
FROM VehicleMiles INNER JOIN qryMaxDates
ON (VehicleMiles.CarNum = qryMaxDates.CarNum)
AND (VehicleMiles.DorDate = qryMaxDates.MaxDate)

Where are you stationed? Daughter is in England, at Mildenhall (hubby at Lakenheath).

[Madmax]

Thanks for the info. I will try it out tomorrow and see if it would pull better for me.

Stationed at Barksdale in Lousianna. I have been here 14 years. Crew chief on B-52 and they dont move much. Currently working as a Security Manager/PRP guy and organizing every shop in the world that needs it. My next big project is a combined DB to track Human Resources inside of a squadron lol. My captain Found out I learned and Liked Access. He put me to work.

[Madmax]

oh and I am going to try to get your website opened up because it is very helpful and stupid to not be opened. submitting it now.

[Madmax]

They say to contact you first and have your site recategorized from None to something else. Do not put for web advertisement or games or anything like that. If you cant do that let me know and I will submit a change and post this forum post as the need for a change.

[pbaldy]

Small world; my daughter is a maintenance officer. She started on the AWACS planes at Tinker, went to F-15's at Seymour Johnson, now on the KC-135 at Mildenhall. Her husband is an F-15E Strike Eagle pilot; since they got married the AF has done pretty well at keeping them together. I've offered to help her automate things, but so far she hasn't had anything. Probably a case of not understanding what Access can do for her.

As to site category, I'm going to have to look into that. I just put together the pages and push them to GoDaddy. Not sure how/where to categorize the site. Google here I come!

[Madmax]

Coolness, Yeah the USAF could use access in many different areas. I currently have one tracking security manager stuff, one tracking PT, one tracking prp stuff. My list of to do = my Group RA would like one, my mobility section wants one, and eventually I would like to build one that pulls all the information together in a squadron with various areas all tracked in one combined large database. this is where my human resource tool would be as well as training and so on. but that is far down the line. I do have a regular job to contend with lol. Honestly, if they kick me out for PT I might make one and try to sell it back to them. hehe