Help Union SQL for simpletons

[cephal0n]

I am in a great need of help here, I’m using union sql using access for the firs time and I'm stuck with this problem, I used a DCount to count records
and create a single line of results, here is the code.

SELECT "A) No. Workorder Completed" As Field, DCount("[Query3]![Type]","[Query3]", "[Query3]![Compliant]Is Not Null") + 
 DCount("[Query4]![Type]","[Query4]", "[Query4]![Status]='1'") As [CountOfWork]
From[Query3];

UNION SELECT "B) No. Workorder for the Month" As Field, DCount("[Query3]![Workorder]","[Query3]", "[Query3]![Compliant]Is Not Null" ) + DCount("[Query3]![Type]","[Query3]", "[Query3]![ComuOverdue]Is  Null")
As [CountOfWork]
FROM [Query3];

UNION SELECT "C) Total of Workorder" As Field, DCount("[Query3]![Type]","[Query3]", "[Query3]![Compliant]Is Not Null") + 
DCount("[Query4]![Type]","[Query4]", "[Query4]![Status]='1'") / 
DCount("[Query3]![Workorder]","[Query3]", "[Query3]![Compliant]Is Not Null" ) + DCount("[Query3]![Type]","[Query3]", "[Query3]![ComuOverdue]Is  Null") 
FROM [Query3],[Query4];

I have the Totals from A=155 and B=537 and I divided A/B manually and the total is 0.2886++ but when DCount started dividing it shows a whole different output.

I would like to clear out that this code works for the first two problem A and B, but when I started dividing in problem C the total is not quite right, I tried the DCount for the first time and I liked it because its one of the solution I can think of, but its giving me really hard time understanding why it wont follow my simple instruction

[sierra7]

Hi
I'm somewhat bemused by your code! As far as I can see, each 'SELECT' is only going to return a single value.

The first two you say work. OK, but you can't UNION them as they are because there is a semi-colon between them.

The Third SELECT seems to be made-up with components from the first two but you are dividing without any bracket around you 'A' term and 'B' term. Like this

UNION SELECT "C) Total of Workorder" As Field, (DCount("[Query3]![Type]","[Query3]", "[Query3]![Compliant]Is Not Null") + 
DCount("[Query4]![Type]","[Query4]", "[Query4]![Status]='1'") )/( 
DCount("[Query3]![Workorder]","[Query3]", "[Query3]![Compliant]Is Not Null" ) + DCount("[Query3]![Type]","[Query3]", "[Query3]![ComuOverdue]Is Null") )
FROM [Query3],[Query4];

but even so I'm not sure this will work.

Personally, I would define four variables to hold each of the counted values, then evaluate the sums and ratioos as required.

Each DSum() function makes a call to the database and returns the full table (query in this case) each time which can cause performance issues if the network is busy etc.

Does this help?

S7

[NeoPa]

This is some strange SQL, HOWEVER, I think your actual problem in the third part is in the FROM clause. You only want the one FROM item. If the other two are working for you then use [Query3] again.