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

Table Relationship with Blank Values

mjoachim
P: 33
I am working on a query that'll find invalid entries. My users enter payroll data into a form and the form only allows them to enter active Cost Code & Job/Sub Job combinations, but often enough they change something after initial entry that results in an invalid combination.

In my query, I have a relationship created between my Data Import(Payroll entries) table and a table of current/active cost codes (see attached). With that relationship, I pull all entries from my Data Import table and only the Cost code field from the Cost Codes table if there is a match. This allows me to see if a match is missing and therefore invalid.

This works great if an entry has a sub job. The problem is if there is no sub job, that field in both tables is blank which doesn't appear to create a match for the relationship.

Is there some way to specify in the relationship that blank should match blank, or add a Nz( ,0) statement of sorts? I don't really want to store a 0 value in the tables, so am seeking out a way to create a match for comparison only.

Thanks!
Attached Images
File Type: jpg Table Relationship.jpg (44.6 KB, 103 views)
Dec 15 '16 #1

✓ answered by jforbes

This won't be the most efficient Query, but it will probably do what you want:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM [Data Import] 
  3. LEFT JOIN [Cost Codes] 
  4. ON  [Data Import].[Job Number] = [Cost Codes].JobNumber
  5. AND [Data Import].[Cost Code] = [Cost Codes].CostCode
  6. AND [Data Import].[Sub Job] & '' = [Cost Codes].SubJobNumber &''
I think the same thing can be accomplished with a couple NZ()s:
Expand|Select|Wrap|Line Numbers
  1. Nz([Data Import].[Sub Job], "") = Nz([Cost Codes].SubJobNumber, "")
Maybe you can tell us which works better. =)

Share this Question
Share on Google+
2 Replies


jforbes
Expert 100+
P: 1,107
This won't be the most efficient Query, but it will probably do what you want:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM [Data Import] 
  3. LEFT JOIN [Cost Codes] 
  4. ON  [Data Import].[Job Number] = [Cost Codes].JobNumber
  5. AND [Data Import].[Cost Code] = [Cost Codes].CostCode
  6. AND [Data Import].[Sub Job] & '' = [Cost Codes].SubJobNumber &''
I think the same thing can be accomplished with a couple NZ()s:
Expand|Select|Wrap|Line Numbers
  1. Nz([Data Import].[Sub Job], "") = Nz([Cost Codes].SubJobNumber, "")
Maybe you can tell us which works better. =)
Dec 16 '16 #2

mjoachim
P: 33
I would say they both work equally as well. Slow and inefficient as you expected, but nonetheless accomplishes what I asked.

As always, thank you for your help jforbes!
Dec 16 '16 #3

Post your reply

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