How do I Join Archived tables and Active table through Queries

[Guest]

I recently inherited 3 databases from the person that was previously
maintaining them. I've been through a level 1 course in Access 2003 and when
i asked the instructor she said that they don't cover these types of things.
I need to know how to run a query off of 2 identical tables only one of them
is labled "Archived". I was told by my instructor that it was a type of
union query and someone on here should be able to answer my question. If
someone could please get back to me soon i would greatly appreciate it.

Thank You

 

[Guest]

Hi,

It is a UNION or UNION ALL query. Unfortunately you can't do a Union query
using the QBE grid. Instead you have to make it using SQL code. Assuming that
the tables are identical EXCEPT for the table names, do this.

1. Create a query from either table. Doesn't matter which. Run it to make
sure it's working as expected.

2. Open the query in design view. Next go to View, SQL View. This shows the
SQL for your query. Copy it using Ctrl + c.

3. Delete the semicolon ( ; ) at the end and then press the Enter key.

4. Type in the word UNION and press the Enter key.

5. Do a Ctrl + v to paste in the SQL from above. Modify this SQL by changing
the table name to the second table. Make sure that there's a semicolon at the
end.

6. Run the query which could look as simple as this one:

SELECT Original.*
FROM Original
UNION
SELECT Archived.*
FROM Archived;

Or something like:

SELECT Original.Month, Original.Year, Original.htn
FROM Original
UNION ALL
SELECT Archived.Month, Archived.Year, Archived.ASA
FROM Archived
ORDER BY 1, 2;

Notice that the field names don't have to match. There just needs to be the
same number and datatype.

Now for the difference between UNION and UNION ALL. A Union query does not
show duplicate records. For example both tables have a Smith John H and you
are only doing a query on the last_name, first_name, and MI. A Union query
will only show that name once whereas Union All will show the same name
twice. Also since Union All queries don't have to worry about finding
duplicates, they are much faster.

Also Union queries are just one of the reasons I usually recommend not
putting records in archived tables.

 

[Guest]

Doing what Jerry Whittle said will get you there but I would recommend that
you consider combining the two tables into one. First backup the database.

You would just need to add an Archived Yes/No field and append the archive
records. In your append query design view grid add a column like this --
Archived Records: -1

This flags all archive records. Then in all your queries use a criteria of
0 (zero) for the Archived field to pull only current records.

 

[punjab_tom]

Can Access even populate archive tables?

It doesn't have triggers does it?

-Tom

 

[Guest]

You are certainly right about the triggers. Most Access archive tables are
run manually with a combination of append and delete queries. I certainly
like Karl's idea of a yes/no field and using one table instead.