Microsoft Access Queries

[Sridharanmb]

I Have Table
1. YYYY_MM (2018 and 2019)
2. Merchant ID
3. Merchant Name
4. Sales Value
Revenue

based on the above i have created query by providing the enter parameter for Period, i get the report but i would like to have the query in below format

ID Name 2018-01 201901 variance
Sales Value/ER Sales Value/Revenue Difference between periods for Sales/Revenue

 

[theDBguy]

Hi. You should be able to do this using one query for each period and one query for the variance.

 

[Sridharanmb]

Thx...but how we can do that...really i don't have knowledge on the access. just now starting understanding

 

[CJ_London]

there are a number of ways

you could use a crosstab query - something like

TRANSFORM Sum(Table1.SalesValue) AS SumOfSalesValue
SELECT Table1.merchantID, Table1.merchantName
FROM Table1
GROUP BY Table1.merchantID, Table1.merchantName
PIVOT Table1.period

then a second query based on that to subtract one value from the other


Note that you should be storing merchantID and name in a separate table and only have ID in this table otherwise you will get problems in the name is misspelt

 

[Sridharanmb]

Hi
I have created a query and output is like this

Merchant ID Merc. Name 2019-01 2018-01
100700000005 XXXXX 88.16 1000

I have attached query design view, i would like to have the result between two periods

 

[CJ_London]

now create a second query based on this one and subtract one of the year fields from the other

 

[Sridharanmb]

THx...but if i change period then again i need to great a another QUery?

example if i change the period in the first query to 02(2018-02 vs 2019-02), then how can i create dynamic second query?

 

[CJ_London]

rather than using column names like 2018-02, use a generic names such as this year and last year