asked on

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

Kelvin Sparks

You cannot have detail and totals in a single query - that is the purpose of reports and forms.

J3D1-KN1G1-1t

ASKER

Hi Kelvinsparks : )

Ok i will try to do a form or report and getg back to ya.

J3D1-KN1G1-1t

ASKER

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.

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

Kelvin Sparks

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

J3D1-KN1G1-1t

ASKER

Yup there are null values in the Closed date field

Kelvin Sparks

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.

Kelvin Sparks

or is it t.[Closed Date] IS NOT NULL after the second FROM

J3D1-KN1G1-1t

ASKER

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:

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:

Kelvin Sparks

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;

J3D1-KN1G1-1t

ASKER

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:

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.

ASKER CERTIFIED SOLUTION

Kelvin Sparks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

J3D1-KN1G1-1t

ASKER

Thanks Kelvin! just got back from break I will give this ago soon

J3D1-KN1G1-1t

ASKER

Hi Kelvin,

I tried your code and i got the following error:

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:

Thank you for your help :)

Kelvin Sparks

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.

J3D1-KN1G1-1t

ASKER

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!