Try these two queries --
qry_All_Batch_qry ---
SELECT QCP_ID, BATCH_ID, DATE, Platform, Null AS Complete, couNtBatch_id
FROM QRY_ALL_COM
UNION ALL SELECT QCP_ID, BATCH_ID, DATE, Null, Null, countBatch_ID
From QRY_ALL_GOV
UNION ALL SELECT QCP_ID, BATCH_ID, DATE, Null, Null, CountBatch_ID
From QRY_RPH
UNION ALL SELECT QCP_ID, BATCH_ID, DATE, Null, Complete, CountBatch_ID
QRY_MailPrepALL;
SELECT QCP_ID, BATCH_ID, DATE, Platform, Complete, Sum([couNtBatch_id]) AS
Total_Batch_Count
FROM qry_All_Batch_qry;
--
Build a little, test a little.
LG said:
Sorry of all the confusion. It has changed many times and therefore that's
why so many different fields.
TBL_All, TBL_GOV_TBL_mailprep, TBL_MedDRPH
TBL_ALL and TBL_Employee. The employee table is linked by Proc_id
QRY_ALL_COM, QRY_ALL_GOV, QRY_MAILPREPALL, QRY_RPH are the queries.
QRY_ALL_COM produces:
(cancanated
to produce
last Name)
QCP_ID BATCH_ID DATE Platform coutBatch_id
Martin cm070709-1 07/07/09 QL 30
Martin cm070709-2 07/07/09 QL 50
Martin cm070709-3 07/07/09 RX 20
Martin cm070709-4 07/07/09 QL 30
QRY_ALL_GOV produces:
QCP_ID BATCH_ID DATE countBatch_ID
Hughes eg070709-1 07/07/2009 18
Martin CM070709-1 07/07/2009 20
Murphy LM070709-1 07/07/2009 30
QRY_RPH produces:
QCP_ID DATE BATCH_ID
CountBatch_ID
UR1YGXA 07/07/2009 ds070709-mdp1 5
QRY_MailPrepALL produces
(yes/no chk box)
Date QCP_ID Batch_ID
Complete QTY
07/07/2009 ur1ygxa ds090109-RXA -1
20
07/07/2009 CP704 ds070109-cm5 -4
28
Now I need to produce a report that tells me all QCP_ID with all batches and
number they completed.
I can change the cancatnation to ready just the QCP_ID instead of the name.
The other queries have the 2 colums batch_ID and the countBatch_id. The
count of the batch ID is to show how many were in that specific batch.
Different tables and fields are required because we do a mail merge with the
fields and I did not want too many blank fields. For Ex Gov only uses Last
Name as the Agency and there are other boxes that have info in them than the
TBL_ALL would have many blank fields.
Let me know if I need more information
:
I know I have posted this many times
I went back and read your other post and find you are not posting the same
information each time.
Examples --
TBL_ALL and TBL_Com
TBL_All, TBL_GOV_TBL_mailprep, TBL_MedDRPH
TBL_ALL and TBL_Employee. The employee table is linked by Proc_id
QRY_ALL_COM, QRY_ALL_GOV, QRY_MAIL PREP, QRY_RPH
Processor Batch ID Date Platform
Martin cm070709-1 07/07/09 QL
Martin cm070709-2 07/07/09 QL
Martin cm070709-3 07/07/09 RX
Martin cm070709-4 07/07/09 QL
DATE QCP_ID BATCH1 (this is set up to count) Platform
7/28/2009 DAN2 DS07282009C-1 15 RX
7/28/2009 DAN2 DS07282009P-2 250 Pharm
7/28/2009 DAN2 DS07282009Q-2 50 QL
7/28/2009 CS1 CS07282009C-1 25 RX
7/28/2009 CS1 CS07282009Q-1 50 QL
7/28/2009 NJ50 NJ07282009C-1 25 RX
7/28/2009 NJ50 NJ07282009P-1 50 Pharm
Date, LName, Batch_ID, and CountBatch_ID
Date, QCP_ID, Batch_ID
What does this mean --
BATCH1 (this is set up to count)
DS07282009C-1 15
Is this one field or two?
It appears to me that because you are not posting the correct information
any suggested queries presented to you are retyped and result in errors. You
in turn do not post back the SQL you actually used and do not fully state the
error or explain the results.
If you will post your actual table structure with field names and datatype
along with sample data some one can give you a solution.
--
Build a little, test a little.
:
I know I have posted this many times but, i am not getting it. Sorry for the
lack of knowledge.
I have 4 tables that have many different fields and many same fields.
TBL_All, TBL_GOV_TBL_mailprep, TBL_MedDRPH. I am trying to get a count in
each table for a date range of how many each person entered in each table.
ex: 09/04/2009 cp704, cs090409-cm1 50
(DATE) (QCP_ID) (BATCH_ID) (CountBatch_id)
The fields that are the same are Date, QCP_ID, Batch_ID. I need the count
of the batch_id field for each QCP_id. There is also platform that I need
but is only included in 3 of the tables.
I have tried various things and I get data of the same batches with someone
else's id and dates are correct. I didn't quite get the union query part
since I do have seperate queries already built.
Please help in explaining a process and where to go. I am a little unclear
about SQL and maybe that's where the disconnect is.
Any assistance in putting this together is greatly appreciated. The tables
have to be seperate because otherwise I would have too many empty fields.
