question on tables and queries

P

pete0085

I've imported 2 tables from excel and ran a query against those two tables to
determined the difference.

This is something I would like to do each month. I would like to keep the
same settings in my database, is there a way I can easily import/update the
data in the new table without creating a new one each month?

The query would stay the same as I'm comparing the same two files, but the
the data will be changing every month. I don't know much about access and
would like to know if there is an easier way of doing this besides creating a
new table and query each month?

I'm working with excel files and using Access 2003.
 
G

George Nicholson

If the field names in the 2 tables will remain the same each month (or if
they can be manipulated to be the same before you import), then create 2
delete queries (one for each table) to empty this month's data. This gives
you 2 "shell" tables to import into next month (rather than create new
tables, import into the shells). Since your existing query already refers to
those 2 tables, all you need to do is run the query once you've done the
import.

Monthly Steps:
-Delete last months data from Access tables (but not the tables themselves)
-(Optional) Manipulate Excel columns to match FieldNames expected by Access
-Import Excel data into existing Access tables
-Run query

If you need to retain the data you have imported, rather than compare &
discard, it becomes more complicated, but I'm sure the process can still be
simplified depending on what needs to be kept, etc.
 
P

pete0085

The fields will always be the same, just the numbers/data will change.

I tried to delete the data in the tables and did an import and choose the
option to use an existing table.

I added a couple records and re-ran the query by double clicking on it, but
the results were the same as before. Is there something different I need to
do to re-run the query?
 
P

pete0085

To clarify my question. I have two tables I compare. If I change data in
one table the difference shows up, however, if I change data in the second
data, it doesn't display the difference.

Would I need to run 2 different queries to accomplish this?
 
G

George Nicholson

Would I need to run 2 different queries to accomplish this?
If by "differences" you mean "records in one table but not the other", then
yes, you would need 2 queries.
However, you could then create a 3rd UNION query that combines the results
of the 1st 2 queries so that all differences appear together. (If you go
that route, consider adding a "NonMatch" field to your queries that state
"In A but not B" and "In B but not A" to preserve your sanity.)

If "differences" means records in both tables with the same unique or
primary key but have different data in some other field(s), then you should
be able to do that in one query.
 
P

pete0085

How would I create this 3rd query?

George Nicholson said:
If by "differences" you mean "records in one table but not the other", then
yes, you would need 2 queries.
However, you could then create a 3rd UNION query that combines the results
of the 1st 2 queries so that all differences appear together. (If you go
that route, consider adding a "NonMatch" field to your queries that state
"In A but not B" and "In B but not A" to preserve your sanity.)

If "differences" means records in both tables with the same unique or
primary key but have different data in some other field(s), then you should
be able to do that in one query.
Click to expand...
 
Top