Access query deduct two rows

[Michiel Soede]

All,

A very simple question but I can not wrap my head around it als having looked into some examples on different fora. I just need to deduct month 1 from month 2 (from the table fusion_entries) written by means of a sql code in Access (I know how to fix using normal queries) by joining entity cost center and account. I think I need to make a sub query on month 1 and inner join that on the main query on month 2. But some how it does not work and also not sure if I need to use a sub query. There might be quite some transactions so efficiency also comes into play. Hopefully somebody can share his expertise around this.

Thanks in advance,

Michiel

Code so far without sub query and in the attachment the simplified table:

SELECT c.ENTITY, c.COST_CENTRE, c.ACCOUNT, c.ACCOUNTED_TOT AS Jan, d.ACCOUNTED_TOT AS Feb
FROM TBL_FUSION_ENTRIES AS c INNER JOIN (SELECT ENTITY, COST_CENTRE, ACCOUNT, ACCOUNTED_TOT FROM TBL_FUSION_ENTRIES WHERE [PERIOD_NUM] = '2')  AS d ON c.COST_CENTRE = d.COST_CENTRE
WHERE c.PERIOD_NUM = '1';

 

[arnelgp]

Here take a look at the query

 

[Michiel Soede]

Hi thanks for your answer. It seems to work. Two questions to understand. You now only include "T1.ENTITY=C.ENTITY" to join the two tables but if their would be different cost centers or accounts used would you then also add "T1.COST_CENTER=C.COST_CENTER"?

What is now exactly the impact in this query of the "top 1" piece? I know what the top N does but not sure how it impacts the query here.

many thanks

Michiel

 

[jdraw]

Michiel,

Can you describe to readers in simple, plain English what you are trying to achieve?
Forget queries and transactions in database--just plain English. Just as you would tell someone who doesn't know database, doesn't know you and doesn't know your environment --- what are you trying to automate?

Sometimes, writing something down for others to read, will clarify your thoughts.

Good luck.

 

[Michiel Soede]

Yes of course. I have a table of financial transactions in which the transactions are recorded in rows. More specifically I have a separate field for "month".

Currently I am building a form in which I want to show the monthly variance of the general ledger accounts. In order to do so I wanted to know how I should write this in SQL in Access. I know I can do so with creating multiple queries in Access but I was especially interested in the SQL of 'deducting rows'.

Regards,

Michiel

 

[arnelgp]

Yes you can add the cost_center and other field that would make the entity unique.

Top 1 specifically tells access to teturn only one row of data.
You may elect to remove it if you know that the select statement will indeed return 1 row.
But youll get message from access, utmost 1 record must be returned or something.