By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,248 Members | 1,979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,248 IT Pros & Developers. It's quick & easy.

How do I match up to 6 fields from 2 or more tables and only return values that match

P: 2
In Access 2010 - I have saved 4 tables with identical fields. I am looking for criteria to match:
Job Number and Size 1 and Sch 1 and Size 2 and Sch 2 and description.

All must match to have a unique value. There will be many of the same values in all fields, but only when these values are combined I get a "unique" value or inventory part in this case.

At this point I have combined all 6 fields into 1 calculated field to accomplish what I need. That seems a little archaic. I have also joined the fields in a query, but non populated fields affect the results.

Please advise on best route to accomplish this type of task.
Nov 18 '17 #1

✓ answered by NeoPa

I suspect I don't understand what you're asking for but to match records from two tables with the same-named fields - and there are multiple of those - you would use the following in your FROM clause :
Expand|Select|Wrap|Line Numbers
  1. FROM   [Table1]
  2.        INNER JOIN
  3.        [Table2]
  4.   ON   [Table1].[FieldA]=[Table2].[FieldA]
  5.  AND   [Table1].[FieldB]=[Table2].[FieldB]
  6.  AND   [Table1].[FieldC]=[Table2].[FieldC]
  7.  AND   [Table1].[FieldD]=[Table2].[FieldD]
OTOH It almost never makes sense in a database to have multiple tables with the same fields defined. That's generally a clue someone doesn't have a very good understanding of database design concepts when you come across databases with such a design.

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,419
I suspect I don't understand what you're asking for but to match records from two tables with the same-named fields - and there are multiple of those - you would use the following in your FROM clause :
Expand|Select|Wrap|Line Numbers
  1. FROM   [Table1]
  2.        INNER JOIN
  3.        [Table2]
  4.   ON   [Table1].[FieldA]=[Table2].[FieldA]
  5.  AND   [Table1].[FieldB]=[Table2].[FieldB]
  6.  AND   [Table1].[FieldC]=[Table2].[FieldC]
  7.  AND   [Table1].[FieldD]=[Table2].[FieldD]
OTOH It almost never makes sense in a database to have multiple tables with the same fields defined. That's generally a clue someone doesn't have a very good understanding of database design concepts when you come across databases with such a design.
Nov 19 '17 #2

P: 2
Thank you NeoPa and yes I agree with your design comment.
Nov 19 '17 #3

NeoPa
Expert Mod 15k+
P: 31,419
I'm glad I was able to help Rodney :-)
Nov 20 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.