Union query error (1 Viewer)

D

dineshkm

New member
Local time
Yesterday, 16:49
Joined
Jun 4, 2019
Messages
3
Sir I am seeking a help in Access.

I have got one Union query which gives an error 'This expression is typed incorrectly or too complex to be evaluated' is getting. But if the statement executes separately with out UNION clause it is generating correctly. Pls advise how to sort out the issue.
My query statement is as follows.
SELECT format([Query4.DATE],"dd/mm/yyyy") as dates, Query4.shift, Query4.machineno as mc,Query4.sd_sider as sider, Query4.count,round([Query4.prdnkgs],2) as prdnkgs,round(Query4.production,2) as hank,Query4.WKDSPINDLES as wspdl,round([query4.stdgps],2)as stdgps,round([query4.gpsach],2)as gpsach, round([query4.stdkg],2)as stdkg,round((stdgps*wspdl)/1000,2) as stdkgs,round(gpsach/stdgps*100,2) as pper,round(wspdl/query4.spindle * 100,2) as uper,round(pper/uper * 100,2) as eper,query4.waste,round(query4.waste/prdnkgs *100,2) as uwas,round(Query4.stpmnts,2) as stpmnts,query4.speed
FROM Query4
GROUP BY Query4.DATE, Query4.shift, Query4.machineno,Query4.sd_sider, Query4.count, Query4.production, Query4.CNO, Query4.SPINDLE, Query4.STPMNTS, Query4.WKDSPINDLES, Query4.prdnkgs,query4.stdgps,Query4.gpsach,Query4.stdkg,query4.speed,query4.waste
UNION SELECT q4.DATE , q4.shift, q4.machineno as mc,q4.sd_sider, q4.count, round([q4.prdnkgs],2) as prdnkgs,round(Q4.production,2) as hank,q4.WKDSPINDLES as wspdl,round([q4.stdgps],2) as stdgps,round([q4.gpsach],2) as gpsach,round([q4.stdkg],2) as stdkg,round((stdgps*wspdl)/1000,2) as stdkgs,round(gpsach/stdgps*100,2) as pper,round(wspdl/q4.spindle * 100,2) as uper,round(pper/uper * 100,2) as eper,q4.waste,round(q4.waste/prdnkgs *100,2) as uwas,round(q4.stpmnts,2) as stpmnts,q4.speed
FROM q4
GROUP BY q4.DATE, q4.shift, q4.machineno, q4.sd_sider,q4.count, q4.production, q4.CNO, q4.SPINDLE, q4.STPMNTS, q4.WKDSPINDLES, q4.prdnkgs, q4.waste,q4.stdgps,q4.gpsach,q4.stdkg,q4.speed;
SELECT format([Query4.DATE],"dd/mm/yyyy") as dates, Query4.shift, Query4.machineno as mc,Query4.sd_sider as sider, Query4.count,round([Query4.prdnkgs],2) as prdnkgs,round(Query4.production,2) as hank,Query4.WKDSPINDLES as wspdl,round([query4.stdgps],2)as stdgps,round([query4.gpsach],2)as gpsach, round([query4.stdkg],2)as stdkg,round((stdgps*wspdl)/1000,2) as stdkgs,round(gpsach/stdgps*100,2) as pper,round(wspdl/query4.spindle * 100,2) as uper,round(pper/uper * 100,2) as eper,query4.waste,round(query4.waste/prdnkgs *100,2) as uwas,round(Query4.stpmnts,2) as stpmnts,query4.speed
FROM Query4
GROUP BY Query4.DATE, Query4.shift, Query4.machineno,Query4.sd_sider, Query4.count, Query4.production, Query4.CNO, Query4.SPINDLE, Query4.STPMNTS, Query4.WKDSPINDLES, Query4.prdnkgs,query4.stdgps,Query4.gpsach,Query4.stdkg,query4.speed,query4.waste
UNION SELECT q4.DATE , q4.shift, q4.machineno as mc,q4.sd_sider, q4.count, round([q4.prdnkgs],2) as prdnkgs,round(Q4.production,2) as hank,q4.WKDSPINDLES as wspdl,round([q4.stdgps],2) as stdgps,round([q4.gpsach],2) as gpsach,round([q4.stdkg],2) as stdkg,round((stdgps*wspdl)/1000,2) as stdkgs,round(gpsach/stdgps*100,2) as pper,round(wspdl/q4.spindle * 100,2) as uper,round(pper/uper * 100,2) as eper,q4.waste,round(q4.waste/prdnkgs *100,2) as uwas,round(q4.stpmnts,2) as stpmnts,q4.speed
FROM q4
GROUP BY q4.DATE, q4.shift, q4.machineno, q4.sd_sider,q4.count, q4.production, q4.CNO, q4.SPINDLE, q4.STPMNTS, q4.WKDSPINDLES, q4.prdnkgs, q4.waste,q4.stdgps,q4.gpsach,q4.stdkg,q4.speed;
 
Minty

Minty

AWF VIP
Local time
Today, 00:49
Joined
Jul 26, 2013
Messages
10,368
Why are you grouping the results of the individual queries ?
There appears to be no aggregate functions being performed to necessitate it ?

Also date, count and shift are reserved words so either put square brackets around them or change the name of the field as that might be tripping you up.

Finally you appear to be unioning the same exact 2 sets of results, is that your intention?
 
Minty

Minty

AWF VIP
Local time
Today, 00:49
Joined
Jul 26, 2013
Messages
10,368
Here it is in a easier to read format
Code: 
SELECT format([Query4.DATE], "dd/mm/yyyy") AS dates,
       Query4.shift,
       Query4.machineno AS mc,
       Query4.sd_sider AS sider,
       Query4.count,
       ROUND([Query4.prdnkgs], 2) AS prdnkgs,
       ROUND(Query4.production, 2) AS hank,
       Query4.WKDSPINDLES AS wspdl,
       ROUND([query4.stdgps], 2) AS stdgps,
       ROUND([query4.gpsach], 2) AS gpsach,
       ROUND([query4.stdkg], 2) AS stdkg,
       ROUND((stdgps * wspdl) / 1000, 2) AS stdkgs,
       ROUND(gpsach / stdgps * 100, 2) AS pper,
       ROUND(wspdl / Query4.spindle * 100, 2) AS uper,
       ROUND(pper / uper * 100, 2) AS eper,
       Query4.waste,
       ROUND(Query4.waste / prdnkgs * 100, 2) AS uwas,
       ROUND(Query4.stpmnts, 2) AS stpmnts,
       Query4.speed
FROM Query4
GROUP BY Query4.DATE,
         Query4.shift,
         Query4.machineno,
         Query4.sd_sider,
         Query4.count,
         Query4.production,
         Query4.CNO,
         Query4.SPINDLE,
         Query4.STPMNTS,
         Query4.WKDSPINDLES,
         Query4.prdnkgs,
         Query4.stdgps,
         Query4.gpsach,
         Query4.stdkg,
         Query4.speed,
         Query4.waste
UNION
SELECT q4.DATE,
       q4.shift,
       q4.machineno AS mc,
       q4.sd_sider,
       q4.count,
       ROUND([q4.prdnkgs], 2) AS prdnkgs,
       ROUND(q4.production, 2) AS hank,
       q4.WKDSPINDLES AS wspdl,
       ROUND([q4.stdgps], 2) AS stdgps,
       ROUND([q4.gpsach], 2) AS gpsach,
       ROUND([q4.stdkg], 2) AS stdkg,
       ROUND((stdgps * wspdl) / 1000, 2) AS stdkgs,
       ROUND(gpsach / stdgps * 100, 2) AS pper,
       ROUND(wspdl / q4.spindle * 100, 2) AS uper,
       ROUND(pper / uper * 100, 2) AS eper,
       q4.waste,
       ROUND(q4.waste / prdnkgs * 100, 2) AS uwas,
       ROUND(q4.stpmnts, 2) AS stpmnts,
       q4.speed
FROM q4
GROUP BY q4.DATE,
         q4.shift,
         q4.machineno,
         q4.sd_sider,
         q4.count,
         q4.production,
         q4.CNO,
         q4.SPINDLE,
         q4.STPMNTS,
         q4.WKDSPINDLES,
         q4.prdnkgs,
         q4.waste,
         q4.stdgps,
         q4.gpsach,
         q4.stdkg,
         q4.speed;
SELECT format([Query4.DATE], "dd/mm/yyyy") AS dates,
       Query4.shift,
       Query4.machineno AS mc,
       Query4.sd_sider AS sider,
       Query4.count,
       ROUND([Query4.prdnkgs], 2) AS prdnkgs,
       ROUND(Query4.production, 2) AS hank,
       Query4.WKDSPINDLES AS wspdl,
       ROUND([query4.stdgps], 2) AS stdgps,
       ROUND([query4.gpsach], 2) AS gpsach,
       ROUND([query4.stdkg], 2) AS stdkg,
       ROUND((stdgps * wspdl) / 1000, 2) AS stdkgs,
       ROUND(gpsach / stdgps * 100, 2) AS pper,
       ROUND(wspdl / Query4.spindle * 100, 2) AS uper,
       ROUND(pper / uper * 100, 2) AS eper,
       Query4.waste,
       ROUND(Query4.waste / prdnkgs * 100, 2) AS uwas,
       ROUND(Query4.stpmnts, 2) AS stpmnts,
       Query4.speed
FROM Query4
GROUP BY Query4.DATE,
         Query4.shift,
         Query4.machineno,
         Query4.sd_sider,
         Query4.count,
         Query4.production,
         Query4.CNO,
         Query4.SPINDLE,
         Query4.STPMNTS,
         Query4.WKDSPINDLES,
         Query4.prdnkgs,
         Query4.stdgps,
         Query4.gpsach,
         Query4.stdkg,
         Query4.speed,
         Query4.waste
UNION
SELECT q4.DATE,
       q4.shift,
       q4.machineno AS mc,
       q4.sd_sider,
       q4.count,
       ROUND([q4.prdnkgs], 2) AS prdnkgs,
       ROUND(q4.production, 2) AS hank,
       q4.WKDSPINDLES AS wspdl,
       ROUND([q4.stdgps], 2) AS stdgps,
       ROUND([q4.gpsach], 2) AS gpsach,
       ROUND([q4.stdkg], 2) AS stdkg,
       ROUND((stdgps * wspdl) / 1000, 2) AS stdkgs,
       ROUND(gpsach / stdgps * 100, 2) AS pper,
       ROUND(wspdl / q4.spindle * 100, 2) AS uper,
       ROUND(pper / uper * 100, 2) AS eper,
       q4.waste,
       ROUND(q4.waste / prdnkgs * 100, 2) AS uwas,
       ROUND(q4.stpmnts, 2) AS stpmnts,
       q4.speed
FROM q4
GROUP BY q4.DATE,
         q4.shift,
         q4.machineno,
         q4.sd_sider,
         q4.count,
         q4.production,
         q4.CNO,
         q4.SPINDLE,
         q4.STPMNTS,
         q4.WKDSPINDLES,
         q4.prdnkgs,
         q4.waste,
         q4.stdgps,
         q4.gpsach,
         q4.stdkg,
         q4.speed;
 
Bullschmidt

Bullschmidt

Freelance DB Developer
Local time
Yesterday, 18:49
Joined
May 9, 2019
Messages
40
I have got one Union query which gives an error 'This expression is typed incorrectly or too complex to be evaluated' is getting. But if the statement executes separately with out UNION clause it is generating correctly. Pls advise how to sort out the issue.
Click to expand...

In general I would say to make sure that each field in the SQL statements being UNIONed such as field 1 and field 1 and field 1 are all the same exact type of field and of course be sure that all have the exact same number of fields.

If too complex then maybe you could initially create 3 separate tables with the proper structure and then fill those as needed via SQL statements and have the final UNION SQL statement use those tables.
 
P

plog

Banishment Pending
Local time
Yesterday, 18:49
Joined
May 11, 2011
Messages
11,638
UNION query pro-tip:

Only use SELECT, FROM and UNION clauses in a UNION query. No WHERE, no JOIN, No GROUP BY. Etc.

Use those other clauses (WHERE, JOIN, GROUP BY, etc) before and after the UNION query. Make the UNION as simple as possible and use subqueries to get you data ready for the UNION, and then use the UNION as a subquery if you need to do further operations on the UNION dataset as a whole
 
D

dineshkm

New member
Local time
Yesterday, 16:49
Joined
Jun 4, 2019
Messages
3
Re: Union query error for the blelow select clause

Dear Sir/Madam

As suggested by you I have give the SQL statement given by you, but I am getting the same error. I attach the screen shot of the same. Pls go thru and help me to solve the issue.

Regards
Dinesh
 

Attachments

  • access.JPG
    access.JPG
    62.3 KB · Views: 68
June7

June7

AWF VIP
Local time
Yesterday, 15:49
Joined
Mar 9, 2014
Messages
5,466
Image doesn't really help. If you want to provide db for analysis, follow instructions at bottom of my post.
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 18:49
Joined
Feb 28, 2001
Messages
27,147
I didn't see any obvious bracketing or parenthetical errors.

You DO realize that the resulting UNION query will not be sorted top-to-bottom? Each segment will be sorted but overall, the ordering you requested will be segmented.

As suggested, if you wanted strict end-to-end ordering, you would take out ALL of the ORDER BY clauses and make the union. Name it. Then in a layered query impose the order. For that matter, impose the formulae in the outer layer as well. Use the UNION only for the purpose of gathering. Use the outer layer for sorting, ordering, computation, aggregates, etc. That might simplify your query enough to let it pass.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom