Creating a query that tracks changes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i need help creating a query that will track where and when something has
changed from year to year. i have 2 databases with lists of names and
categories. i need to see where the changes are between the years, what is on
one and not the other, and if the category changed. the other problem i'm
having: one list of names are all caps the other has a combo of all caps and
all lower.
 
Access isn't case sensitive like Oracle so you shouldn't have a problem there.

By databases do you mean tables or do you have two different Access databases?

If you mean tables, go to the database windown and select queries. Select
New and then Unmatched Query Wizard. Give that a try.
 
No that didn't work, that's were the case problem happens. Unless I'm setting
up the query wrong it's returning all of the names in lowercase, but they are
the same just different cases. This is what I have- 2 excel spread sheets
that I imported into Access so I have 2 tables with data from 2006 & 2007. I
ran the unmatched query on the names, it returned 500+ names when I compared
to the original they were the same just different case and some were
different but I had to manually compare the results to pick out the
duplicates.
 
Did you import the spreadsheets into tables or did you link to the
spreadsheets? If you linked then there is the possibility that the ODBC
driver distinguishes case sensitivity. Also, if you linked or imported
the fields could have spaces at the end.

You could force both name fields into upper case using UCase against
them and then run the unmatched query against two queries that you have
used.

Or something like the following

SELECT TableA.*
FROM TableA LEFT JOIN TableB
ON UCase(TableA.NameField) = UCase(TableB.NameField)
WHERE TableB.NameField is Null

If that still gives you unexplained missing values then try applying
Trim to both fields in the join in addition to the UCase function.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Back
Top