# dates and numbers

#### eric.l.bohon

##### Board Regular
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.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
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

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!

Replies
2
Views
266
Replies
2
Views
592
Replies
0
Views
204
Replies
1
Views
230
Replies
3
Views
199

1,221,417
Messages
6,159,789
Members
451,589
Latest member
Harold14

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back