Creating a query that tracks changes

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"owliyz28" wrote:

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.

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.

"Jerry Whittle" wrote:

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"owliyz28" wrote:

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.

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
'================================================= ===


owliyz28 wrote:
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.

"Jerry Whittle" wrote:

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"owliyz28" wrote:

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.