one table relation to two

[smile]

Usually, a lookup column looks like this - "Take the value stored in this lookup column, use it to find some information in another table, and then display the information from that table in this table."

I need to Take the value stored in this lookup table, use it to fill two separate tables depending on ordertype. If the order is type1 I fill table 2 if order is type 2 I fill table 3


I have a database with 3 tables:

Table 1:

ID autonumber primary key
ORDR (numeric value increases by 1)

Table 2: Oders printable info1

ID autonumber primary key
ORDR (linked to Table 1 )

Table 3: Oders printable info2

ID autonumber primary key
ORDR (linked to Table 1 )



It's a piece of a large dabase and hard to explain, but I have to enter specifinc information in Table 1 then with relationship enter that information in Table 2 or Table 3 depending on the order type. Table 2 and 3 hold other information that is used to generate a report. Table 1 provides start unique identifier increased by 1 like, ORDR-001, 002 003 etc.

I never have to enter or should I say link same order number from table1 to both. Would like to prevent it by acident too.

Do I need a junction table to make it work? Is there a better way to make it work?
Thanks.

 

[ajetrumpet]

Table 1:

ID autonumber primary key
ORDR (numeric value increases by 1)

Table 2: Oders printable info1

ID autonumber primary key
ORDR (linked to Table 1 )

Table 3: Oders printable info2

ID autonumber primary key
ORDR (linked to Table 1 )

I have to enter specifinc information in Table 1 then with relationship enter that information in Table 2 or Table 3 depending on the order type. Table 2 and 3 hold other information that is used to generate a report.

So, it sounds like Table's 2 and 3 are dependent on value that is present in table1. Thus, they are child sets. Childs don't have to have records in them, so why couldn't you just link #1 to both of the others? Hopefully, the work you're talking about is going to be performed within the forms themselves, and not the tables. Preventing accidents might be a bit easier this way too, via object coding.

 

[smile]

So, it sounds like Table's 2 and 3 are dependent on value that is present in table1. Thus, they are child sets. Childs don't have to have records in them, so why couldn't you just link #1 to both of the others? Hopefully, the work you're talking about is going to be performed within the forms themselves, and not the tables. Preventing accidents might be a bit easier this way too, via object coding.

I will use forms like you said, but how do I use object coding for filtering ?
BTW if I link table 1 to table 2 and 3 like you said - when I click on a plus sign I get pop up "insert subdatasheet" where I must select table 2 or table 3. While this method shows data when I expant using plus sign it doesn't work using a report.

When I create a report I include the field that relates from table 1 called "KIO number" then add 2 fields from tables 2 and 3 I should be able to get a report like this:

---------------------|-----------------|------------------------
"Table 1 KIO number" |  Table2 field KIO |  Table3 field KIO
---------------------|-----------------|------------------------
1                    |       1         |
2                    |       2         | 
3                    |                 |         3
4                    |                 |         4
5                    |       5         |

But I get results only if I include table 2 or 3 not both.

 

[smile]

I know access 2007 has new multifields lookup thing but why can't I just create simple lookup from 1 source to 2 destination tables?

 

[ajetrumpet]

if I link table 1 to table 2 and 3 like you said - when I click on a plus sign I get pop up "insert subdatasheet" where I must select table 2 or table 3.

Well that makes sense. It probably can't decide which datasheet to display, because they are both dependents. And that's the way it should be.

When I create a report I include the field that relates from table 1 called "KIO number" then add 2 fields from tables 2 and 3 I should be able to get a report like this:

---------------------|-----------------|------------------------
"Table 1 KIO number" |  Table2 field KIO |  Table3 field KIO
---------------------|-----------------|------------------------
1                    |       1         |
2                    |       2         | 
3                    |                 |         3
4                    |                 |         4
5                    |       5         |

Why? What's the purpose here? I have no idea how you would do that, but I would guess that querying those three tables like that, even with JOINS would not work. It would probably yield a blank set too. I don't know why, but isn't there any other way you can do this? How about more than one query?