Modify the query to show monthwise details (1 Viewer)

Ravi Kumar

Registered User.
Local time
Today, 10:59
Joined
Aug 22, 2019
Messages
162
Dear all ,

I have a table which has the data of daily rework qty of 5 different categories(types) , I need two modifications to complete this so please help me:
1. I have a query which summarizes the data day wise , but I need an extra query which summarizes the same in month wise.(month by categories(type))
day wise query SQL -
SELECT DISTINCTROW Format$([Master Table].[Date of Entry],'Long Date') AS [Date of Entry By Day], [Master Table].Type, Sum([Master Table].[Inspected Qty]) AS [Sum Of Inspected Qty], Sum([Master Table].[Rework Qty]) AS [Sum Of Rework Qty]
FROM [Master Table]
GROUP BY Format$([Master Table].[Date of Entry],'Long Date'), [Master Table].Type;

2. I have made a chart which is currently only showing sum of inspection qty daily , but how to get a line graph in the secondary axis showing rework percentage of that day.
I have attached the database for your reference.
 

Attachments

  • Daily Rework statistics.zip
    83 KB · Views: 248

Galaxiom

Super Moderator
Staff member
Local time
Today, 16:29
Joined
Jan 20, 2009
Messages
12,849
Derive a field based on the Month and Group By it.

There are are many ways to make the field. You can use a date in the month and year. Format as YYMM. All that matters is that you get the same value for the records of a particular month.
 

isladogs

MVP / VIP
Local time
Today, 05:29
Joined
Jan 14, 2017
Messages
18,186
Not at my computer at the moment.
Use your aggregate (totals) query as the record source for the chart. Format the monthly total field as Percent
If that doesn't help, suggest you post the updated SQL or modified database for someone to look at
 

Ravi Kumar

Registered User.
Local time
Today, 10:59
Joined
Aug 22, 2019
Messages
162
Not at my computer at the moment.
Use your aggregate (totals) query as the record source for the chart. Format the monthly total field as Percent
If that doesn't help, suggest you post the updated SQL or modified database for someone to look at
SQL:
SELECT DISTINCTROW [Master Table].[Date of Entry], Sum([Master Table].[Inspected Qty]) AS [Sum Of Inspected Qty], Sum([Master Table].[Rework Qty]) AS [Sum Of Rework Qty], Count(*) AS [Count Of Master Table]
FROM [Master Table]
GROUP BY [Master Table].[Date of Entry];
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:29
Joined
Feb 28, 2001
Messages
27,001
Ravi Kumar -

You posted a request for help in my profile but I do not claim to be up on creating charts using VBA. I always create them manually. I don't know that I have any knowledge of that part of your question.
 

isladogs

MVP / VIP
Local time
Today, 05:29
Joined
Jan 14, 2017
Messages
18,186
Ravi
I've not had time to look at this yet. Will try to do so this evening (UK time).
Which version of Access are you using. Traditional charts or modern charts?
 

isladogs

MVP / VIP
Local time
Today, 05:29
Joined
Jan 14, 2017
Messages
18,186
Hi Ravi
1. The SQL you posted in post #7 doesn't give monthly totals. See if this does what you want:
SQL:
SELECT Format([Date of Entry],"mmm yyyy") AS MonthYear, Count([Master Table].ID) AS Records, Sum([Master Table].[Inspected Qty]) AS TotalInspected, Sum([Master Table].[Rework Qty]) AS TotalRework
FROM [Master Table]
GROUP BY Format([Date of Entry],"mmm yyyy");

2. It appears you've since asked about your chart in a separate thread: https://www.access-programmers.co.u...-data-labels-value-in-chart-in-report.309527/
 

Ravi Kumar

Registered User.
Local time
Today, 10:59
Joined
Aug 22, 2019
Messages
162
Hi Ravi
1. The SQL you posted in post #7 doesn't give monthly totals. See if this does what you want:
SQL:
SELECT Format([Date of Entry],"mmm yyyy") AS MonthYear, Count([Master Table].ID) AS Records, Sum([Master Table].[Inspected Qty]) AS TotalInspected, Sum([Master Table].[Rework Qty]) AS TotalRework
FROM [Master Table]
GROUP BY Format([Date of Entry],"mmm yyyy");

2. It appears you've since asked about your chart in a separate thread: https://www.access-programmers.co.u...-data-labels-value-in-chart-in-report.309527/
Hi , The issue I asked there is completely different , it was about rounding of the data label's value (actual : 13.07456234% , needed : 13.1%)
The issue I have asked here is how to make combo chart - 1 clustered bar for inspected qty & 1 line graph (line graph should show the percentage rework daily)
And Thank you very much , the SQL you provided helped me .
 

isladogs

MVP / VIP
Local time
Today, 05:29
Joined
Jan 14, 2017
Messages
18,186
Sorry about that. I've just modified your summary chart in the other thread to show 1 d.p. for your data labels.

Although you didn't answer my question, it seems you are using modern charts.
Doing so means that anyone opening your report in an older version of Access just sees a blank space where the chart should be.

Anyway, as stated you will need a combo chart which I've never used in the modern charts.
So it would be a learning experience for me as well

Unfortunately you didn't supply the chart mentioned in post #1 in your download for the other thread.
If you add it to the version I uploaded, I'll have a look later if I have time.
Hopefully someone else will do so first
 

Ravi Kumar

Registered User.
Local time
Today, 10:59
Joined
Aug 22, 2019
Messages
162
Ok
Sorry about that. I've just modified your summary chart in the other thread to show 1 d.p. for your data labels.

Although you didn't answer my question, it seems you are using modern charts.
Doing so means that anyone opening your report in an older version of Access just sees a blank space where the chart should be.

Anyway, as stated you will need a combo chart which I've never used in the modern charts.
So it would be a learning experience for me as well

Unfortunately you didn't supply the chart mentioned in post #1 in your download for the other thread.
If you add it to the version I uploaded, I'll have a look later if I have time.
Hopefully someone else will do so first
Okay ..I will upload the chart ..currently I am now caught up in other works hence I will upload in 1 hour
 

Users who are viewing this thread

Top Bottom