Help needed.

[jmp04]

Right i have three tables giving the following information.....
Tables:

Volume -1
Terms 2
Issue 2

Information:

Client Name
Policy Type
Policy Number
Producer Code


I need to create a Query that runs off all information for the Volume table compared to Terms and Issue at the same time, one with No Terms, and one with No Issue and one with No Volume.

I need to be able to match the data next to each other in the collums (where it is correct and exactly the same) and also be able to run off the failed items where they do not match, or put them at the bottom or something.

Havent used Access in years so havent got a clue where to start really.
Any help would be much appriciated.

 

[Atomic Shrimp]

Could you post a few rows of example data from each table (munged to obscure anything sensitive, if need be) - there's not quite enough information in your descriptions to be able to work out what is in your tables and how it might all join together.

 

[jmp04]

Image/data as requested. Bits blocked out obviously.
Thanks.

 

[Atomic Shrimp]

I typed out a lengthy reply and the message board software ate it... Grrr... I'll try to remember what I said....

Which field(s) in these tables are you looking to use to relate them to each other? It doesn't look like it should be ID, as this appears to be just a record identity not relating to anything outside of each individual table.
If it's Client Name you plan to use as the join, can you be sure you don't have duplicates? (i.e. not multiple records for Joe Smith, but multiple people who happen to have the same name).

Anyway, what I suggest you do is this:

Create a query in design mode
drop in all three tables
Choose the field in Volume that will link it to the other tables, click and drag this field name from the Volume table and drop it on top of the same field name in one of the other tables. This will create a join between the two tables.
Do the same to join Volume to the other table
Right click each of the joins in turn and select the option that says to show all records in the Volume table.
Double click the field names you want in your query from each table
Run the query and see what you get

You should end up with everything from the Volume table, plus matching bits from the other two tables, with blanks where there are no matching records.

Give that a go and let us know if you have any problems...

 

[jmp04]

Okay, gave it a go, her'es what i got.

I feel like a right access fool at the moment.