Summing Query in access with existing query

Hi There,

I have the below query running access:

SELECT v.sum_month AS [Month], Sum(v.x) AS [Open Dates], Sum(v.y) AS [Closed Dates]
FROM (SELECT format(t.[Date],'yyyymm') as sort_month, format(t.[Date],'mmm yyyy') as sum_month, 1 as x, 0 as y
 FROM Central_Reporting_System_Register t
 union all
 SELECT format(t.[Date Closed:],'yyyymm') as sort_month, format(t.[Date Closed:],'mmm yyyy') as sum_month, 0 as x, 1 as y
 FROM Central_Reporting_System_Register t)  AS v
GROUP BY v.sum_month, v.sort_month
ORDER BY v.sort_month;



1. I want to be able to basically modify this query where it doesn't show the top row.  The top row is basically not showing the correct info.

2.At the bottom of the sum fields ( open date and closed date) i want totals for that column. So basically a total number of open dates and a total number of closed dates. Please bare in mind the sum of both these fields has to exclude the top row.

As you probably already know i didn't write the above script it was actually given to me by an expert on this website. So any help on this matter would be greatly appreciated.

Many Thanks

Jedi


J3D1-KN1G1-1tAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
You cannot have detail and totals in a single query - that is the purpose of reports and forms.
0
J3D1-KN1G1-1tAuthor Commented:
Hi Kelvinsparks : )

Ok i will try to do a form or report and getg back to ya.
0
J3D1-KN1G1-1tAuthor Commented:
Ok I have done a report and I'm facing an issue which relates to my original question of the incorrect top row.

If you have a look at the image you will see that the top row has a blank "Month" field, Blank "Open Dates" field and number 77 in the "Closed Date" field. This is actually incorrect data, is there a way of removing this row? As you can my total sums at the bottom are the same (79) instead of being 79 for Open Dates and 2 for Closed Dates.

 Table image showing the incorrect top top row

Is there a way to remove the top row so that my total sums can add up properly?

Any help would be greatly appreciated.

Many Thanks

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kelvin SparksCommented:
It is returming a NULL I suspect because you have some NULL values in table/View Central_Reporting_System_Register. Is that the case?

Kelvin
0
J3D1-KN1G1-1tAuthor Commented:
Yup there are null values in the Closed date field
0
Kelvin SparksCommented:
Alter the SELECT v.sum_month AS [Month], Sum(v.x) AS [Open Dates], Sum(v.y) AS [Closed Dates]
FROM (SELECT format(t.[Date],'yyyymm') as sort_month, format(t.[Date],'mmm yyyy') as sum_month, 1 as x, 0 as y
 FROM Central_Reporting_System_Register t
 to have where t.[Date] IS NOT NULL after it.
0
Kelvin SparksCommented:
or is it t.[Closed Date] IS NOT NULL after the second FROM
0
J3D1-KN1G1-1tAuthor Commented:

Do add "IS NOT NULL" twice?



SELECT v.sum_month AS [Month], Sum(v.x) AS [Open Dates], Sum(v.y) AS [Closed Dates]
FROM (SELECT format(t.[Date],'yyyymm') as sort_month, format(t.[Date],'mmm yyyy') as sum_month, 1 as x, 0 as y
 FROM Central_Reporting_System_Register t
 union all
 SELECT format(t.[Date Closed:] IS NOT NULL,'yyyymm') as sort_month, format(t.[Date Closed:] IS NOT NULL,'mmm yyyy') as sum_month, 0 as x, 1 as y
 FROM Central_Reporting_System_Register t)  AS v
GROUP BY v.sum_month, v.sort_month
ORDER BY v.sort_month;

From above this is what i get:

 Cap1
SELECT v.sum_month AS [Month], Sum(v.x) AS [Open Dates], Sum(v.y) AS [Closed Dates]
FROM (SELECT format(t.[Date] IS NOT NULL,'yyyymm') as sort_month, format(t.[Date] IS NOT NULL,'mmm yyyy') as sum_month, 1 as x, 0 as y
 FROM Central_Reporting_System_Register t
 union all
 SELECT format(t.[Date Closed:],'yyyymm') as sort_month, format(t.[Date Closed:],'mmm yyyy') as sum_month, 0 as x, 1 as y
 FROM Central_Reporting_System_Register t)  AS v
GROUP BY v.sum_month, v.sort_month
ORDER BY v.sort_month;

From above this is what i get:


 Cap2
0
Kelvin SparksCommented:
Not quite as I intended
SELECT v.sum_month AS [Month], Sum(v.x) AS [Open Dates], Sum(v.y) AS [Closed Dates]
FROM (SELECT format(t.[Date],'yyyymm') as sort_month, format(t.[Date],'mmm yyyy') as sum_month, 1 as x, 0 as y
 FROM Central_Reporting_System_Register t
WHERE t.[Date] IS NOT NULL
 union all
 SELECT format(t.[Date Closed:] IS NOT NULL,'yyyymm') as sort_month, format(t.[Date Closed:] IS NOT NULL,'mmm yyyy') as sum_month, 0 as x, 1 as y
 FROM Central_Reporting_System_Register t)  AS v
WHERE t.[Date Closed] IS NOT NULL
GROUP BY v.sum_month, v.sort_month
ORDER BY v.sort_month;
0
J3D1-KN1G1-1tAuthor Commented:
error1
Hi Kelvinsparks I get the above error when i type in the code given above. I thought the error might be related to line 8 at "WHERE t.[Date Closed] IS NOT NULL". Date Closed might need to be written with semi-colon? like "Date Closed:"?

I tried that and i got the error below:

 error2
Please let me know what im doing wrong and help would be greatly appreciated.

P.S: sorry for the late reply been feeling under the weather lately.
0
Kelvin SparksCommented:
Apologies, missed yor reply.

Can you try
SELECT v.sum_month AS [Month], Sum(v.x) AS [Open Dates], Sum(v.y) AS [Closed Dates]
FROM (SELECT format(t.[Date],'yyyymm') as sort_month, format(t.[Date],'mmm yyyy') as sum_month, 1 as x, 0 as y
 FROM Central_Reporting_System_Register t
WHERE t.[Date] IS NOT NULL
 union all
 SELECT format(t.[Date Closed:] IS NOT NULL,'yyyymm') as sort_month, format(t.[Date Closed:] 'mmm yyyy') as sum_month, 0 as x, 1 as y
 FROM Central_Reporting_System_Register t)  AS v
WHERE t.[Date Closed] IS NOT NULL
GROUP BY v.sum_month, v.sort_month
ORDER BY v.sort_month;

and see how you go
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
J3D1-KN1G1-1tAuthor Commented:
Thanks Kelvin! just got back from break I will give this ago soon
0
J3D1-KN1G1-1tAuthor Commented:
Hi Kelvin,

I tried your code and i got the following error:
 Error1

Then i changed the code to fix this error(This could be wrong)

SELECT v.sum_month AS [Month], Sum(v.x) AS [Open Dates], Sum(v.y) AS [Closed Dates]
FROM (SELECT format(t.[Date],'yyyymm') as sort_month, format(t.[Date],'mmm yyyy') as sum_month, 1 as x, 0 as y
 FROM Central_Reporting_System_Register t
WHERE t.[Date] IS NOT NULL
 union all
 SELECT format(t.[Date Closed:] IS NOT NULL,'yyyymm') as sort_month, format(t.[Date Closed:], 'mmm yyyy') as sum_month, 0 as x, 1 as y
 FROM Central_Reporting_System_Register t)  AS v
WHERE t.[Date Closed:] IS NOT NULL
GROUP BY v.sum_month, v.sort_month
ORDER BY v.sort_month;


So the changes made are in bold which were adding a comma after "format(t.[Date Closed:]" and adding a semi colon at the end of "WHERE t.[Date Closed". These could be the wrong changes as I am not good at scripting. After these changes were made i got the following error:

 Error 2 after changes

Thank you for your help :)

0
Kelvin SparksCommented:
I'm guessing from the last error that this is Access 2007 or Access 2010 - where multivalue fields were introduced. I've never used them. From what I understand they're some form of GUI way of managing what developers referred to as link tables (not to be confused with linked tables). I suspect you're close, but without being able to see the schema directly or the actual tables I don't think I can help too much more.
0
J3D1-KN1G1-1tAuthor Commented:
Oh no :( Thanks heaps but for your help and patience. Very much appreciated, i will do my best to figure it out from here on.

Cheers!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.