MS Access help please

Factual Questions
1

I want to do something that I believe is pretty simple in Access but I can’t figure out the right way to ask it in Google to figure it out so hopefully someone here can point me in the right direction.

Here is what I want to do:

I have scores for a test. Those raw scores corrorlate to a point table based on Age and Sex. I want to be able to type the score into my databasenad have it read a second table, pull the correct points from that table, and enter them into a field on the score table automatically.

For example:

Joey did 55 pushups. Joey is a 19 yr old male. There is a table for “Males under 30 yrs old” that lists the point value for 52-57 pushups as 8.5 pts. When I type in 55 in the pushups field I want it to reach out to the Males Under 30 table and grab the point value for 55 pushups.

This should be easy to do but I can’t figure out how to ask this question in google to start digging.

Any help here?

2

Can you change the table layout or is it fixed?

If I was doing it in SQL I would have a Pushups table, with the following fields…MinAge, MaxAge, MinPushups, MaxPushups, Points, Gender. The row you’re looking for would have the values 0, 30, 52, 57, 8.5, M.

The SQL you’d use would be “SELECT Points FROM Pushups WHERE MinAge < 19 AND MaxAge > 30 AND MinPushups =< 55 AND MaxPushups >= 55 AND Gender = ‘M’” If you put in 19, M and 55 as variables the query would return 8.5

Does that help? It works best if your tables are set up properly, using int types (one column for min, one for max) instead of strings (min-max).

3

I think I’m following what you’re saying here. The table can be laid out any way I want it to be so that’s not a probelm. I’ve only been using Access (and databases) for about 2 months but I’ve picked up some of the SQL stuff.

SELECT FROM WHERE

I think that’s what I’m looking for. I’ll have to experiment.

Will the same thing work for time? Like 12:00 - 12:30 = 40 points. etc…?

4

Depends.

If each time gets a different score unrelated to any standard score then you just add time to every table.

If it is just a multiplier - 10% extra points for less than 12:30 say, then set up a time table.

And you shouldn’t have a pushup table you should have an exercise table with all the exercises listed. It makes subsequent changes easier.

5

Oh and I should add that for database solutions that don’t have hundreds of thousands of records Excel is far easier to use and a much better tool for most purposes.

6

I’m not sure how this could possibly be true. A database by definition has multiple tables. I have yet to see this handled in Excel without a huge hack.

7

Don’t think I agree with this. I only have about 3000 people that I track but there are records on PT, locators, personal info, etc… With a DB I only have to type the names once and using forms I can enter data directly attached to those names.

8

I understand and agree with your suggestion. The problem is that, as far as I can tell, I can’t run SQL code WITHIN a Form in Access. Meaning, when they type “30” and go to the next field the “Points” field won’t automatically pull from the table. I believe I must run a query for it to go to that table, which doesn’t happen automatically in Access forms (or does it?). So I would need to fill in everyones scores, then run a seperate query to see all of their points.

I can use VB code within the form though.

9

You can use VB code to track events (like textbox.losefocus or whatever) and then when that event fires, you can have it run some SQL.