Help merging 2 tables

[MarkT]

I have 2 tables Site_2002 and Site_2003. These represent the same data from different years. Instead of continuing to create new tables each year, I would like to merge these tables into a single table.

What I’m looking to do is create a new table with a Fiscal Year column. Add the contents of Site_2002 and Site_2003 to the new table and at the same time set the values of the FY column to match whichever table the data is coming from.

Could someone give me an idea on how to start this? I’m not very good with SQL and I’m not sure which functions are needed to pull this off. I hope with a nudge in the right direction I can figure this out. Any pseudo code would be greatly appreciated.

[RobDog888]

If your using Access:
Add your new Fiscal_Year field to the first table and use it as the
base. Populate the new field with 2002. Then rename Site_2002
to something like Site_Data. Then select the Site_2003 table and
copy it and paste it to the Site_Data table name and select
Append data to existing table option. Then populate the
remaining empty Fiscal_Year fields with 2003.

If your using SQL server then:
Use the INSERT INTO statement.
Create the new filed Fiscal_Year in 2002. Populate it with 2002.
Then create a default on that field of "YEAR(GetDate())".
Then execute a statement like ...
INSERT INTO
Site_2003 (Col1, Col2, Col3...)
VALUES
(SELECT * FROM Site_2003)

[MarkT]

Thanks for the suggestion. This is a SQl db. What I was looking into was adding a Fiscal_Year to both of the tables and updating it with the correct year. Then I was going to try to use a Union between the 2 table to create the final table. Does that seem like it may work?

[RobDog888]

Yes, that sound ok. Are you going to keep every year in
separate tables? A union may end up being too resource
intensive once you need to query the union for reports, etc.
May be better to have the one table with all records in it then
just have to execute one query on the table. Not union the two
tables and then execute the query on it. Just something to keep in mind.