I would like to be able to merge two tables on a comparative basis (e.g., amounts for two different years are in parrallel columns)without losing any of the records.

Suppose the two tables contain accounting information summarized by Fund and by Account_Number and these are not identical in both tables. That is, there some Fund and Account_Number combinations common to both tables and others unique to one or the other. I wish to pull the information together as follows:

Fund Account No. Year1_Amount Year2_Amount

As mentioned above, some of these combinations will only have a Year1_Amount or a Year2_Amount, but not both.

The approach I have previously taken is to create a list of the unique Account_Numbers, then pull the Year#_Amount's from both in to separate columns.

Is there a more elegant way to do this?

Thank you

I see that you need to structure this data into one table. How you approach that will be thru the use of select queries, append queries and update queries.

In the end, I would set the table as so:

FundID
(Account No should be in the FundID table)
Year
Amount


The data could be:

Record 1
Office Supplies
1999
$2000.00

Record 2
Office Supplies
2000
$1750.00

If you need some help converting your data into one table, I would be glad to look at it for you. I enjoy this kind of challenge.