Subquery Issue 1

  • Thread starter Thread starter Deb
  • Start date Start date
D

Deb

Karl:

The way this is working now, the query is pulling all "Shipping Priority"
information from the tbl_History table. Here is the code for the Crosstab
query:

TRANSFORM Sum(tbl_History.DRMOTrans) AS DRMO
SELECT tbl_History.Division, tbl_History.Program
FROM tbl_History
GROUP BY tbl_History.Division, tbl_History.Program
PIVOT tbl_History.ShippingPriority;

That is working as expected. The current code for the query that is giving
me so many problems is:

SELECT ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
sqry_MonthlyRpt.TransactionDate, ctqry_MthDrmPriority.[1],
ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3],
ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO,
Sum(sqry_MonthlyRpt.UnitCost) AS SumOfUnitCost
FROM ctqry_MthDrmPriority INNER JOIN sqry_MonthlyRpt ON
(ctqry_MthDrmPriority.Division = sqry_MonthlyRpt.Division) AND
(ctqry_MthDrmPriority.Program = sqry_MonthlyRpt.Program)
GROUP BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
sqry_MonthlyRpt.TransactionDate, ctqry_MthDrmPriority.[1],
ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3],
ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO
HAVING (((ctqry_MthDrmPriority.[1]) Is Not Null)) OR
(((ctqry_MthDrmPriority.[2]) Is Not Null)) OR (((ctqry_MthDrmPriority.[3]) Is
Not Null)) OR (((ctqry_MthDrmPriority.CASREP) Is Not Null)) OR
(((ctqry_MthDrmPriority.DTO) Is Not Null))
ORDER BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program;

As you can see, I added the "Inner Join" that you suggested. The problem is
that, while the crosstab query works great on it's own, when I run the query
to add Unit Cost and Transaction date, the resulting query includes "Shipping
Priority" information that is contained in a separate column in tbl_History,
a column not included in the crosstab query. Shipping Priority information
is entered in either the "DRMO" field OR the "Shipped" field. I need the
query to pull only the information in the DRMO field which is correctly
displayed in the crosstab query.
 
the resulting query includes "Shipping Priority" information that is
contained in a separate column in tbl_History, a column not included in the
crosstab query.
You must have left someting out or posted the wrong query as there is no
"Shipping Priority", "DRMO" field or "Shipped" field.
 
The information for the queries comes from tbl_History which has four
"transaction" fields ... "ADJ", "DRM", "REC", and "SHP". The transaction
quantities are entered in these fields. tbl_History has another field,
"Priorities" to define how the transaction quantities were shipped. The
crosstab query pivots on tbl_History.Shipping Priority.

The SELECT query is based on the crosstab query "ctqry_MthDrmPriority". The
fields "[1], [2], [3], [CASREP], and [DTO] are the Shipping priorities fields
in the crosstab query. "sqry_MonthlyRpt" is another SELECT query which
extracts fields pertinent to the information for the report I'm trying to
create. That code is:

SELECT tbl_History.Division, tbl_History.Program,
tbl_History.ShippingPriority, tbl_History.DRMOTrans, tbl_History.SHPTrans,
tbl_History.TransactionDate, tbl_History.UnitCost, tbl_History.EMR,
tbl_History.Requisition
FROM tbl_History
WHERE (((tbl_History.DRMOTrans) Is Not Null)) OR (((tbl_History.SHPTrans) Is
Not Null))
ORDER BY tbl_History.Division, tbl_History.Program;

After looking at that code here, I'm wondering if I drop the
"tbl_History.SHPTrans" reference, perhaps that will solve the problem? I
think I'll give that a try while waiting for a response to this.

As always, many thanks for all of your help!

KARL DEWEY said:
contained in a separate column in tbl_History, a column not included in the
crosstab query.
You must have left someting out or posted the wrong query as there is no
"Shipping Priority", "DRMO" field or "Shipped" field.

--
Build a little, test a little.


Deb said:
Karl:

The way this is working now, the query is pulling all "Shipping Priority"
information from the tbl_History table. Here is the code for the Crosstab
query:

TRANSFORM Sum(tbl_History.DRMOTrans) AS DRMO
SELECT tbl_History.Division, tbl_History.Program
FROM tbl_History
GROUP BY tbl_History.Division, tbl_History.Program
PIVOT tbl_History.ShippingPriority;

That is working as expected. The current code for the query that is giving
me so many problems is:

SELECT ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
sqry_MonthlyRpt.TransactionDate, ctqry_MthDrmPriority.[1],
ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3],
ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO,
Sum(sqry_MonthlyRpt.UnitCost) AS SumOfUnitCost
FROM ctqry_MthDrmPriority INNER JOIN sqry_MonthlyRpt ON
(ctqry_MthDrmPriority.Division = sqry_MonthlyRpt.Division) AND
(ctqry_MthDrmPriority.Program = sqry_MonthlyRpt.Program)
GROUP BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
sqry_MonthlyRpt.TransactionDate, ctqry_MthDrmPriority.[1],
ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3],
ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO
HAVING (((ctqry_MthDrmPriority.[1]) Is Not Null)) OR
(((ctqry_MthDrmPriority.[2]) Is Not Null)) OR (((ctqry_MthDrmPriority.[3]) Is
Not Null)) OR (((ctqry_MthDrmPriority.CASREP) Is Not Null)) OR
(((ctqry_MthDrmPriority.DTO) Is Not Null))
ORDER BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program;

As you can see, I added the "Inner Join" that you suggested. The problem is
that, while the crosstab query works great on it's own, when I run the query
to add Unit Cost and Transaction date, the resulting query includes "Shipping
Priority" information that is contained in a separate column in tbl_History,
a column not included in the crosstab query. Shipping Priority information
is entered in either the "DRMO" field OR the "Shipped" field. I need the
query to pull only the information in the DRMO field which is correctly
displayed in the crosstab query.
Click to expand...
Click to expand...
 
Okay, tried removing the "SHPTrans" field from the SELECT query, but it did
not help. Apparently, anything in the "Shipping Priority" field is being
added to the query. What I need is shipping priority information related
only to the DRM field in tbl_History. How do I get the query to isolate that
information?

Deb said:
The information for the queries comes from tbl_History which has four
"transaction" fields ... "ADJ", "DRM", "REC", and "SHP". The transaction
quantities are entered in these fields. tbl_History has another field,
"Priorities" to define how the transaction quantities were shipped. The
crosstab query pivots on tbl_History.Shipping Priority.

The SELECT query is based on the crosstab query "ctqry_MthDrmPriority". The
fields "[1], [2], [3], [CASREP], and [DTO] are the Shipping priorities fields
in the crosstab query. "sqry_MonthlyRpt" is another SELECT query which
extracts fields pertinent to the information for the report I'm trying to
create. That code is:

SELECT tbl_History.Division, tbl_History.Program,
tbl_History.ShippingPriority, tbl_History.DRMOTrans, tbl_History.SHPTrans,
tbl_History.TransactionDate, tbl_History.UnitCost, tbl_History.EMR,
tbl_History.Requisition
FROM tbl_History
WHERE (((tbl_History.DRMOTrans) Is Not Null)) OR (((tbl_History.SHPTrans) Is
Not Null))
ORDER BY tbl_History.Division, tbl_History.Program;

After looking at that code here, I'm wondering if I drop the
"tbl_History.SHPTrans" reference, perhaps that will solve the problem? I
think I'll give that a try while waiting for a response to this.

As always, many thanks for all of your help!

KARL DEWEY said:
the resulting query includes "Shipping Priority" information that is
Click to expand...
contained in a separate column in tbl_History, a column not included in the
crosstab query.
You must have left someting out or posted the wrong query as there is no
"Shipping Priority", "DRMO" field or "Shipped" field.

--
Build a little, test a little.


Deb said:
Karl:

The way this is working now, the query is pulling all "Shipping Priority"
information from the tbl_History table. Here is the code for the Crosstab
query:

TRANSFORM Sum(tbl_History.DRMOTrans) AS DRMO
SELECT tbl_History.Division, tbl_History.Program
FROM tbl_History
GROUP BY tbl_History.Division, tbl_History.Program
PIVOT tbl_History.ShippingPriority;

That is working as expected. The current code for the query that is giving
me so many problems is:

SELECT ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
sqry_MonthlyRpt.TransactionDate, ctqry_MthDrmPriority.[1],
ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3],
ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO,
Sum(sqry_MonthlyRpt.UnitCost) AS SumOfUnitCost
FROM ctqry_MthDrmPriority INNER JOIN sqry_MonthlyRpt ON
(ctqry_MthDrmPriority.Division = sqry_MonthlyRpt.Division) AND
(ctqry_MthDrmPriority.Program = sqry_MonthlyRpt.Program)
GROUP BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
sqry_MonthlyRpt.TransactionDate, ctqry_MthDrmPriority.[1],
ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3],
ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO
HAVING (((ctqry_MthDrmPriority.[1]) Is Not Null)) OR
(((ctqry_MthDrmPriority.[2]) Is Not Null)) OR (((ctqry_MthDrmPriority.[3]) Is
Not Null)) OR (((ctqry_MthDrmPriority.CASREP) Is Not Null)) OR
(((ctqry_MthDrmPriority.DTO) Is Not Null))
ORDER BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program;

As you can see, I added the "Inner Join" that you suggested. The problem is
that, while the crosstab query works great on it's own, when I run the query
to add Unit Cost and Transaction date, the resulting query includes "Shipping
Priority" information that is contained in a separate column in tbl_History,
a column not included in the crosstab query. Shipping Priority information
is entered in either the "DRMO" field OR the "Shipped" field. I need the
query to pull only the information in the DRMO field which is correctly
displayed in the crosstab query.
Click to expand...
Click to expand...
Click to expand...
 
I am not following but try this --
SELECT tbl_History.Division, tbl_History.Program,
tbl_History.ShippingPriority, tbl_History.DRMOTrans, tbl_History.SHPTrans,
tbl_History.TransactionDate, tbl_History.UnitCost, tbl_History.EMR,
tbl_History.Requisition
FROM tbl_History
WHERE ((tbl_History.DRMOTrans Is Not Null) OR (tbl_History.SHPTrans) Is Not
Null)) AND tbl_History.DRM Is Not Null
ORDER BY tbl_History.Division, tbl_History.Program;

--
Build a little, test a little.


Deb said:
Okay, tried removing the "SHPTrans" field from the SELECT query, but it did
not help. Apparently, anything in the "Shipping Priority" field is being
added to the query. What I need is shipping priority information related
only to the DRM field in tbl_History. How do I get the query to isolate that
information?

Deb said:
The information for the queries comes from tbl_History which has four
"transaction" fields ... "ADJ", "DRM", "REC", and "SHP". The transaction
quantities are entered in these fields. tbl_History has another field,
"Priorities" to define how the transaction quantities were shipped. The
crosstab query pivots on tbl_History.Shipping Priority.

The SELECT query is based on the crosstab query "ctqry_MthDrmPriority". The
fields "[1], [2], [3], [CASREP], and [DTO] are the Shipping priorities fields
in the crosstab query. "sqry_MonthlyRpt" is another SELECT query which
extracts fields pertinent to the information for the report I'm trying to
create. That code is:

SELECT tbl_History.Division, tbl_History.Program,
tbl_History.ShippingPriority, tbl_History.DRMOTrans, tbl_History.SHPTrans,
tbl_History.TransactionDate, tbl_History.UnitCost, tbl_History.EMR,
tbl_History.Requisition
FROM tbl_History
WHERE (((tbl_History.DRMOTrans) Is Not Null)) OR (((tbl_History.SHPTrans) Is
Not Null))
ORDER BY tbl_History.Division, tbl_History.Program;

After looking at that code here, I'm wondering if I drop the
"tbl_History.SHPTrans" reference, perhaps that will solve the problem? I
think I'll give that a try while waiting for a response to this.

As always, many thanks for all of your help!

KARL DEWEY said:
the resulting query includes "Shipping Priority" information that is
contained in a separate column in tbl_History, a column not included in the
crosstab query.
You must have left someting out or posted the wrong query as there is no
"Shipping Priority", "DRMO" field or "Shipped" field.

--
Build a little, test a little.


:

Karl:

The way this is working now, the query is pulling all "Shipping Priority"
information from the tbl_History table. Here is the code for the Crosstab
query:

TRANSFORM Sum(tbl_History.DRMOTrans) AS DRMO
SELECT tbl_History.Division, tbl_History.Program
FROM tbl_History
GROUP BY tbl_History.Division, tbl_History.Program
PIVOT tbl_History.ShippingPriority;

That is working as expected. The current code for the query that is giving
me so many problems is:

SELECT ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
sqry_MonthlyRpt.TransactionDate, ctqry_MthDrmPriority.[1],
ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3],
ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO,
Sum(sqry_MonthlyRpt.UnitCost) AS SumOfUnitCost
FROM ctqry_MthDrmPriority INNER JOIN sqry_MonthlyRpt ON
(ctqry_MthDrmPriority.Division = sqry_MonthlyRpt.Division) AND
(ctqry_MthDrmPriority.Program = sqry_MonthlyRpt.Program)
GROUP BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
sqry_MonthlyRpt.TransactionDate, ctqry_MthDrmPriority.[1],
ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3],
ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO
HAVING (((ctqry_MthDrmPriority.[1]) Is Not Null)) OR
(((ctqry_MthDrmPriority.[2]) Is Not Null)) OR (((ctqry_MthDrmPriority.[3]) Is
Not Null)) OR (((ctqry_MthDrmPriority.CASREP) Is Not Null)) OR
(((ctqry_MthDrmPriority.DTO) Is Not Null))
ORDER BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program;

As you can see, I added the "Inner Join" that you suggested. The problem is
that, while the crosstab query works great on it's own, when I run the query
to add Unit Cost and Transaction date, the resulting query includes "Shipping
Priority" information that is contained in a separate column in tbl_History,
a column not included in the crosstab query. Shipping Priority information
is entered in either the "DRMO" field OR the "Shipped" field. I need the
query to pull only the information in the DRMO field which is correctly
displayed in the crosstab query.
Click to expand...
Click to expand...
Click to expand...
 
Karl:
I already had a query very similar to this, just had not added the "OR
(tbl_History.SHPTrans) Is Not Null)" since I was attempting to isolate the
DRMO items.

I'm trying to duplicate a report that the client currently produces in
Excel. Left-most columns are Division and Program. That is followed by a
breakdown of quantity "shipped" in columns indicating the shipping priority
("1", "2", "3", "CASREP", "DTO") - hence the need for a "crosstab" query.
The report contains a set of these columns for "Shipped Items" and a set for
"DRMO Items". The report also sums the values of the items shipped ("Unit
Cost").

The following is the result of one of the crosstab queries.

Division Program 1 2 3 CASREP DTO
56110AW SSEE INC E
56110KW SSEE 1 1
56110KW SSEE INC E 1
56120DB SSEE
56120EH CCOP 5 5 5
56120MD OBD
56120WB SSEE
56120WB SSEE INC E
56160MD COBLU

I want to add "Transaction Date" and "Unit Cost" to this query, along with a
colunm to indicate the number of EMRs/Requisitions (a count of these
documents). I'm going on the assumption that I can create another query
which will pull the crosstab query information and add the other columns I
need. I've been able to do this, but the resulting query always pulls both
DRMO and Shipped Unit Cost and quantities. I need separate data for a DRMO
report and a Shipped Report.


KARL DEWEY said:
I am not following but try this --
SELECT tbl_History.Division, tbl_History.Program,
tbl_History.ShippingPriority, tbl_History.DRMOTrans, tbl_History.SHPTrans,
tbl_History.TransactionDate, tbl_History.UnitCost, tbl_History.EMR,
tbl_History.Requisition
FROM tbl_History
WHERE ((tbl_History.DRMOTrans Is Not Null) OR (tbl_History.SHPTrans) Is Not
Null)) AND tbl_History.DRM Is Not Null
ORDER BY tbl_History.Division, tbl_History.Program;

--
Build a little, test a little.


Deb said:
Okay, tried removing the "SHPTrans" field from the SELECT query, but it did
not help. Apparently, anything in the "Shipping Priority" field is being
added to the query. What I need is shipping priority information related
only to the DRM field in tbl_History. How do I get the query to isolate that
information?

Deb said:
The information for the queries comes from tbl_History which has four
"transaction" fields ... "ADJ", "DRM", "REC", and "SHP". The transaction
quantities are entered in these fields. tbl_History has another field,
"Priorities" to define how the transaction quantities were shipped. The
crosstab query pivots on tbl_History.Shipping Priority.

The SELECT query is based on the crosstab query "ctqry_MthDrmPriority". The
fields "[1], [2], [3], [CASREP], and [DTO] are the Shipping priorities fields
in the crosstab query. "sqry_MonthlyRpt" is another SELECT query which
extracts fields pertinent to the information for the report I'm trying to
create. That code is:

SELECT tbl_History.Division, tbl_History.Program,
tbl_History.ShippingPriority, tbl_History.DRMOTrans, tbl_History.SHPTrans,
tbl_History.TransactionDate, tbl_History.UnitCost, tbl_History.EMR,
tbl_History.Requisition
FROM tbl_History
WHERE (((tbl_History.DRMOTrans) Is Not Null)) OR (((tbl_History.SHPTrans) Is
Not Null))
ORDER BY tbl_History.Division, tbl_History.Program;

After looking at that code here, I'm wondering if I drop the
"tbl_History.SHPTrans" reference, perhaps that will solve the problem? I
think I'll give that a try while waiting for a response to this.

As always, many thanks for all of your help!

:

the resulting query includes "Shipping Priority" information that is
contained in a separate column in tbl_History, a column not included in the
crosstab query.
You must have left someting out or posted the wrong query as there is no
"Shipping Priority", "DRMO" field or "Shipped" field.

--
Build a little, test a little.


:

Karl:

The way this is working now, the query is pulling all "Shipping Priority"
information from the tbl_History table. Here is the code for the Crosstab
query:

TRANSFORM Sum(tbl_History.DRMOTrans) AS DRMO
SELECT tbl_History.Division, tbl_History.Program
FROM tbl_History
GROUP BY tbl_History.Division, tbl_History.Program
PIVOT tbl_History.ShippingPriority;

That is working as expected. The current code for the query that is giving
me so many problems is:

SELECT ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
sqry_MonthlyRpt.TransactionDate, ctqry_MthDrmPriority.[1],
ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3],
ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO,
Sum(sqry_MonthlyRpt.UnitCost) AS SumOfUnitCost
FROM ctqry_MthDrmPriority INNER JOIN sqry_MonthlyRpt ON
(ctqry_MthDrmPriority.Division = sqry_MonthlyRpt.Division) AND
(ctqry_MthDrmPriority.Program = sqry_MonthlyRpt.Program)
GROUP BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
sqry_MonthlyRpt.TransactionDate, ctqry_MthDrmPriority.[1],
ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3],
ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO
HAVING (((ctqry_MthDrmPriority.[1]) Is Not Null)) OR
(((ctqry_MthDrmPriority.[2]) Is Not Null)) OR (((ctqry_MthDrmPriority.[3]) Is
Not Null)) OR (((ctqry_MthDrmPriority.CASREP) Is Not Null)) OR
(((ctqry_MthDrmPriority.DTO) Is Not Null))
ORDER BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program;

As you can see, I added the "Inner Join" that you suggested. The problem is
that, while the crosstab query works great on it's own, when I run the query
to add Unit Cost and Transaction date, the resulting query includes "Shipping
Priority" information that is contained in a separate column in tbl_History,
a column not included in the crosstab query. Shipping Priority information
is entered in either the "DRMO" field OR the "Shipped" field. I need the
query to pull only the information in the DRMO field which is correctly
displayed in the crosstab query.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top