Union SQL Need your Honest Opinion

[cephal0n]

Hi All!
First of I apologize for my previews post needing help on union select and not providing so more explanation, but thank you very much for your opinion and sugestion. I have thought about my problem and try to find a solution. I now have few answres and would like to ask for your expertise on how I handled my sql.

I made an sql code, I used sql specific by creating query > new > design view> close the Show Table > right click> click SQL Specific

I choose this method because I have a great need of it and test my record counting in the future. So please bear with me I when I don’t have any vb or macro included, this is just pure sql, and I have three tables and I joined their workorders using UNION SQL And count them using DCount, here is the tricky part, I'm using the DCount condition to produce specific results and I made a simple query so here's the Code:

SELECT "a) Total Workorder for the whole report" As Field, DCount("[Query2]![Workorder]") As [Total]
FROM [Query2];

UNION SELECT"e) No. of Workorders Completed" As Field, DCount("[Query2]![Overdue]Is Null", "[Query2]", "[Query2]![Member_A]Like 'PPC*' And [Query2]![Compliance] Is Not Null")
OR
DCount("[Query2]![Overdue]Is Null", "[Query2]", "[Query2]![Member_A]Like 'PPC*' And [Query2]![Compliance] Is Not Null And [Query2]![Member_B]Not Like 'PPC*'")
FROM [Query2];

My biggest problem is my sql logic here, I thought its going to be easy breezy, but I’m terribly NOT so here's the Idea. I have a query and it has joint field Workorder, Member_A, Member_B, Overdue and Compliance, but coming from different tables.

tblRecord
----------------------------
Workorder Compliance Overdue
01 1
02 1
03
04 1
05 1 1

tblRec1
------------------
Workorder Member_A
01 PPC
02 SLEX
03 PPC
04 PPC
05 SSD

tbRec2
------------------
Workorder Member_B
01 PPC
02 PPC
03 PPC
04 SLEX
05 PPC

I have 2 problems that must be solved
1. count the workorder in the whole table
2. Count workorders in the table where Meber_A like "PPC*" and Compliant = 1, but If Member_A Like "PPC*" and compliant Is Null
then look in tblRec2 and look in Member_B where not like "PPC*"

for problem no.1 I made the sql code:
SELECT "a) Total Workorder for the whole report" As Field, DCount("[Query2]![Workorder]") As [Total]
FROM [Query2];

for problem no. 2 I wrote the code:
UNION SELECT"e) No.of Workorders Completed" As Field, DCount("[Query2]![Overdue]Is Null", "[Query2]", "[Query2]![Member_A]Like 'PPC*' And [Query2]![Compliance] Is Not Null") OR DCount("[Query2]![Overdue]Is Null", "[Query2]", "[Query2]![Member_A]Like 'PPC*' And
[Query2]![Compliance] Is Not Null And [Query2]![Member_B]Not Like 'PPC*'")
FROM [Query2];

Here’s what my sql logic summary that I made for problem no. 2:
overdue is null and who (Member_A) is like ppc* and compliance = 1
Or
overdue isnull and who (Member_A) is like ppc* and compliance isnull and who (Member_B) is not like ppc*

My problem begins here when I get a –1 for the Total which is not an accurate result. The overdue and compliance field is pretty important in the Total outcome of the whole problem and record counting, but I also want to include who were the members participated, you will see them in my Member_A and Member_B fields.
I hope this explanation is not confusing and if it is, I'm willing to provide more, so what do you guys think? Is my logic here right, If not a very good suggestion or opinion would be very helpful.

[dima69]

Hi All!
First of I apologize for my previews post needing help on union select and not providing so more explanation, but thank you very much for your opinion and sugestion. I have thought about my problem and try to find a solution. I now have few answres and would like to ask for your expertise on how I handled my sql.

I made an sql code, I used sql specific by creating query > new > design view> close the Show Table > right click> click SQL Specific

I choose this method because I have a great need of it and test my record counting in the future. So please bear with me I when I don’t have any vb or macro included, this is just pure sql, and I have three tables and I joined their workorders using UNION SQL And count them using DCount, here is the tricky part, I'm using the DCount condition to produce specific results and I made a simple query so here's the Code:

SELECT "a) Total Workorder for the whole report" As Field, DCount("[Query2]![Workorder]") As [Total]
FROM [Query2];

UNION SELECT"e) No. of Workorders Completed" As Field, DCount("[Query2]![Overdue]Is Null", "[Query2]", "[Query2]![Member_A]Like 'PPC*' And [Query2]![Compliance] Is Not Null")
OR
DCount("[Query2]![Overdue]Is Null", "[Query2]", "[Query2]![Member_A]Like 'PPC*' And [Query2]![Compliance] Is Not Null And [Query2]![Member_B]Not Like 'PPC*'")
FROM [Query2];

My biggest problem is my sql logic here, I thought its going to be easy breezy, but I’m terribly NOT so here's the Idea. I have a query and it has joint field Workorder, Member_A, Member_B, Overdue and Compliance, but coming from different tables.

tblRecord
----------------------------
Workorder Compliance Overdue
01 1
02 1
03
04 1
05 1 1

tblRec1
------------------
Workorder Member_A
01 PPC
02 SLEX
03 PPC
04 PPC
05 SSD

tbRec2
------------------
Workorder Member_B
01 PPC
02 PPC
03 PPC
04 SLEX
05 PPC

I have 2 problems that must be solved
1. count the workorder in the whole table
2. Count workorders in the table where Meber_A like "PPC*" and Compliant = 1, but If Member_A Like "PPC*" and compliant Is Null
then look in tblRec2 and look in Member_B where not like "PPC*"

for problem no.1 I made the sql code:
SELECT "a) Total Workorder for the whole report" As Field, DCount("[Query2]![Workorder]") As [Total]
FROM [Query2];

for problem no. 2 I wrote the code:
UNION SELECT"e) No.of Workorders Completed" As Field, DCount("[Query2]![Overdue]Is Null", "[Query2]", "[Query2]![Member_A]Like 'PPC*' And [Query2]![Compliance] Is Not Null") OR DCount("[Query2]![Overdue]Is Null", "[Query2]", "[Query2]![Member_A]Like 'PPC*' And
[Query2]![Compliance] Is Not Null And [Query2]![Member_B]Not Like 'PPC*'")
FROM [Query2];

Here’s what my sql logic summary that I made for problem no. 2:
overdue is null and who (Member_A) is like ppc* and compliance = 1
Or
overdue isnull and who (Member_A) is like ppc* and compliance isnull and who (Member_B) is not like ppc*

My problem begins here when I get a –1 for the Total which is not an accurate result. The overdue and compliance field is pretty important in the Total outcome of the whole problem and record counting, but I also want to include who were the members participated, you will see them in my Member_A and Member_B fields.
I hope this explanation is not confusing and if it is, I'm willing to provide more, so what do you guys think? Is my logic here right, If not a very good suggestion or opinion would be very helpful.

Without getting too deep into your code, I can tell you that your attemtpt to use DCount in a wrong way. What you need is probably Count in Select ... Group By query.

[cephal0n]

thanks! dima69 I guess I have to rethink about what I really want DCount to do for my query.