Solved

MS Access Query Question

Posted on 2008-10-29
1
172 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 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query 8 48
Problem with duplicate records in Oracle query 16 25
Regular expression help 2 22
SQL Server 2012 r2 - Query results have 2 seperate results instead of 1 2 22
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

805 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