Web Development



Access Query not doing what I need

By alcottrj ·
I have a database that has several tables that are all linked with a relationship on a field that contains a unique numeric ID. I need to run a query that pulls data from two of the tables and all attempts so far are not giving me the output that I need.

Table A has several fields including the unique ID and has over 1000 records in it. Table B has about 5 fields one of which is the unique ID. Table B only has about 500 records, not all the unique IDs from Table A are in Table B.

I would like my query to give me a listing of all 1000+ records from Table A and one or two of the fields from Table B, but if the unique ID isn't in Table B those fields would just be blank.

The query that I have is only matching the 500 records that match up between the tables and is not reporting the rest of the Unique IDs from Table A. Basically if the Unique ID is not in Table B I need to know which one. I could compare the two and figure it out manually, but I need it to be dynamic as I make changes to Table B.

Any help is greatly appreciated.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

right click

by Dr Dij In reply to Access Query not doing wh ...

on a link between files (in query design)
left click on 'join properties'

click on #2 probably, which means all recs from file on left and matches from file on right.

(if this is not the file you want all recs from click #3 instead, the reverse)
then click OK

Collapse -

Thanks a million

by alcottrj In reply to right click

That seems so easy I'm almost embarrased I asked the question. It was the third option on the join properties and that did exactly what I needed. Thanks for the help!

Collapse -

By default, it's doing

by Tony Hopkinson In reply to Access Query not doing wh ...

an inner join, ie returning only those records that exist in both (all three in your case) tables.
What you need is an outer join, type outer join into access's help it will tell you all about it.

Collapse -

outer join

by john.a.wills In reply to By default, it's doing

Specifically, you need a left join:

Collapse -

I knew that.

by Tony Hopkinson In reply to outer join

well actually I knew I could look it up again, I tend to fall into the old ways of *= and =* and then hit google or help, some habits die hard.

Related Discussions

Related Forums