Web Development

Ask something
Question Locked

Access Query not doing what I need

0 helpful
6 replies

by alcottrj

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

6 replies

by dr dij

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

by alcottrj

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!

by tony hopkinson

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.

by john.a.wills

Specifically, you need a left join:
SELECT ... FROM A LEFT JOIN B ON A.ID = B.ID

by tony hopkinson

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.

This discussion is closed to new replies.