Solved

JOINING on TWO FIELDS in Microsoft Access 97

Posted on 2008-06-18
10
251 Views
Last Modified: 2010-04-21
I've designed a query in SQL view. I have two tables, and unfortunately, the keys that I am joining on are in two separate fields. An account and a user. I'm trying to make sure that the account/user pair exists in the other table. Why not use just the account? Because accounts can span unlimited users, so I have to specifically look for the account/user pair for the join. My code is below, and it only shows user and account from the first table. and not the account from the second table (diicust.Field40 = account)
SELECT inputfile.user, inputfile.account, diicust.Field40
FROM inputfile LEFT JOIN diicust ON diicust.Field40 = inputfile.account AND diicust.Field14 = inputfile.user;

Open in new window

0
Comment
Question by:MeridianManagement
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 21816089
>I'm trying to make sure that the account/user pair exists in the other table.
if you want to see which one does NOT exist on the other table:

SELECT inputfile.user, inputfile.account, diicust.Field40
FROM inputfile LEFT JOIN diicust ON diicust.Field40 = inputfile.account AND diicust.Field14 = inputfile.user
WHERE diicust.Field14 IS NULL;

if you want only those that are in both tables, use INNER JOIN

if you want only those that are in diicust but NOT in inputfile:

SELECT inputfile.user, inputfile.account, diicust.Field40
FROM diicust LEFT JOIN inputfile ON diicust.Field40 = inputfile.account AND diicust.Field14 = inputfile.user
WHERE inputfile.account IS NULL;
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 21816117
SELECT inputfile.user, inputfile.account, diicust.Field40
FROM inputfile
INNER JOIN diicust ON diicust.Field40 = inputfile.account AND diicust.Field14 = inputfile.user

Your SQL looks correct, other than I changed the LEFT join to an INNER join, which requires that both tables meet the join criteria.
A LEFT join returns all the records in inputfile, and only the related diicust values if the join criteria is met.

Are account and user the same data type in both tables?
 
0
 
LVL 2

Author Comment

by:MeridianManagement
ID: 21816126
why is my query not working though?
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21816164
please clarify "not working".
the SQL statement will only return what you ask it to do, which might differ from what you expect it to do.
0
 
LVL 2

Author Comment

by:MeridianManagement
ID: 21816219
My inputfile table has 14156 records
// this pulls up all the records in input file and according to angelll it's only supposed to pull the ones in the input file that aren't found in diicust. It's returned 14156 records.
 
SELECT inputfile.user, inputfile.account, diicust.Field40, diicust.Field14
FROM inputfile LEFT JOIN diicust ON (diicust.Field14 = inputfile.user) AND (diicust.Field40 = inputfile.account)
WHERE diicust.Field14 IS NULL;
 
// this I know doesn't make sense since I need the pair, but I was just proving that my table data matches. It returned 76,899 records.
 
SELECT inputfile.user, inputfile.account, diicust.Field40, diicust.Field14
FROM inputfile, diicust
WHERE diicust.Field14 = inputfile.user;

Open in new window

0
 
LVL 2

Author Comment

by:MeridianManagement
ID: 21816238
The outer left join is returning all records, but my second query proves that my data matches.

Would an index on these fields be screwing this up?
0
 
LVL 2

Author Comment

by:MeridianManagement
ID: 21816268
anymore test queries you would like me to run so you can investigate my table structures?
0
 
LVL 2

Author Comment

by:MeridianManagement
ID: 21816279
jimhorn, account and user are number (long integers) in both tables.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21816347
>but my second query proves that my data matches.
yes, but only on the user, but not on the account+user pair!

to check, find 1 account+user (manually) of which you know they match (eventually fill in such rows by hand).

then, run the query with limiting to that account+userid in either table, with a inner join.
if no records are returned, there is some issue with the data types.
0
 
LVL 2

Author Closing Comment

by:MeridianManagement
ID: 31468529
Angel, Thanks you were right. A weird zero was appearing on one of the tables, but it was an extra trailing zero! How weird! I exported into excel, used the left function and imported back in. I'm sure you could've told me an easier way but I didn't have much time to go back and forth. Anyway, thanks! You're the best.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question