Create a 2 year comparison report

Question

I would like to create a 2 year annual comparison report using financial data from a single table.  The data is based on a many to many relationship as there may be accounts used in eg: 2011 that will not be used in eg: 2012, and vice versa. I have created separate queries that return the desired data for each year, but can't seem to join them correctly to make it work in the report.

The report should list all account names in the left most column.  Column headings should be eg: 2011 and 2012.  The total annual amount for each account should fill the appropriate year heading.

Non of the available joins do the trick if I add the 2 queries into one query.

So far what I have attempted with make table queries and a junction table has not worked. Make table doesn't like that much.

Using Access 2007.

Any help would be greatly appreciated.

Connie

Answer 1

You could create a union query AllAccountNames that returns all account names:

SELECT [AccountName] FROM [QueryFor2011]

UNION

SELECT [AccountName] FROM [QueryFor2012]

Then create a new query like this:

SELECT [AllAccountNames].[AccountName], [QueryFor2011].[TotalAmount] AS Total2011, [QueryFor2012].[TotalAmount] AS Total2012

FROM [QueryFor2011] RIGHT JOIN ([AllAccountNames] LEFT JOIN [QueryFor2012] ON [AllAccountNames].[AccountName] = [QueryFor201].[AccountName]) ON [QueryFor2012].[AccountName] = [AllAccountNames].[AccountName]

Change the names to suit your situation.

Answer 2

Hi Hans,

Thanks for your help.  I have it figured out.  Someone added parentheses and it worked perfectly.

Answer 3

I don't understand why you have a PAST ACCT and CURR ACCT field in QryACAcctNames.

Apart from that, it's best to create the second query in design view. Access will then take care of the details of the SQL for you.

Answer 4

I created the above queries.  The union query works perfectly.  However, the select query keeps giving me error messages. The below is the actual statement used.  Queries are all actual. 

SELECT [QryACAcctNames].[PAST ACCT], [QryACPastYear].[PAST DEP] AS TOTPastDEP, [QryACCurrentYear].[CURR DEP] AS TOTCurrDEP

FROM [QryACPastYear] RIGHT JOIN ([QryACAcctNames] LEFT JOIN [QryACCurrentYear] ON [QryACAcctNames].[PAST ACCT] = [QryACPastYear].[PAST ACCT]) ON [QryACCurrentYear].[CURR ACCT] = [QryACAcctNames].[CURR ACCT]

When I attempt to save it, the message "Syntax error in Join Operation" pops up.

Can you tell me what I'm doing wrong?

Thx!!

Answer 5

Thanks very much. I will try that!