Converting Excel table for lookup of values based on row and column headers

kicrewd

Registered User.
Local time
Today, 05:19
Joined
Sep 3, 2014
Messages
18
Hi,

I am still trying to understand how data is best structured in Access.

I have a table of values (for instance: weight) and I need to be able to look up a weight based on the column header (age) and row header (height).

How is this sort of data best structured and accessed in Access?

Thanks.
 
The correct table structure in Access would have three fields, Age, Height and Weight.

The query for weight would use parameters on the Age and Height fields.
 
Here's what I heard: I have 3 fields: height, age, weight. Since, you provided no context other than your field names, I'm going to assume this data is how large a llama's should be. So this is your table:

LlamaSizes
LlamaHeight - Long integer, represented in centimeters
LlamaAge - Long integer, represented in years
LlamaWeight - Long integer, represented in grams
 
Alright, putting three fields in one table makes sense.

It just seems like I end up repeating a lot of data in the table since there are age X height number of weights and repeated data is what Access is supposed to be good at getting rid of.

But since there is no other data linked to each field, it wouldn't make sense to have three tables, one each for age, height and weight with weight consisting of relationships to the other two.

I guess that as soon as you start to have other data connected to one of those three it starts to make sense to break it into related tables, like if each age had certain other pieces of data associated with it. Then you would start to think of it like a table each for age, height, and weight, as well as a table called person which is related to those.

Eh?
 
Then you would start to think of it like a table each for age, height, and weight, as well as a table called person which is related to those.

The only way you would seperate those entities is if they had no relationship. You explicitly stated in your initial post that a specific age and a specific height related to a specific weight. When you have that relationship, you need them all in the same table.

The only thing that could change that is if your relationship is logical. Suppose weight equaled height x age. In that instance, you don't even store weight, you simply calculate it when needed.

Again, based on your initial description, you need to store all 3 of those because they have a relationship, but not a logical one. If you want to be more specific in your databases purpose then I can help more specifically.

Adding additional data fields depends on how that new data relates to your existing data.
 
Back to my initial problem: I am creating a query which needs to pull a specific weight from the table based on the age and height. I am trying to do this with the dlookup function.

Is there a simpler way to pull this value of weight? Galaxiom referenced doing this as if it was a built in functionality of a query:

"The query for weight would use parameters on the Age and Height fields."
 
It really depends on where you are pulling it from and the purpose. If you are pulling it one at a time and to put on a form or a report, you would use a Dlookup. If you have a dataset of people's names along with their age/height, you would create a query, link them appropriately and run the query for the entire dataset.

In what context are you trying to retrieve weight?
 
I am using the weight values one at a time to calculate other values in the query and output all of it to a report.
 
If you are using them in a query then it sounds like you need to join your data to the table and pull them that way. A Dlookup isn't the proper way to get data into a query.
 
I can put the three fields: age, height and weight in the query, but those represent a standard table of data which can be referenced.

I have a form to input the age and height, the table is information that is built into the database. How do I get the specific weight from the table for a specific age and height input?
 
For one specific age and height you would use a Dlookup. Unless of course you are ultimately referencing a query, in which case you should join the table in that query.
 
How do I get the specific weight from the table for a specific age and height input?

By joining to the other data you are retrieving on the Age and Height fields. This will return a Weight.

This is why it is essential that you use three fields to store the data rather than a matrix with Age and Height as rows and columns.
 
Galaxiom:

Using three fields so that I can retrieve on the Age and Height fields and return a Weight makes a lot of sense theoretically.

What do you mean by joining to the other data? Is there a way to do this with relationships so that when I fill in an age and height it automatically pulls the weight to fill the query field?
 

Users who are viewing this thread

Back
Top Bottom