multiple relationship between two tables 1

[jozino01]

i have two tables and want to create relationship between:

table1.field1 and table2.field1
table1.field1 and table2.field2

when i tried to do it, access 2000 created table1_1 in relationship screen. i am just designing database without data so i didn't test it yet.

i want to omit any possible issues late so i want to be sure is ok.

is it possible to do it some other way?
if yes, would it cause any issue later?

 

[PHV]

So, in table2, field2 is a duplicate of field1 ? Why ?
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jozino01]

no, there are no duplicate fields.

table1 is table containing manufacturing processes (sawmill)
it looks like this:
RERUNID NUMBER CUTDATE
1 JAG 5-25 5/01/06

table2 is table containing material (lumber) - some lumber is resaw more then once.
field REMANIN indicates lumber going to be resawed
field REMANOUT indicates outturn from remanufacturing

LOTID CUTID REMANIN REMANOUT SIZES GRADE LOT VOLUME PRICE REVENUE PERCENTAGE INVOICE CUSTOMER
84 0 1 0 12 X 12 (20) 2 MER & BTR JGH 4200 24960 $560.00 $13,977.60 1.91% 988 SB9348

i tried to design database for lumber processing - the issue is that some is sold right after the first cut and the rest (unsold) is remanufactured - some pieces once, some twice, ...

 

[PHV]

Ah, table2 has 2 different ForeignKeys referencing the same table.
You thus need 2 different instances of table1 in the relationships window, its normal.
Remember that when writing queries joining table2 and table1:
SELECT A.LOTID, A.CUTID, A.REMANIN, I.CUTDATE, A.REMANOUT, O.CUTDATE
FROM (table2 AS A
LEFT JOIN table1 AS I ON A.REMANIN = I.NUMBER)
LEFT JOIN table1 AS O ON A.REMANOUT = O.NUMBER

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jozino01]

thanks