Queries with Multiple Criteria (1 Viewer)

[RogueJD]

Hey all.

Relatively new to Access, but I did take a few undergrad classes on relational databases and the like.

I'll get right to the point. I have attached portions of my database to get better feedback.

I'm using a subform "APFT_Subform" that I would like to do several things.

Task: Record results of a physical fitness test (PT test)
- Enter a person's age at the time of the test (store on APFT table)
- Enter a person's raw push up score (store on APFT table)
- Enter a person's raw sit-up score (store on APFT table)
- Enter a person's raw run score (store on APFT table)

- Auto-calculate scores based on pre-defined standards. These standards change based on age, sex. E.g.: 20 y/o male, 50 push ups is 71 points; 20 y/o female, 50 push ups is 100 points. See this table for further info: http://www.wiu.edu/coehs/military_science/physical_training/APFT Standards.htm

- No need to store scores on APFT table, just display on APFT_Subform
- Add scores from 3 events (Store on APFT table)

So - that's the intent. I was hoping to get guidance on how to effectively accomplish this. I simply don't know how to do queries that well.

Of course, I don't expect anyone to do work for me, but I was hoping someone would map out the steps I'd need to take to make it happen.

Thanks, all.

//Edit: Added .mdb file and clarified my problem. Please see post #6 in this thread.

 

[RogueJD]

Access ninjas, where are you?

 

[RogueJD]

I'm sure that this wouldn't stump you guys. I hope my OP wasn't too confusing.

The forms are generated - the only issue I have is with calculating the "raw" scores into into APFT scores.

How do I go about entering a person's age, sex, and "raw" scores, and having Access calculate that information based off of a chart?

Males have a different scoring scale than females. On top of that, age is a factor when calculating points. (See OP for chart)

 

[John Big Booty]

Are you able to save your DB as '03 version and upload that?

 

[BigHappyDaddy]

Rogue: I think you are going to need to design a basic table that will house the relationships between the raw score (ie number of pushups), the age, sex and the final score.

For example:
EventID (the id that represents the event being scored: like pushups)
Sex
Age
RawScore (actual number of pushups)
FinalScore (the score between 0-100)

Then you could use a query to 'retrieve' the final score for each event, then sum the three scores.

Let me know if this helps. It has been a long time since I had to take an APFT test (got out of the Army in Oct '97), but I would like to help you.

Formerly known as: 1LT Stevenson

 

[RogueJD]

Thanks for the info, BigHappyDaddy.

I knew I would have to make several tables for the APFT score data, I'm just looking for the most efficient way of doing it.

I've attached an .mdb format of the database.

Here's what I -think- i need to do:

- Create APFT Score Tables
- - One for each age group and sex (8 age groups, 3 events, 2 sexes. That's a lot of tables.)
- - Each table will have the following attributes: Event_ID (PK) then a range of raw scores.
- - The Raw Score attribute will have the corresponding APFT score assigned to it in the data field.

My question deals more with the query I'll have to use to make it work.

In short, I want to manually enter age, sex, and raw scores, hit a button, and generate the APFT scores (preferably have it auto-generate as I enter it).

There are some other issues as well (alternate APFT events, extended APFT scale, physical profiles, etc. - For you former military out there, you know what I'm talking about.) I'm aware I'll need to address these issues, but I'm not worried about that right now.

Thanks for helping. I look forward to the solution.

 

[BigHappyDaddy]

- - One for each age group and sex (8 age groups, 3 events, 2 sexes. That's a lot of tables.)

I am thinking just one table as described above. Then take the values from you form (Sex and Age) and the manually entered raw score to retrieive the final score for each event. Then I would simply sum the results of the three events.
I wouldn't get too worked up about entering the data for each and every age. I think I could enter in the data for the first age in each group. Then your query is to return the largest age not equal to the service member's age.
Designing your tables need to be complete before you worry about writing queries. Poor table design requires complex queries to overcome table shortcomings.

 

[RogueJD]

Sounds good. I redesigned the tables. I have two now - Only because the attributes are different - Pushups and Situps have Reps as attributes, whereas the Two Mile run has times as the attribute.

I'll finish the tables as per your advice, then I'll probably come back to get some help structuring these queries.

 

[BigHappyDaddy]

While you could break it up into two tables, you still could combine them under one table. Store both the reps and time into the same field, but the field needs to be a Double data type. Whole numbers for push-ups and sit-ups. The fractional numbers would be the time. This way you still only have one query to write.

 

[RogueJD]

//Edit - Disregard this post. Had a problem -but I worked through it. It claimed to have too many fields defined. Just had to repair the DB to make it work.