Joining two tables on two fields

[Reader]

Hello all,

I am joining two tables in a query and the output should be all those
records where two fields in table1 match two corresponding fields in
table2.

I joined the tables using both fields in design view and the Select
statement in SQL view looks good. The query runs perfectly and shows
the result I want but when I save the query and close it, re-opening in
design view shows the error message "Microsoft Office Access can't
represent the join expression {Table1].[Fieldx] = [Table2].[Fieldx] in
design view." This is okay as long as it does not affect the query but
when I run it, I am left with a join on only one of the fields and the
other join (that of Fieldx) is gone.

My trouble is that I am not the only user of this database and someone
might accidentally open this in design view and change my query
unknowingly. Is there a way I can retain the query as using both fields
to join both tables?

Any ideas are very welcome.
Thanks!

[Allen Browne]

A 2-field join is quite a common scenario, so there must be another factor
in this as well.

Suggestions:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of the Name AutoCorrupt junk:
Tools | Database Utilities | Compact

3. Open the 2 tables in design view, and check that the Data Type and Size
of both tables match. For example, the fields are Text type, 50 characters,
then the same field in the other table should ideally be Text, 50 characters
as well.

4. Consider creating a relation between the 2 tables. Choose Relationships
on the Tools menu. Drag Fieldx from Table1 onto Fieldx in Table2. Access
pops up a dialog. Enter the 2nd field on the 2nd row of the dialog to make
the 2 field relation. Check the box for Referential Integrity (RI).

5. Make sure you have the latest service pack for your version of Access and
for JET 4. Both are available from:
http://support.microsoft.com/gp/sp

If you succeeded in matching the field types and sizes, and creating the
relation with enforced RI, and you have JET 4 SP8, Access should have no
problem with the multi-field join in the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Reader" <co*********@hotmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

Hello all,

I am joining two tables in a query and the output should be all those
records where two fields in table1 match two corresponding fields in
table2.

I joined the tables using both fields in design view and the Select
statement in SQL view looks good. The query runs perfectly and shows
the result I want but when I save the query and close it, re-opening in
design view shows the error message "Microsoft Office Access can't
represent the join expression {Table1].[Fieldx] = [Table2].[Fieldx] in
design view." This is okay as long as it does not affect the query but
when I run it, I am left with a join on only one of the fields and the
other join (that of Fieldx) is gone.

My trouble is that I am not the only user of this database and someone
might accidentally open this in design view and change my query
unknowingly. Is there a way I can retain the query as using both fields
to join both tables?

Any ideas are very welcome.
Thanks!

[Steve]

That error message usually occurs when FieldX has been deleted from one of
the tables or FieldX has been renamed in one of the tables. Check your
tables to see if this is the case.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1125 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com
"Reader" <co*********@hotmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

Hello all,

I am joining two tables in a query and the output should be all those
records where two fields in table1 match two corresponding fields in
table2.

I joined the tables using both fields in design view and the Select
statement in SQL view looks good. The query runs perfectly and shows
the result I want but when I save the query and close it, re-opening in
design view shows the error message "Microsoft Office Access can't
represent the join expression {Table1].[Fieldx] = [Table2].[Fieldx] in
design view." This is okay as long as it does not affect the query but
when I run it, I am left with a join on only one of the fields and the
other join (that of Fieldx) is gone.

My trouble is that I am not the only user of this database and someone
might accidentally open this in design view and change my query
unknowingly. Is there a way I can retain the query as using both fields
to join both tables?

Any ideas are very welcome.
Thanks!

[StopThisAdvertising]

"Steve" <no****@nospam.spam> wrote in message
news:6T*************@newsread2.news.atl.earthlink. net...

--
PC Datasheet

To the original poster:

Most people here have a common belief that the newsgroups are for *free
exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell
his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Keith.