dates and numbers

eric.l.bohon

Board Regular
Joined
May 8, 2006
Messages
149
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
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

Board Regular
Joined
Jun 19, 2007
Messages
200
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!
 

Forum statistics

Threads
1,181,405
Messages
5,929,756
Members
436,687
Latest member
Glass of Gin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top