Trying to reference like Excel

[Adrianna]

I've imported a table from excel that provides column/row referencing. I want to be able to tell a query If [age 18-21] and [30-40 pushups], then referene the number that the intersection of the to in the table.
[age 18-20] is a record header and [30-40 pushups] is a field header? This is reallt simple in excel because I'm able to reference a cell, but I'm not able to track, calculate, total, and gernerate the reports that I need in excel.
Oh, these reference table have already be split into Male and Female, so I can pull the queries seperately, unless someone knows an easier way of referencing the to from one table?

 

[Liv Manto]

if the age is on record header and no of pushups is the field header in Excel, what data is on the intersecting cell?

What data do you want on your query?

Provide more details.

 

[Adrianna]

Liv
I need to look up the score at the intersection of the two. So when someone selects a gender, that will specify which chart to reference, then they will select an age range. The age range will corresponde with a record and the number of "pushup"(or what ever activity), will key in to pin point the score.
Maybe this would be much easier through excel, but I don't know how I could save each record that's keyed in, so that I can reference it and pull reports later. If it's to difficult to process in Access, then I can post to the EXCEL forum and see if anyone knows how to store records. The boss man really wants it to be easy and seamless as possible(don't they all).
Anyway, hopefully that gives you a better idea of what I'm looking at.

 

[Pat Hartman]

The general procedure for converting spreadsheet type data to table format is to take the row header and a single column header as the key for the table and the contents of the intersection cell would be a data field. So in your example you would have a table that looks something like:

    Age        Exercise     Score
[age 18-21] [30-40 pushups]  23
[age 18-21] [50-70 leglifts] 29
[age 22-29] [30-40 pushups]  34

However, a more useful table would split the fields into:

AgeFrom
AgeTo
ExerciseType
RepeatFrom
RepeatTo
Score

The first 5 fields would be used to form the primary key.

A table with the above structure would ultimately be the easiest to use and report from because it does not mush multiple pieces of data into the same column.

Making the transition from using spreadsheets to using tables takes a big change in how you view data. You might want to do some reading on database design to get you started.

 

[Adrianna]

Pat, the problem with this is that I also have to consider Gender. These tables are to large to be created following your method although I really likes the idea. Your way would allow me to easily pull the information that I need, but if you have 10 age ranges and 100 repetitions, that would require 1000 items in the table. Will the table hold that many. Then I would have to have two tables. One for each gender. Why would something that is so easy in Excel be so hard to perform in Access? Isn't there some way that I could get my subquery to ask for and age that matches the record header that corresponds withthe age?

 

[The_Doc_Man]

You can do what you want but it is not as easy as you would like it because the query has to be dynamically generated. The issue is that Excel binds information differently than Access. And that binding difference is your problem.

You cannot do what you want with a static query. (You could do it with one static query per column, MAYBE, and even there I'm not sure. But selecting the right query is just as ugly as what I will show you here.)

If you really wanted to do this dynamically, I would take this approach.

1. Analyze your table ahead of time. Identify the columns on which you would make your queries. Identify the ones that are merely qualifiers. (Male/Female sounds to me like a qualifier.)

2. Choose one of the elements as the primary key. Age range is as good a candidate as any other, I suppose. Make that one of the Combo Boxes on your form. Assume it is [AgeRange].

3. Build a list of possible criteria you can use for queries, each of which sounds like a column name. Make it a list box or a combo box, however you want it to be. Assume it is [Category].

4. As part of the combo-box _AfterUpdate event, build a couple of strings based on the options selected in the combo boxes.

5. Make your qualifiers (male/female, for example) also contribute to the same query strings. You might get away with male/female as a check-box or option button box, then call your query string builder from the Click event. So at this point, the query string builder might be a general (i.e. non-event) subroutine called from various event routines. For male/female, call it [MaleBox].

6. Build the string somewhat like this:

stField = "[" & Category & "]"

stQuery = "[AgeRange] = """ & [RangeBox] & """"

if [MaleBox] then
stQuery = stQuery & " AND [Sex] = ""M"""
else
stQuery = stQuery & " AND [Sex] = ""F"""
end if

7. Make sure you have a place to show the answer. Call it [AnswerBox].

Then use these items in a DLookup to show the value in yet another box as the final part of the event routine:

[AnswerBox] = DLookup(stField,"MyTable",stQuery)

 

[Pat Hartman]

Ten age ranges is certainly reasonable but, 100 reps? From the example you showed, reps were grouped also. You could either include sex as the sixth field of the key or you could have two score columns - ScoreFemale and ScoreMale. I would go with the two score columns since it reduces the number of rows by 50% unless you want to use different rep ranges for each sex. Then it would be better to have sex as part of the key.

Trust me, if you want to do any analysis of this data, my way will work better.

1000 rows in a table is nothing. You can store millions if you need to as long as your total database size does not exceed the max for whatever version of Access you are using.

DLookup() is about the worst way (performance wise) to retrieve data.

When you are planning your table design as you move from the spreadsheet world to the relational database world, keep in mind that the number (max 255) and names of columns in tables is fixed and normally requires significant changes in queries, forms, and reports to change. Whereas the number of rows is virtually unlimited and the addition of a row will normally not require a single change in anything you currently have working.