Normlized Tables. Cannot get Queries and Forms to work correctly

[DStevens]

I have 3 tables:

PERSON
----------------------------------------
PK      PERSON_ID       AutoNumber
         FIRST_NAME     Text
         LAST_NAME      Text

NUMBER_SET
----------------------------------------
PK      NUMBER_SET_ID     AutoNumber
FK      PERSON_ID            Number
         NUM1                    Integer
         NUM2                    Integer
         NUM3                    Integer

ANOTHER_SET
----------------------------------------
PK     ANOTHER_SET_ID   AutoNumber
FK     PERSON_ID            Number
        VAL1                    Text
        VAL2                    Text
        VAL3                    Text

I'm trying to create a form with all the data. I can have the PERSON and either NUMBER_SET or ANOTHER_SET but not both. I'm assuming it's because I have a PERSON_ID FK in each table but I don't know what to do about it.

 

[Kryst51]

What I suggest is to have a number type...... Like this:

PERSON
----------------------------------------
PK PERSON_ID AutoNumber
FIRST_NAME Text
LAST_NAME Text

Your number table is wrong It should Go Like this:

NUMBER_SET
----------------------------------------
PK NUMBER_SET_ID AutoNumber
FK PERSON_ID Number
NUM Integer
FK NUMBER_TYPE_ID Number

The Num1,Num2,Num3 will be records in the number table relating the the name ID....

I would add a type table.

NUMBER_TYPE
----------------------------------------
PK NUMBER_TYPE_ID AutoNumber
TYPE Text

In the relationship screen relate the pk Number_Type_ID (From the type table) to the Fk in the number table.

 

[PNGBill]

Can you post the sql of your query?

 

[DStevens]

Your number table is wrong It should Go Like this:



The Num1,Num2,Num3 will be records in the number table relating the the name ID....

I would add a type table.



In the relationship screen relate the pk Number_Type_ID (From the type table) to the Fk in the number table.

Hmmm, I guess I'm severely confused. Each of the "Num" fields are different columns representing similar but different things. How can I have 3 columns referenced in 1 column in the PERSON table?

 

[DStevens]

Can you post the sql of your query?

SELECT
PERSON.CPERSON_ID, PERSON.PERSON_NAME, NUMBER_SET.NUM1, NUMBER_SET.NUM2, NUMBER_SET.NUM3, ANOTHER_SET.VAL1, ANOTHER_SET.VAL2, ANOTHER_SET.VAL3
FROM (PERSON INNER JOIN NUMBER_SET ON PERSON.PERSON_ID = NUMBER_SET.PERSON_ID) INNER JOIN ANOTHER_SET ON PERSON.PERSON_ID = ANOTHER_SET.PERSON_ID;

I'm pretty sure the problem is here:

...ON PERSON.PERSON_ID = ANOTHER_SET.PERSON_ID;

 

[Kryst51]

Hmmm, I guess I'm severely confused. Each of the "Num" fields are different columns representing similar but different things. How can I have 3 columns in 1 column in the PERSON table?

Mmmmm. Well, maybe if you told us something about the data.... I don't mean specifics, as you said it's senstive info, you could have categories, or if its different tests, Different test names that you could choose, either way it would be a separate record..... Many record numbers each with the same reference to the person in the persons table which in a query can be pulled together and manipulated to appear how you want it to. it would be the same as the type table.

But if the numbers really are different, like a telephone number vs a zip code you could do the columns as you have it.... I don't know your data, so I assume things by the names you are giving them in your table, it looks like similar data.

 

[DStevens]

Mmmmm. Well, maybe if you told us something about the data.... I don't mean specifics, as you said it's senstive info, you could have categories, or if its different tests, Different test names that you could choose, either way it would be a separate record..... Many record numbers each with the same reference to the person in the persons table which in a query can be pulled together and manipulated to appear how you want it to. it would be the same as the type table.

But if the numbers really are different, like a telephone number vs a zip code you could do the columns as you have it.... I don't know your data, so I assume things by the names you are giving them in your table, it looks like similar data.

I'll give an example:

PERSON
----------------------------------------------
PERSON_ID     FIRST_NAME     LAST_NAME
        1     Devin          Stevens
        2     Jim            Bob

NUMBER_SET
----------------------------------------------
NUMBER_SET_ID     MAX     EACH     PARTIAL
     1            426     1.25     .14
     2            308     1.1      .425

ANOTHER_SET
----------------------------------------------
ANOTHER_SET_ID   MAX     EACH     PARTIAL
     1           202     2.335    1.02
     2           729     .83      .08

The 2 sets are not related to each other even though they have identical columns. The columns in their respective sets, however, are related.

 

[Kryst51]

I'll give an example:

PERSON
----------------------------------------------
PERSON_ID     FIRST_NAME     LAST_NAME
             1     Devin               Stevens
             2     Jim                  Bob

NUMBER_SET
----------------------------------------------
NUMBER_SET_ID     MAX     EACH     PARTIAL
         1           426      1.25       .14
         2           308      1.1         .425

ANOTHER_SET
----------------------------------------------
ANOTHER_SET_ID   MAX     EACH     PARTIAL
         1           202      2.335     1.02
         2           729      .83        .08

The 2 sets are not related to each other even though they have identical columns. The columns in their respective sets, however, are related.

Well... the Original set vs another set can be done in one table with a type or category field the way I showed previously..... Are your data columns calculations from something else? Or from one of the other coumns? If so, caluclations are not stored in the table itself but are done on the fly, this helps reduce errors if there are changes to the orgininating data.... There are some exceptions to this rule, but they are rare cases. if this is not the case and the data is indeed separate data, then your columns are fine, but you don't necessarily need the other table....

 

[DStevens]

Well... the Original set vs another set can be done in one table with a type or category field the way I showed previously..... Are your data columns calculations from something else? Or from one of the other coumns? If so, caluclations are not stored in the table itself but are done on the fly, this helps reduce errors if there are changes to the orgininating data.... There are some exceptions to this rule, but they are rare cases. if this is not the case and the data is indeed separate data, then your columns are fine, but you don't necessarily need the other table....

Nope. They are raw data that need to be stored. Some calculations will be based off of them.

I didn't think we'd want the sets in the same table since they aren't related to each other at all.

Also, I forgot to add the FK to the set tables. Assume the PERSON_ID FK has the same value as the Autonumber PK in each table.

AutoNumber FK Max Each Partial
1          1   ### #.# #.#
...