Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

Question

Microsoft Access Group. Compare Tables.

Nov 27, 2011 10:00AM PST

Hello,
I have been told what I want to do cannot be done with Tables. Now I am
wondering if it can be done in a report. I am trying to combine 2 Tables
along a common field ID, but there are several issues. Each ID
represents a city block and on each block there are many traffic signs
(many rows). Table_OLD lists an ID (identifying the block) with a row
for each sign at, say, a year ago. Table_NEW lists an ID (identifying
the block) with a row for each sign as they are currently. I want to see
them side-by-side, but sometimes a particular ID (block) will have more
signs in Table_OLD. Other times it will have more signs in Table_NEW.
So with some IDs (blocks) there will have to be blanks in the Sign
Description field on the Table_OLD side and at other IDs, there will
have be blanks in the Sign Description field on the Table_NEW side.
I tried a UNION (full) join of the Tables on the ID field, but it
creates many duplicate rows because each particular ID in each Table has
many entries. When I tried to put both Tables in a report side-by-side,
Access insisted I link them and then created the same duplicate fields.
The idea being that the list of signs align (are grouped by?) along the
same ID number and leave blanks where there are fewer signs in one table
or the other.
I can physically put the two Tables next to each other so it seesm logical that Access could so it in a much neater way.
Thanks for looking at this.

Discussion is locked