By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,339 Members | 1,644 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,339 IT Pros & Developers. It's quick & easy.

Help Union SQL for simpletons

P: 38
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT "A) No. Workorder Completed" As Field, DCount("[Query3]![Type]","[Query3]", "[Query3]![Compliant]Is Not Null") + 
  2.  DCount("[Query4]![Type]","[Query4]", "[Query4]![Status]='1'") As [CountOfWork]
  3. From[Query3];
Expand|Select|Wrap|Line Numbers
  1. 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")
  2. As [CountOfWork]
  3. FROM [Query3];
Expand|Select|Wrap|Line Numbers
  1. UNION SELECT "C) Total of Workorder" As Field, DCount("[Query3]![Type]","[Query3]", "[Query3]![Compliant]Is Not Null") + 
  2. DCount("[Query4]![Type]","[Query4]", "[Query4]![Status]='1'") / 
  3. DCount("[Query3]![Workorder]","[Query3]", "[Query3]![Compliant]Is Not Null" ) + DCount("[Query3]![Type]","[Query3]", "[Query3]![ComuOverdue]Is  Null") 
  4. 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
Feb 28 '08 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 446
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
Expand|Select|Wrap|Line Numbers
  1. UNION SELECT "C) Total of Workorder" As Field, (DCount("[Query3]![Type]","[Query3]", "[Query3]![Compliant]Is Not Null") + 
  2. DCount("[Query4]![Type]","[Query4]", "[Query4]![Status]='1'") )/( 
  3. DCount("[Query3]![Workorder]","[Query3]", "[Query3]![Compliant]Is Not Null" ) + DCount("[Query3]![Type]","[Query3]", "[Query3]![ComuOverdue]Is Null") )
  4. 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
Feb 29 '08 #2

NeoPa
Expert Mod 15k+
P: 31,768
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.
Mar 1 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.