Solved

MS Access Query Question

Posted on 2008-10-29
1
175 Views
Last Modified: 2013-12-17
I have two ms access tables Clients and CareerCodes.

The CareerCodes table I have a clientkey column which corresponds with the appropriate client key in the clients table.

What i want to do is Select * From Clients and join the CareerName from the careercodes table.

I already started working on a query to do this but there is one problem. Sometimes the careercode in the client table is set to 0. In this case there will be no corresponding record in the careercodes table. My query will not bring back those client records because it expects a match on the join criteria.

I need to join CareerCodes.CareerName on Client.ClientKey = careercodes.ClientKey but when there is not corresponding record in careercodes i still need to list the clients record. I don't really care what shows up in the CarreerCodes.CareerName column because i will know there is no career when i see that the client.careercode = 0.

Could somebody give me an example query where it will bring back all of the clients even when there is not a corrisponding record in the career table. I am looking for a solution that will bring back one record per client regardless if they have a corresponding careercode or not, we can dummy in empty strings in the careername column when there is no corresponding record.

Thanks
0
Comment
Question by:talker2004
[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
1 Comment
 
LVL 16

Accepted Solution

by:
HooKooDooKu earned 500 total points
ID: 22833674
Is this as simple as making sure there is a record in the careercodes table with a value of zero to match on the Clients with a setting of zero.  (i.e. rather than having a table with a value that is essentially NULL, how about having the table point to a record that the corresponding record has a value of "Blank Record").
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

751 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