compare tables

[JCAA]

I am trying to do a comparison between 2 different tables in Access. I need
to compare the difference between historical and forecasted values which are
stored in different tables. I managed to make a union between the 2 tables
but I need to know what data comes from what table and what the difference
is both in numbers and percent. This is the union query I have so far:


SELECT [Trend], [Amount]
FROM [FFF - History]
WHERE [Month]="MAY"


UNION SELECT [Trend], [Amount]
FROM [FFFb - Forcst]
WHERE [Month]="May"


All help is greatly appreciated!

Thanks!

 

[RickSpr]

To determine which table each row comes from, simply add a distinct literal for each to the corresponding SELECT clause. For example,
SELECT "History" As TableName, [Trend], [Amount]
.
.
.
UNION SELECT "Forecast", [Trend], [Amount]

However, are you sure a UNION query is what you want? It seems to me that you'd have an easier time if the History and Forecast information were in the same row. For that, you'd want an inner join, and you could include the difference and percentage calculations as part of the query:
SELECT [FFF - History].[Trend] AS HistoryTrend,
[FFF - History].[Amount] AS HistoryAmount,
[FFFb - Forcst].[Trend] AS ForecastTrend,
[FFFb - Forcst].[Amount] AS ForecastAmount,
[FFF - History].[Amount] - [FFFb - Forcst].[Amount] AS Difference,
([FFF - History].[Amount] - [FFFb - Forcst].[Amount]) / [FFFb - Forcst].[Amount] * 100.0 AS DiffPct
FROM [FFF - History] INNER JOIN [FFFb - Forcst]
ON [FFF - History].[Month] = [FFFb - Forcst].[Month]
AND [FFF - History].[join field 2] = [FFFb - Forcst].[join field 2]
AND [FFF - History].[join field 3] = [FFFb - Forcst].[join field 3]
(etc.)
WHERE [FFF - History].[Month] = "May"

I don't know what other columns you need to match on in the join's ON clause--I suspect Trend may be the only one, other than Month.

One caution: I've assumed that the forecast amount will never be 0. If it is, the calculation of DiffPct will crash with a division by 0. That can be fixed, if necessary.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Leslie67]

Compare records

Is there a quick way to compare a record in two tables to determine if any changes have been made?

I have two tables with 16 fields. They *should* be identical. And I know for a fact that the first 3 fields of each table will never change.

What I want to do is compare the other 13 fields for any changes. Is there an easier way to do this other than by stringing a whole bunch of "OR"s together?

 

[Leslie67]

Sorry... I meant to put that in a new thread. Please ignore.