Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access Query Question

Posted on 2008-10-29
1
Medium Priority
?
177 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 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 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