SQL Union group by problem

[darbid]

I am getting help from someone who works with oracle but is having trouble getting things done with access.

strsqlc = "select mo,max(pcount),max(ccount)" &_
" from " & _
" (select month(Date_of_Project) as mo, year(Date_of_Project) as ye, count(Date_of_Project) as pcount, '0' as ccount" & _
" From [tbl_LuT Vorgang]" & _
" WHERE " & strwho & _
" [tbl_LuT Vorgang].Date_of_Project >= #" & Format(strcpstartdate, "mm\/dd\/yyyy") & "#" & _
" AND [tbl_LuT Vorgang].Date_of_Project <= #" & Format(strcpenddate, "mm\/dd\/yyyy") & "#" & _
" GROUP by month(Date_of_Project), year(Date_of_Project))" & _
" UNION (select month(Date_of_Contact) as mo, year(Date_of_Contact) as ye, '0' as pcount, count(Date_of_Contact) as ccount" & _
" From [tbl_LuT Vorgang]" & _
" WHERE " & strwho & _
" [tbl_LuT Vorgang].Date_of_Contact >= #" & Format(strcpstartdate, "mm\/dd\/yyyy") & "#" & _
" AND [tbl_LuT Vorgang].Date_of_Contact <= #" & Format(strcpenddate, "mm\/dd\/yyyy") & "#" & _
" GROUP by month(Date_of_Contact), year(Date_of_Contact))" & _
" GROUP by mo, ye" & _
" ORDER by mo,ye;"

could anyone offer some help please?

 

[Premy]

Have u checked out Access Help on the subject?

 

[darbid]

we have spent about 3 or 4 hours on this. We have checked the help and more.

 

[Kiwiman]

Howzit

You have two Group by statements in your Union section, get rid of the one that starts with Group by MO

Sorry seeing things...

If I'm seeing things right this time you are grouping by ye in the last group by stmt but you do not have ye in the select portion

strsqlc = "select mo,max(pcount),max(ccount)" &_
" from " & _

 

[darbid]

If I'm seeing things right this time you are grouping by ye in the last group by stmt but you do not have ye in the select portion

You are seeing 100% correct and that is intented. We will group by year but do not need it so have not got it in the select.

 

[Kiwiman]

Howzit

Definitely been a long day...

Another possiblity, grasping at straws one would say...

Breaking down your first Stmt in the union stmt

• the part in blue is not part of the group by clause, and it is not being counted, sum...
• What does the strwho do? Should there be an AND between that and the next statement? You may very well have this covered in the strwho string itself

maybe the strwho needs to be like - encased in apostrophes

]" WHERE '" & strwho & "'" & _

select month(Date_of_Project) as mo, year(Date_of_Project) as ye, count(Date_of_Project) as pcount, [COLOR="Blue"][B]'0' as ccount" & _[/B][/COLOR]
" From [tbl_LuT Vorgang]" & _
[COLOR="Red"][B]" WHERE " & strwho & _[/B][/COLOR]
" [tbl_LuT Vorgang].Date_of_Project >= #" & Format(strcpstartdate, "mm\/dd\/yyyy") & "#" & _
" AND [tbl_LuT Vorgang].Date_of_Project <= #" & Format(strcpenddate, "mm\/dd\/yyyy") & "#" & _
" GROUP by 
month(Date_of_Project), 
year(Date_of_Project))"

 

[darbid]

The strwho does have an AND in it.

Let me give a little explanation of what this should do.

we have the following two columns with dates in them - Contact_date , Project_date.

The user wants to know how many contacts and projects have happened in a month.

The above SQL should make a table grouped by month for contact the project column will have zeros.

Month --- contact_date --- project_date
10 ------------ 2 -------------- 0
11 ------------ 3 -------------- 0
6 ------------ 5 -------------- 0

Then we do the same for project with the contacts as zeros

Month --- contact_date --- project_date
10------------ 0 -------------- 2
11------------ 0 -------------- 3
5 ------------ 0 -------------- 5

Then we take the union of these two table and the max if there are two the same to get the result

Month --- contact_date --- project_date
10 ------------ 2 ------------ 2
11 ------------ 3 ------------ 3
6 ------------- 5 ------------ 0
5 ------------- 0 ------------ 5

One of the reasons that it took so long was that we were getting a simple syntax error but access was not telling us anything else. We were using a OUTER JOIN which we then worked out does not work in jet.

The year is used in case people choose a time from of over one year. but is not needed in the results.

 

[Kiwiman]

Howzit

Have you tried the syntax with

]" WHERE '" & strwho & "'" & _

to see if it makes a difference?

I have never used (let alone seen it) the date format "mm\/dd\/yyyy". I use the fromat "mm-dd-yyyy". I'm not saying your format doesn't work - its just something I am unfamiliar with.

When I have union query issues, I tend to troubleshoot them by (via the queries objects) page:

1. Run all the queries, comprising the union individually, to ensure that they actually run as a stand alone query. If they do we can elimiinate the individual queries as the problem
2. You will need to replace the strwho with the actually string it represents when running it in the queries object screen
3. I would then create the union to see if it runs correctly as it is - again if successful we can eliminate that as the problem
4. I would then add the final part that counts the data and uses the union query as the source. Again I would be hoping for it run successfully
5. If all successful, I would past the sql of the final product back into the code then replace the strwho back to its rightful place. Check to see if the code makes it all the way through
6. to step through the code put stop in the code before your strsqlc part, then step through using the F8 key, If it falls over, you will know which line is causing the problem

Are you able to post the whole code for this, so I can see how it is forms and how the strsqlc is being used, after successfully running through the code.

 

[darbid]

Kiwiman thanks for taking the time to look at it. I have taken out all those possible problems and am using the access query maker thingy.

Pulled it apart and then rebuilt it.

This works but gives me all results in one table, I now need to group this so that I only get one mo and ye

SELECT *
FROM
(SELECT month(Date_of_Project) AS mo, year(Date_of_Project) AS ye, count(Date_of_Project) AS pcount, '0' AS ccount
FROM [tbl_LuT Vorgang]
WHERE [tbl_LuT Vorgang].Date_of_Project>=#1/1/2005# And [tbl_LuT Vorgang].Date_of_Project<=#4/1/2006#
GROUP BY month(Date_of_Project), year(Date_of_Project))
 UNION (SELECT month(Date_of_Contact) AS mo, year(Date_of_Contact) AS ye, '0' AS pcount, count(Date_of_Contact) AS ccount
FROM [tbl_LuT Vorgang]
WHERE [tbl_LuT Vorgang].Date_of_Contact>=#1/1/2005# And [tbl_LuT Vorgang].Date_of_Contact<=#4/1/2006#
GROUP BY month(Date_of_Contact), year(Date_of_Contact))
ORDER BY ye, mo;

Now I get a syntax error as soon as I put in the first "Select mo, ye"
and the last "Group by mo, ye"

like this.

SELECT mo, ye
FROM
(SELECT month(Date_of_Project) AS mo, year(Date_of_Project) AS ye, count(Date_of_Project) AS pcount, '0' AS ccount
FROM [tbl_LuT Vorgang]
WHERE [tbl_LuT Vorgang].Date_of_Project>=#1/1/2005# And [tbl_LuT Vorgang].Date_of_Project<=#4/1/2006#
GROUP BY month(Date_of_Project), year(Date_of_Project))
 UNION (SELECT month(Date_of_Contact) AS mo, year(Date_of_Contact) AS ye, '0' AS pcount, count(Date_of_Contact) AS ccount
FROM [tbl_LuT Vorgang]
WHERE [tbl_LuT Vorgang].Date_of_Contact>=#1/1/2005# And [tbl_LuT Vorgang].Date_of_Contact<=#4/1/2006#
GROUP BY month(Date_of_Contact), year(Date_of_Contact))
GROUP BY mo, ye
ORDER BY ye, mo;

 

[Kiwiman]

Howzit

Great - progress.

can you try the below. I have out taken out two brackets.

My example works - when I put the brackets in that I have taken out of yours, I get a Union Syntax error

The two i have taken out is

• first part of the union query year(Date_of_Project))
  
• second part of union (SELECT

SELECT mo, ye
FROM
(SELECT month(Date_of_Project) AS mo, year(Date_of_Project) AS ye, count(Date_of_Project) AS pcount, '0' AS ccount
FROM [tbl_LuT Vorgang]
WHERE [tbl_LuT Vorgang].Date_of_Project>=#1/1/2005# And [tbl_LuT Vorgang].Date_of_Project<=#4/1/2006#
GROUP BY month(Date_of_Project), year(Date_of_Project)
 UNION SELECT month(Date_of_Contact) AS mo, year(Date_of_Contact) AS ye, '0' AS pcount, count(Date_of_Contact) AS ccount
FROM [tbl_LuT Vorgang]
WHERE [tbl_LuT Vorgang].Date_of_Contact>=#1/1/2005# And [tbl_LuT Vorgang].Date_of_Contact<=#4/1/2006#
GROUP BY month(Date_of_Contact), year(Date_of_Contact))
GROUP BY mo, ye
ORDER BY ye, mo;

My example below works

select id,sname from (SELECT 
tblSalesExec.SalesExecID as id, 
tblSalesExec.SalesExec as Sname
FROM 
tblSalesExec
GROUP BY 
tblSalesExec.SalesExecID,
 tblSalesExec.SalesExec
UNION SELECT
 tblSalesExec.SalesExecID as id, 
tblSalesExec.SalesExec as sname
FROM 
tblSalesExec
GROUP BY 
tblSalesExec.SalesExecID, 
tblSalesExec.SalesExec)
group by id,sname

But my example below doesn't - with the extra brackets

select id,sname from (SELECT 
tblSalesExec.SalesExecID as id, 
tblSalesExec.SalesExec as Sname
FROM 
tblSalesExec
GROUP BY 
tblSalesExec.SalesExecID,
 tblSalesExec.SalesExec[B][COLOR="Red"])[/COLOR][/B]
UNION [B][COLOR="red"]([/COLOR][/B]SELECT
 tblSalesExec.SalesExecID as id, 
tblSalesExec.SalesExec as sname
FROM 
tblSalesExec
GROUP BY 
tblSalesExec.SalesExecID, 
tblSalesExec.SalesExec))
group by id,sname

 

[darbid]

removing those two brackets does certainly mean that it works with out an error message but the resulting table is just a grouped mo and ye. Access also automatically puts [ into the query.

SELECT mo, ye
FROM [B][SIZE=4][COLOR=Red][[/COLOR][/SIZE][/B]SELECT month(Date_of_Project) AS mo, year(Date_of_Project) AS ye, count(Date_of_Project) AS pcount, '0' AS ccount
FROM [tbl_LuT Vorgang]
WHERE [tbl_LuT Vorgang].Date_of_Project>=#1/1/2005# And [tbl_LuT Vorgang].Date_of_Project<=#4/1/2006#
GROUP BY month(Date_of_Project), year(Date_of_Project)
 UNION SELECT month(Date_of_Contact) AS mo, year(Date_of_Contact) AS ye, '0' AS pcount, count(Date_of_Contact) AS ccount
FROM [tbl_LuT Vorgang]
WHERE [tbl_LuT Vorgang].Date_of_Contact>=#1/1/2005# And [tbl_LuT Vorgang].Date_of_Contact<=#4/1/2006#
GROUP BY month(Date_of_Contact), year(Date_of_Contact)[SIZE=4][COLOR=Red][B]]. AS [%$##@_Alias][/B][/COLOR][/SIZE]
GROUP BY mo, ye
ORDER BY ye, mo;

with bracketing we naturally wanted to do this, but we get a join error?

SELECT mo, ye
FROM
[SIZE=4][B][COLOR=Red]([/COLOR][/B][/SIZE](SELECT month(Date_of_Project) AS mo, year(Date_of_Project) AS ye, count(Date_of_Project) AS pcount, '0' AS ccount
FROM [tbl_LuT Vorgang]
WHERE [tbl_LuT Vorgang].Date_of_Project>=#1/1/2005# And [tbl_LuT Vorgang].Date_of_Project<=#4/1/2006#
GROUP BY month(Date_of_Project), year(Date_of_Project))
 UNION (SELECT month(Date_of_Contact) AS mo, year(Date_of_Contact) AS ye, '0' AS pcount, count(Date_of_Contact) AS ccount
FROM [tbl_LuT Vorgang]
WHERE [tbl_LuT Vorgang].Date_of_Contact>=#1/1/2005# And [tbl_LuT Vorgang].Date_of_Contact<=#4/1/2006#
GROUP BY month(Date_of_Contact), year(Date_of_Contact))[SIZE=4][B][COLOR=Red])[/COLOR][/B][/SIZE]
GROUP BY mo, ye
ORDER BY ye, mo;

 

[Kiwiman]

Howzit

Sorry running out of ideas...

Its only grouping by as we have no max(pcount),max(ccount)"

in the selection.

It looks like access requires an alias for this one and if one isnt supplied it will put that in. I put in T1 in an another example.

This gives me the number of commission lines and the number of payments made

SELECT t1.SalesExecID, t1.Yr1, t1.Mth, Sum(t1.Comms) AS Comm, Sum(t1.Pmts) AS Pmt
FROM [SELECT tblStatements.SalesExecID, Year([sovdate]) AS Yr1, Month([sovdate]) AS Mth, Count(tblStatements.StmtID) AS Comms, 0 AS Pmts
FROM tblStatements
GROUP BY tblStatements.SalesExecID, Year([sovdate]), Month([sovdate]), 0
union all
SELECT tblStatements.SalesExecID, Year([sovdate]) AS Yr1, Month([sovdate]) AS Mth, 0 AS Comms, Count(tblStatements.StmtID) AS Pmts
FROM tblStatements INNER JOIN tblPayments ON tblStatements.StmtID = tblPayments.StmtID
GROUP BY tblStatements.SalesExecID, Year([sovdate]), Month([sovdate])]. AS t1
GROUP BY t1.SalesExecID, t1.Yr1, t1.Mth;

have you tried Dcount?

SELECT tblStatements.SalesExecID, Year([sovdate]) AS Yr1, DCount("[StmtID]","tblstatements","[Salesexecid] = " & [tblStatements]![SalesExecID] & "And [tblStatements]![SOVYear] = " & Year([tblStatements]![SOVDate])) AS num
FROM tblStatements
GROUP BY tblStatements.SalesExecID, Year([sovdate]), DCount("[StmtID]","tblstatements","[Salesexecid] = " & [tblStatements]![SalesExecID] & "And [tblStatements]![SOVYear] = " & Year([tblStatements]![SOVDate]));

 

[darbid]

you got it....legend

I have however lost my expert sql person to finish off what I am sure is a small problem

to make it work i need this

SELECT t1.mo, t1.ye, max(t1.pcount), max(t1.ccount)
FROM [SIZE=4][COLOR=Red][B]([/B][/COLOR][/SIZE]SELECT month(Date_of_Project) AS mo, year(Date_of_Project) AS ye, count(Date_of_Project) AS pcount, 0 AS ccount
FROM [tbl_LuT Vorgang]
WHERE [tbl_LuT Vorgang].Date_of_Project>=#1/1/2005# And [tbl_LuT Vorgang].Date_of_Project<=#4/1/2006#
GROUP BY month(Date_of_Project), year(Date_of_Project)
UNION ALL SELECT month(Date_of_Contact) AS mo, year(Date_of_Contact) AS ye, 0 AS pcount, count(Date_of_Contact) AS ccount
FROM [tbl_LuT Vorgang]
WHERE [tbl_LuT Vorgang].Date_of_Contact>=#1/1/2005# And [tbl_LuT Vorgang].Date_of_Contact<=#4/1/2006#
GROUP BY month(Date_of_Contact), year(Date_of_Contact)[SIZE=4][COLOR=Red][B])[/B][/COLOR][/SIZE] AS t1
GROUP BY t1.mo, t1.ye;

then access automatically changes the above to this which then has a invalid bracketing of name SELECT......
this appears to be how you have it as well.

SELECT t1.mo, t1.ye, max(t1.pcount), max(t1.ccount)
FROM [SIZE=4][COLOR=Red][B][[/B][/COLOR][/SIZE]SELECT month(Date_of_Project) AS mo, year(Date_of_Project) AS ye, count(Date_of_Project) AS pcount, 0 AS ccount
FROM [tbl_LuT Vorgang]
WHERE [tbl_LuT Vorgang].Date_of_Project>=#1/1/2005# And [tbl_LuT Vorgang].Date_of_Project<=#4/1/2006#
GROUP BY month(Date_of_Project), year(Date_of_Project)
UNION ALL SELECT month(Date_of_Contact) AS mo, year(Date_of_Contact) AS ye, 0 AS pcount, count(Date_of_Contact) AS ccount
FROM [tbl_LuT Vorgang]
WHERE [tbl_LuT Vorgang].Date_of_Contact>=#1/1/2005# And [tbl_LuT Vorgang].Date_of_Contact<=#4/1/2006#
GROUP BY month(Date_of_Contact), year(Date_of_Contact)[B][SIZE=4][COLOR=Red]].[/COLOR][/SIZE][/B] AS t1
GROUP BY t1.mo, t1.ye;

 

[Kiwiman]

Howzit

I can't say I noticed the [ and the ]. that access put in - if you hadn't pointed it out, I may not have noticed it at all.

When I saved my query it did the exact same thng. But i am able to run it just fine with the [ and the ]. in. I'm really not sure where to go from here.