I have two tables with identical autonumber values that I need to run a report from. These are identical tables for different years with the year and the auto number becoming the identifier of the record.
I need a query that will pull all open records from both tables into one report.

I assume that both tables are contained in the one database. If they are not, attach or link one of the Tables. Next, use a Union Query to link the two tables together. It is very straight forward provided you select the exact same fields in your query on both sides of the Union statement. See below...

SELECT tb_CR_Shared.Client_No
FROM tb_CR_Shared;
UNION SELECT tb_CR_Shared1.Client_No
FROM tb_CR_Shared1;

Good Luck.

Pat

Thanks that worked great! I have one small problem, the two tables in the same database, use the last 2 digits in the year as part of the identifier quote number, I have an input mask set up in the year field of each. 0/1 in the 2001 table, 0/2 in the 2002, these numbers do not carry over to the union they come up 0, what is the best fix.

Hi.. I am glad it worked for you. Two possible solutions you might try.

1. Insert the word 'Distinct' on both sides of your Union Query.

SELECT DISTINCT tb_CR_Shared.Client_No
FROM tb_CR_Shared;
UNION SELECT DISTINCT tb_CR_Shared1.Client_No
FROM tb_CR_Shared1;

Try this. Otherwise, you might have to declare the data type of your Field on both sides of the Union Query.

eg From Above...

tb_CR_Shared.Expr1:Cdbl([Client_No]) FROM tb_CR_Shared; etc...

Using this process, it should show the final two numbers.

I think that you should reconsider your database design. Keeping each year in a separate table FORCES you to make changes to the queries, forms, reports EVERY year. Although this type of design guarentees a certain amount of job security it really should not be used except in cases where the tables get so large that they impede day-to-day performance and therefore, data needs to be archived to get past the operational problems.

Keep a single table that contains all the data. Keep a date added field so that you can tell when a row was added. Use a standard autonumber datatype to assign the unique record ids. You probably designed the system this way originally because you wanted the autonumbers to restart at 1 on Jan 1. There are other ways around this depending on how much code you are willing to write (search the archives for MANY samples). But, when you get right down to it, it really doesn't matter what the autonumber value is as long as it is unique. I would hazard a guess that records 0001-01 and 0001-02 have nothing to do with each other and there is no need to know that a particular record was the 121st entered for a given year (you could calculate that piece of data anyway as long as the autonumbers are sequentially assigned).

[This message has been edited by Pat Hartman (edited 01-07-2002).]