Subquery issue

D

Deb

I built a crosstab query, and want to build another query which pulls the
crosstab information plus Unit Cost from another query. Code is as follows:

SELECT ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
ctqry_MthDrmPriority.[1], ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3],
ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO,
FROM (SELECT sqry_MonthlyRpt.UnitCost FROM sqry_MonthlyRpt WHERE Division =
sqry_MonthlyRpt.Division) AS [Sum of Unit Costs] ctqry_MthDrmPriority,
GROUP BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
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;

I think using a subquery is the way to go, and I've tried several different
angles. The latest error message is:

"The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect."

I've checked for misspellings, and am just unsure if the punctuation is
incorrect or if something is missing. Of course, the subquery might even be
in the wrong place, I just don't know.

Please advise.
 
K

KARL DEWEY

I think you got to have an alias ---
FROM (SELECT sqry_MonthlyRpt.UnitCost FROM sqry_MonthlyRpt WHERE Division =
sqry_MonthlyRpt.Division) AS [Sum of Unit Costs] ctqry_MthDrmPriority,
GROUP BY ...

FROM (SELECT [XYZ].UnitCost FROM sqry_MonthlyRpt AS [XYZ] WHERE
[XYZ]Division = sqry_MonthlyRpt.Division) AS [Sum of Unit Costs]
ctqry_MthDrmPriority
GROUP BY ...

You have to remove comma after ctqry_MthDrmPriority and the subquery needs
to be joined to ctqry_MthDrmPriority.
 
J

Jeanette Cunningham

Hi Deb,
a quick look showed an extra comma in the 3rd line of the first select
statement.

ctqry_MthDrmPriority.DTO,
See the comma at the end of DTO in the line above


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
K

KARL DEWEY

You join two tables/queries like this --
FROM Table1 INNER JOIN Table2 ON Table1.FieldX = Table2.FieldY

If you do not join you will create a Cartesian effect that will produce
records amounting to the quanity in one table multiplied by the quanity in
the other.

--
Build a little, test a little.


Deb said:
Thanks Karl. Error message - "Syntax Error in FROM Clause" and
"ctqry_MthDrmPriority" was highlighted. ??? Also, how do you "join" a
subquery to the main query???

KARL DEWEY said:
I think you got to have an alias ---
FROM (SELECT sqry_MonthlyRpt.UnitCost FROM sqry_MonthlyRpt WHERE Division =
sqry_MonthlyRpt.Division) AS [Sum of Unit Costs] ctqry_MthDrmPriority,
GROUP BY ...

FROM (SELECT [XYZ].UnitCost FROM sqry_MonthlyRpt AS [XYZ] WHERE
[XYZ]Division = sqry_MonthlyRpt.Division) AS [Sum of Unit Costs]
ctqry_MthDrmPriority
GROUP BY ...

You have to remove comma after ctqry_MthDrmPriority and the subquery needs
to be joined to ctqry_MthDrmPriority.

--
Build a little, test a little.


Deb said:
I built a crosstab query, and want to build another query which pulls the
crosstab information plus Unit Cost from another query. Code is as follows:

SELECT ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
ctqry_MthDrmPriority.[1], ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3],
ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO,
FROM (SELECT sqry_MonthlyRpt.UnitCost FROM sqry_MonthlyRpt WHERE Division =
sqry_MonthlyRpt.Division) AS [Sum of Unit Costs] ctqry_MthDrmPriority,
GROUP BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
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;

I think using a subquery is the way to go, and I've tried several different
angles. The latest error message is:

"The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect."

I've checked for misspellings, and am just unsure if the punctuation is
incorrect or if something is missing. Of course, the subquery might even be
in the wrong place, I just don't know.

Please advise.
Click to expand...
Click to expand...
Click to expand...
 
D

Deb

Karl: Thanks again. I certainly don't want that Cartesian effect!!

Would the "join clause" go before the "(Select sqry_MonthlyRpt.UnitCost ...)
or after that. I hate being such a dunce, but flailing around with all of
these little patches has really got me down!

KARL DEWEY said:
You join two tables/queries like this --
FROM Table1 INNER JOIN Table2 ON Table1.FieldX = Table2.FieldY

If you do not join you will create a Cartesian effect that will produce
records amounting to the quanity in one table multiplied by the quanity in
the other.

--
Build a little, test a little.


Deb said:
Thanks Karl. Error message - "Syntax Error in FROM Clause" and
"ctqry_MthDrmPriority" was highlighted. ??? Also, how do you "join" a
subquery to the main query???

KARL DEWEY said:
I think you got to have an alias ---
FROM (SELECT sqry_MonthlyRpt.UnitCost FROM sqry_MonthlyRpt WHERE Division =
sqry_MonthlyRpt.Division) AS [Sum of Unit Costs] ctqry_MthDrmPriority,
GROUP BY ...

FROM (SELECT [XYZ].UnitCost FROM sqry_MonthlyRpt AS [XYZ] WHERE
[XYZ]Division = sqry_MonthlyRpt.Division) AS [Sum of Unit Costs]
ctqry_MthDrmPriority
GROUP BY ...

You have to remove comma after ctqry_MthDrmPriority and the subquery needs
to be joined to ctqry_MthDrmPriority.

--
Build a little, test a little.


:

I built a crosstab query, and want to build another query which pulls the
crosstab information plus Unit Cost from another query. Code is as follows:

SELECT ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
ctqry_MthDrmPriority.[1], ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3],
ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO,
FROM (SELECT sqry_MonthlyRpt.UnitCost FROM sqry_MonthlyRpt WHERE Division =
sqry_MonthlyRpt.Division) AS [Sum of Unit Costs] ctqry_MthDrmPriority,
GROUP BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
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;

I think using a subquery is the way to go, and I've tried several different
angles. The latest error message is:

"The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect."

I've checked for misspellings, and am just unsure if the punctuation is
incorrect or if something is missing. Of course, the subquery might even be
in the wrong place, I just don't know.

Please advise.
Click to expand...
Click to expand...
Click to expand...
 
D

Deb

Thank you Jeanette! Karl previously caught that, but if you see anything
else that might get me past this dilemma, please feel free to point out
anything that may help!

Jeanette Cunningham said:
Hi Deb,
a quick look showed an extra comma in the 3rd line of the first select
statement.

ctqry_MthDrmPriority.DTO,
See the comma at the end of DTO in the line above


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Deb said:
I built a crosstab query, and want to build another query which pulls the
crosstab information plus Unit Cost from another query. Code is as
follows:

SELECT ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
ctqry_MthDrmPriority.[1], ctqry_MthDrmPriority.[2],
ctqry_MthDrmPriority.[3],
ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO,
FROM (SELECT sqry_MonthlyRpt.UnitCost FROM sqry_MonthlyRpt WHERE Division
=
sqry_MonthlyRpt.Division) AS [Sum of Unit Costs] ctqry_MthDrmPriority,
GROUP BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program,
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;

I think using a subquery is the way to go, and I've tried several
different
angles. The latest error message is:

"The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect."

I've checked for misspellings, and am just unsure if the punctuation is
incorrect or if something is missing. Of course, the subquery might even
be
in the wrong place, I just don't know.

Please advise.
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subquery Issue 1 5
Subquery not integrating to query to display previous sum of quantities 3
Parameter Query 1
Subquery 6
Subquery MAX() function help 0
Crosstab query using union query has subquery 0
Help with a subquery 5
SQL "NOT IN" doesn't function as expected (refresh) 14

Top