Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access Query Question

Posted on 2008-10-29
1
Medium Priority
?
178 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
1 Comment
 
LVL 16

Accepted Solution

by:
HooKooDooKu earned 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

971 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