If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table design
Hi there,
I have the following challenge ahead, can someone please offer some advice?: I have a database with several peoples' measurements. I created the following table structu 1 main table, containing the persons' personal details, age, etc. 1 subtable for each measuring method used on this person, and the respective measurements (e.g. body height, sitting height, arm length etc.), in this case then there are 4 subtables. Each person will only appear once in any of the tables. Therefore I set up a one-to-one relationship between the main table and each subtable, using the PersonID as the primary key. NOW what I want to do, is to compare the persons' measurements with a data source (at the moment in Excel but want to import it into Access) This data source contains about 20 rows, which are ascending intervals in body height measurements, e.g. 1602mm, 1614mm, 1627mm etc. There are 3 columns: Each one is a heading indicating small proportion, middle proportion and big proportion. Each contains a sitting height measurement. So for example, for body height of 1621, there will be in the 3 columns for small proportion, middle proportion and big proportion the following values 830, 865 and 902 respectively. What table structure should I set up, if I want to link each person to this data, and indicate e.g. on a form whether this person is small, middle or big proportioned? I have already thought of making a relationship between the body height field of each of the subtables to the body height field of the new data source, but the problem is that the person's body height must only be in an interval, and not exactly equal to the body heights as set in the new data source. I will later be setting up similiar data tables (for the proportional data) which will also contain data for 10 years ago and 10 years in the future, as these values then differ ( Humans are on average getting bigger by the year, strange but true! ). I thank you kindly in advance for your help. |
#2
|
|||
|
|||
Table design
"Jean" wrote in
: 1 main table, containing the persons' personal details, age, etc. 1 subtable for each measuring method used on this person, and the respective measurements (e.g. body height, sitting height, arm length etc.), Bad. These are essentially the same things, so a better design would be something like (== is Primary Key, -- isn't) PersonID BodyPart Value ======== ======== ----- Eric Arm 52 Eric Bd Hgt 191 Eric Arm 53 Eric Bd Hgt 199 Freya Arm 49 Freya SitHt 103 This is faster and easier to query, and conforms to normal db design practice. NOW what I want to do, is to compare the persons' measurements with a data source (at the moment in Excel but want to import it into Access) This data source contains about 20 rows, which are ascending intervals in body height measurements, e.g. 1602mm, 1614mm, 1627mm etc. I don't really understand what you are trying to do here, but almost certainly the anwer is a DLookUp or DMax function. If you are going to look up the table in Excel, there are the VLookUp and HLookUp and Indirect worksheet functions you can do too. A lot of choice! The best one depends on quite what you are trying to look up and how the base data are arranged. HTH Tim F |
Thread Tools | |
Display Modes | |
|
|