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

How to Compare fields across 2 tables when several other fie

Oct 13, 2011 1:00AM PDT

I admit I am a lightweight and I am still using Access 2000, but I hope that means a lot of you will know the answer to this question.
I have two tables (one older) with traffic sign information. They each have 8 fields. I want to do something like an Unmatched Query between the fields that define what each SIGN means when 4 other fields are equivalent.
In other words, when the MAIN STREET, FROM STREET, TO STREET and SIDE OF STREET fields are the same in both tables, I want to compare the SIGN fields to see the changes. Basically, I want to see the changes on the same block and side of the street between the 2 tables.
I think I need some kind of when MAIN STREET = MAIN STREET, FROM STREET = FROM STREET, etc. conditions are met, what are the unmatched fields in the SIGN field, but like I said, I am at a loss.
Any help would be greatly appreciated.

Discussion is locked

- Collapse -
Answer
Re: comparing
Oct 15, 2011 6:33PM PDT

First I'd make a query which is an inner join between the old and the new table. It has 12 fields in total:
- the four identifying fields
- the four other fields from the old table
- the four other fields from the new table

Then based on that query, use other queries to find out whatever you want to find out.

Be sure to make a new index for those 4 fields combined on both files to speed up the inner join.

Kees

- Collapse -
How to Compare fields across 2 tables etc,
Oct 16, 2011 10:22AM PDT

Thanks Kees_B for your response. It may be that I don't fully understand your strategy. What happened was I made 4 inner joins for MAIN STREET, FROM STREET, TO STREET and SIDE OF STREET for each of the 2 Tables. Each Table has about 23,000 row. The resulting query generated a Table of 350,000 rows, This is probably because there are always a handful of signs for every SIDE OF STREET and Access matched them across on every instance (sometimes several times). Then I still have the problem of finding only those signs that are different between the 2 tables which I don't see how to do.
Maybe if I explain it better with an example: say, in each Table, on the south side of Main St between Nebraska St and Alaska St are 10 signs. Two of the signs have changed in the time since the first Table was made. I need a list of all the signs and their info for the newest Table ( a row for each sign) and at the end a list of the signs from the earlier Table so I can compare them.
What do you think?

- Collapse -
I see the problem now.
Oct 18, 2011 6:33AM PDT

Thanks for explaining that.

So you want to compare sets of data for the same side of the same street. What are the other 4 fields? What are the rules to compare an old set of 2 and a new set of 4 traffic signs with the same 4 identifying fields?
Can you give a concrete example of the output you would want to have for a few cases.

I'm afraid this needs programming anyhow. Depending on what exactly you want to see and the tools he uses, I think it would take a programmer 1 to 2 days to do it for you.

Kees

- Collapse -
I am so close
Oct 19, 2011 9:01AM PDT

I am already very close.
The Streets have the same names in both tables. The same signs have the same names when they come up, so I ordered both tables according to the MAIN STREET, FROM STREET, TO STREET, and SIDE OF STREET. I automatically numbered the rows, did an Outer Join between the numbered fields, and now I have both Tables side by side in 1 table. The signs that were extra in one or other of the tables were placed in the beginning. This is almost alI need. I just want any extra signs to stay in the place of all the other signs for any particular SIDE OF THE STREET.
Another way to look at it is if every Side of the Street had a unique number, then I would do an Outer Join between those numbers.
When I am so close, how can it be so complicated as to require a day or two?