Microsoft Access Queries (1 Viewer)

Sridharanmb

New member
Local time
Yesterday, 23:32
Joined
Jun 18, 2019
Messages
5
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

I’m here to help
Staff member
Local time
Yesterday, 23:32
Joined
Oct 29, 2018
Messages
21,358
Hi. You should be able to do this using one query for each period and one query for the variance.
 

Sridharanmb

New member
Local time
Yesterday, 23:32
Joined
Jun 18, 2019
Messages
5
Thx...but how we can do that...really i don't have knowledge on the access. just now starting understanding
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:32
Joined
Feb 19, 2013
Messages
16,553
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

New member
Local time
Yesterday, 23:32
Joined
Jun 18, 2019
Messages
5
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
 

Attachments

  • Acess-Query.PNG
    Acess-Query.PNG
    14.2 KB · Views: 58

CJ_London

Super Moderator
Staff member
Local time
Today, 06:32
Joined
Feb 19, 2013
Messages
16,553
now create a second query based on this one and subtract one of the year fields from the other
 

Sridharanmb

New member
Local time
Yesterday, 23:32
Joined
Jun 18, 2019
Messages
5
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

Super Moderator
Staff member
Local time
Today, 06:32
Joined
Feb 19, 2013
Messages
16,553
rather than using column names like 2018-02, use a generic names such as this year and last year
 

Users who are viewing this thread

Top Bottom