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.

[CODE=sql]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];[/CODE]

[CODE=sql]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];[/CODE]

[CODE=sql]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];[/CODE]

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
[CODE=sql]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];[/CODE]
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.