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.
2  1487   dima69 181
Recognized Expert New Member
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.
thanks! dima69 I guess I have to rethink about what I really want DCount to do for my query.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: dmb000006 |
last post by:
My question is about a UNION query to deal with an (annoying) JOIN
over
two tables. I am joining over a double column primary key (where the
order of the columns can be changed). This is sooo slooow using a
join, but very fast using a union. How come this is?
Here is more detail...
I have two tables of 'associations' between pairs of objects.
|
by: Sean Dolan |
last post by:
typedef struct ntt {
int type;
union {
int i;
char* s;
};
}nt;
nt n;
n.i = 0;
|
by: laurenq uantrell |
last post by:
Wondering if there is a physical or realistic limitation to the number
of UNION statements I can create in a query? I have a client with
approx 250 tables - the data needs to be kept in seperate tables, but I
need to be filtering them to create single results sets. Each table
holds between 35,000 - 150,000 rows. Should I shoot myself now?
lq
|
by: Mikhail Teterin |
last post by:
Hello!
The sample program below is compiled fine by gcc (with -Wall), but rejected
by Sun's SUNWspro compiler (version 6 update 2).
The point of contention is, whether a value for one of the union's types can
be passed to a function directly -- without creating a separate variable of
the union type and assigning the appropriate field of it.
Is gcc being too liberal, or is this behavior simply part of a newer
|
by: Yevgen Muntyan |
last post by:
Hey,
Why is it legal to do
union U {unsigned char u; int a;};
union U u;
u.a = 1;
u.u;
I tried to find it in the standard, but I only found that
| | |
by: irwishlaw |
last post by:
After compiling and running the following program
#include <stdio.h>
#include <string.h>
typedef union
{
int a;
char b;
float c;
|
by: Bob Altman |
last post by:
Hi all,
I'm struggling to do something really basic. How do I declare a structure
that has a union that maps an array of 3 chars with 3 distinct char
variables. In other words, I want the following two statements to put the
same value into the same byte in the structure:
myStruct.Vars = 5;
myStruct.Var1 = 5;
|
by: Bryan Parkoff |
last post by:
I hate using struct / union with dot between two words. How can I use
one word instead of two words because I want the source code look reading
clear. three variables are shared inside one variable. I manipulate to
change 8-bit data before it causes to change 16-bit data and 32-bit data.
For example.
union
{
|
by: Cyprus106 |
last post by:
I've got a statement as follows that grabs matching records out two tables:
"SELECT * FROM BKRETAIL WHERE BKRETAIL.MATK='THISTHE' UNION SELECT * FROM BKMASTER WHERE BKMASTER.MATK='THISTHE';
My problem is that when I try to place something like "ORDER BY BKRETAIL.MEDSTA ASC" after the statement, it throws an error. I need these ordered in a certain arrangement. To be honest, what I really need is to order them by when MEDSTA's field is...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
