Issue with query qesults using Max date and check box

[sas]

I have three tables:
Large Meters: This table has several fields associated with the Large Meter like brand.
Accounts: This table is where the user assigns a Meter ID from the Large Meter table to an Account ID and also has several other fields like customer.
Large Meters Tested: This table is where the user enters information when a Large Meter is tested. They select the account number and enter the date tested and other information regarding the test. There are two check boxes in this table where the user can check if the test Failed or if they the meter could not be tested.

I want to be able to create a query where it shows the most current record for the account. I want to see the account number, date and if the test failed or could not be tested.

I have tried to use Max in my query for the date tested. The query will return the two dates but it ends up listing two records for each account with one showing that the test failed and one showing that the test did not failed.



For example if account # 1 was tested on 02/22/13 and it failed so the user checked failed and then on 03/22/13 it was tested and everything was ok and then on 04/23/13 it was tested again but the user checked failed because the test had failed again.

When I do a query and use max for the test date I would expect that my results would show that for account # 1:
Test Date: 04/23/13
Failed: -1
Cannot Test: 0

However when I do this query it returns:
Test Date: 04/23/13
Failed: -1
Cannot Test: 0

Test Date: 03/22/13
Failed: 0
Cannot Test: 0

It looks like it is trying to find the max date for when it failed or could not be tested and the max date for when everything is ok. I just want the query to return the most current test date for that account and to show me whether or not the test failed or was unable to be tested.

What do I need to do? I am thinking I need to change something with the check boxes?
I appreciate any help I can get.
Thank you

[pbaldy]

I'm not entirely clear what the goal is, but let's start with:

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

If you only want fails/unable to test, you'd add a WHERE clause to the first query.

[sas]

Paul,
Thank you! So far so good! I'm checking to see if everything is correct

[pbaldy]

No problem, and welcome to the site by the way!

[sas]

Everything is correct. Again thank you for your help!