Display related table information on a form

[chrisg101]

I'm fairly new to access but have a little bit of database experience in the past. Basically I have two tables.
Job table (includes job number, title etc.)
Client table (includes name, address, details etc)

A job can only have one client so it is a one to many relationship. However sometimes we do internal jobs that do not have clients. The key I'm using to relate the two tables is called "Client Code" which is a short text field.

My company insists that we must be able to have no entry into the client code in the jobs databse. My question is this.

When I've created a form (for entering new jobs etc) that displays job and client information the form's query statement links the two tables and ignores all records that have no entry for client code. I understand why this is happening because the relationship is being enforced. It also will not let me enter a record without including the client code. But I need the form to display all job records, including those with no client. If there is no client info I obvioulsy just want the client fields blank.

Can anyone give me some help.
Much appreciated.
Chris.

 

[Darrylles]

Hi Chris,

Your company can insist all they like that they don't want a client code for internal jobs - point of the matter is - they need one.

I understand that you can exclude company jobs because there is no client - code, but it's just as easy to treat everything the same and exclude company jobs by using criteria such as 'client_code <> 1' where '1' is the code for your company.

The client for internal jobs is your company, enter your company as a client record, you then have a code.
You can then use exactly the same functionality to retrieve records in all instances, except where you don't want to see your company jobs - you exclude the client code for your company. (This will NEVER change anyway).

This is how I do it.

Regards,

Darrylle

&quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk

 

[JeremyNYC]

Make the join in the query be an outer join. Then you can include everything from one table and only those records that match from the other table.

But if it's a one-to-many relationship, shouldn't you be displaying the clients in a subform?

If you post more complete definitions of your table, and the sql from your query, it will be easier to decipher.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[chrisg101]

Thanks guys. That should do it.
Cheers, Chris.