Access help needed! Over my head!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

daveallston@rogers.com - 16 May 2008 14:06 GMT

Hello,
Hoping someone can help me out, would be much appreciated. Here is
what I am trying to do:

I have several tables, each with 46,000 rows of data. I need to
analyze it in a couple of different ways. I am very strong with
Access, but some of this query-building I need to do is blowing me
away a bit. Hopefully an expert on here can help me out! Would
appreciate it very much!

Is there a way (possibly in MS Excel, if not Access?) to compare two
individual tables or sheets, and have a resulting table showing a list
of every row of data where a change occured in one or more cells? My
example is I have a table of data from Jan 1, and one from Apr 1, and
I need to find where changes have been made between the two dates, in
any one of the 50+ columns. I have tried using the "find unmatched
query wizard" in Access, but that will only find me the lines of data
where there is no match between sheet A and B for the "matching field"
I select. I want to have a query or search run that returns every line
where information was added or modified, between sheet A and B.

And then further to this, and this might be making it too complicated,
I don't know... is there a way to get a count of the number of changes
by "groupings" (i.e. if there are 400 rows for city A, 500 rows for
city B, 300 rows for city C, and 500 rows for city D, and there were a
total of 75 rows where data was changed between the Jan 1 and Apr 1
sheets, can I run a count to see how many of the 75 rows were city A
vs city B vs city C vs City D?

Thank you very much to anyone who can help me with this. Much, much
appreciated. I will monitor this group all day for any replies, so if
you need more information, or have any quesitons, please let me know.

Cheers,
Dave

Reply to this Message

Michel Walsh - 16 May 2008 16:27 GMT

If John decides to get a sex-change and become Mary, we will have a hard
time to 'match' the original John-record with the new Mary-record, ...
UNLESS ... there is some 'id' which did not change between the two data set
we have to compare. SO:

SELECT old.id,
old.city,
ABS( old.f1 <> new.f1
+ old.f2 <> new.f2
+ ...
+ old.f51 <> new.f51 ) AS numberOfChange

FROM old INNER JOIN new
ON old.id = new.id AND old.city = new.city

GROUP BY old.id, old.city

can do the job. I assumed the two tables names were old and new. I also
assume you did not use NULL in any of the fields f1 to f51.

Hoping it may help,
Vanderghast, Access MVP

-------------------------------------------------
If there are null, say under f1, then change:

old.f1 <> new.f1

to

Nz( old.f1, 9999) <> Nz( new.f1, 9999)

where 9999 is a value not found under f1

That assumes that if both are null, we can consider there is no change, but
if one is null and the other is not, we consider there is a change.

> Hello,
> Hoping someone can help me out, would be much appreciated. Here is
[quoted text clipped - 31 lines]
> Cheers,
> Dave

Reply to this Message