Subquery Issue 1

[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.

 

[KARL DEWEY]

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.

 

[Deb]

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!

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.

 

[Deb]

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?

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.

 

[KARL DEWEY]

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.

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?

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.

 

[Deb]

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.

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.

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?

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.