Relationship Problem

[computergeek]

Hello,

Table 1:
Assem_ID Autonumber
Tag_ID Number (long Int) Lookup on Table 2
Description

Table 2:
Tag_ID Autonumber
Tag_Name

Table 1 Data:
Assem_id Tag_id Description
5 1 asm5tag1
6 2 asm6tag2
7 3 asm7tag3
8 4 asm8tag4

Table 2 Data:
Tag_id
1
2
3
4

Defined relationships: None (there was a one-to-one defined)


I am updating an existing database. When I join the 2 tables above I get:
assem_id table1.tag_id table2.tag_id
6 2 1
7 3 2
8 4 3
(I selected: Only include joins where both fields are equal)

I expected:
assem_id table1.tag_id table2.tag_id
5 1 1
6 2 2
7 3 3
8 4 4

DOES ANYONE KNOW WHAT IS GOING ON? Thanks.

Computergeek

 

[HandsOnAccess]

I recreated the above scenario.

I have joined tag_id from table1 with tag_id from table2.

When i run the query, I am getting your expected result i.e.,
assem_id table1.tag_id table2.tag_id
5 1 1
6 2 2
7 3 3
8 4 4

 

[computergeek]

Hi,

I got the expected result too (in a new database)... I just removed the lookup on the tagId within table1, and now my query works as expected? Confused. They will need the lookup within the table view to enter data.

Thanks,

Computergeek

 

[Lilliabeth]

They will need the lookup within the table view to enter data.

No, they don't. Make a form for them.

Lookups on tables are very unpopular.

http://www.mvps.org/access/tencommandments.htm

 

[PHV]

Have a look here:

http://www.mvps.org/access/lookupfields.htm

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

 

[computergeek]

Hi,

Thanks Lilliabeth, and PH. I removed the lookup, and I am in the midst of creating a form for them to input data.

Computergeek