dates and numbers

[eric.l.bohon]

Have a question. Have two queries, one for my units army physical fitness test current query and army physical fitness test past due query.

Now what I am trying to do is if I input a date on the soldiers test and it is in with 6 months it brings that soldier up on the current query and if it is past 6 months it brings him up on the past-due query. Now I Have the criteria for this. But what I want to also do is if the soldiers date is within 6 months of todays date and he fails the test than it leaves him on the past due query.

Here are the fields that are used: test date, push-up score, sit-up score, 2-mile run score and alternate event score. Now for the score fields if they go below 60 in any or all of these events they fail and if they go above 60 in all events they past the test.

Any help with this.

 

[NumbersMax]

Add criteria on the same row as your current criteria for the current test query to read >= 60 in each test score field you have on the query. When it is on the same row, it's like saying AND...if you want to see the scores, the Totals row can stay the default "Group By', but to hide them, change the Totals row to "Where".

On the past due query, add criteria on a different row than the date criteria that says <60 for each test score field in the query. (Note: If only one failed test keeps him on the past due list and you have multiple tests in one query, then the criteria test <60 will need to be on a separate row for each test score so that they will function as OR statements.)

You want it to read something like this:
Row# Date difference field pushups situps 2-mile alt event
1) > 6 months
2) <60
3) <60
4) <60
5) <60

An alternate idea:

You could add a pass/fail field to your table formatted as yes/no that would default as no and could be updated by query to "yes" if the person passed all the tests they took.

Create update query
Add your table
Select pass/fail field
Enter True (or Yes) in the "Update To" row

Select all of the test score fields and add >= 60 for each on the same row...this will update any existing data in the table.

Then your querys could test on:
date < 6 months AND pass/fail = yes for the current query
date > 6 months OR pass/fail = no for the past due query

Note: always back up your database and make updates to the backup when making changes!

Hope this helps!
Max

 

[NumbersMax]

I see the < 60 s on the 2nd-5th rows left justified on the post...they were supposed to go diagonally down the post so that each one was under a different test score field!