473,320 Members | 2,004 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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.

3 1585
NeoPa
32,556 Expert Mod 16PB
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
Thank you NeoPa and yes I agree with your design comment.
Nov 19 '17 #3
NeoPa
32,556 Expert Mod 16PB
I'm glad I was able to help Rodney :-)
Nov 20 '17 #4

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

Similar topics

6
by: Mark Findlay | last post by:
I am trying to figure out how to set up my reg exp search so that the search will only match on the exact word. Here is the current problem code: Word1 = "RealPlayer.exe" Word2 = "Player.exe"...
1
by: Dominic Marks | last post by:
Hi, (I apologise if this is the wrong list, I haven't posted to a postgresql.org mailing list before, general seemed like a good catch-all) I am trying to implement a centralised...
6
by: AAVF | last post by:
Hi We have a problem with a query. An Access database links via ODBC to a UNIX server. To speed things, we use the ODBC to load the relevant tables to the local PC that runs Access so that...
3
by: baka | last post by:
Dear Sir/madam Here i am having some one sql which returns more rows than the required rows after comaparing tables simple sql statement will be lokk like SELECT t.empcd, s.daicd, s.chucd,...
12
by: veaux | last post by:
Question about joins in queries. I have 2 tables with a field called "ID". Table 1 Rec1 = Jan12FredFlintstone Rec2 = Feb01WilmaRubble Table 2 Rec1 = Jan12BarneyRubble Rec2 = Mar03SamSlate
1
by: dhillarun | last post by:
Hi all, I am displaying an applet and passing values to it by using a php file. I am sending the values to the applets by using PARAM (HTML) tag. But , I want to get some values into PHP...
0
by: Dhananjay | last post by:
hi all, I am facing problem in programming.Can anybody please let me know how to solve the problem. I have a link, clicking the link opens a page in which there are some data which is coming from...
0
by: bndprasad | last post by:
how to retrive javascript function return values into code behind
0
by: Raviexact | last post by:
How to replace only 1d values in 2d array after filter using numpy in python without loop i.e in pythonic way. I want to filter only t2 rows and replace values in second column ( middle column )....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.