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*********@ho tmail.com> wrote in message
news:11******** **************@ i40g2000cwc.goo glegroups.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!