Need help trying to combine queries into 1 query

[Guest]

Hi all,

I've got three queries that work together to combine the results of two
tables. I've got two queries which sum the values (grouped as required) I
need from two tables. I then use a final query to pair the values up, using
a join.

However, the query is dynamic, as it's based on search criteria specified by
the user. So what I'd really like to is create one big query which will
still create the result I need without me having to rewrite the existing
queries, every time the user changes the search criteria.

The following is the queries. I removed the user specific aspects of the
SQL statement as I can add and remove these easily myself (just
adding/removing qualifiers from the where statement). The 'guts' of the
queries are below. Optimally, I'd like to pass the query as a String to
another form and use that String as the RecordSource. Getting the String
back and forth I can do, I'm just having issues creating one query from my
three existing queries.

-----------------------------------------------
QUERY 1: temp_getTotalTestedQuantity
-----------------------------------------------
SELECT tblDetail.DTL_ITEM_NUM, tblDetail.DTL_AREA_NUM,
Sum(tblDetail.DTL_QUAN) AS ItemsReviewed
FROM tblDetail
GROUP BY tblDetail.DTL_ITEM_NUM, tblDetail.DTL_AREA_NUM;

----------------------------------------------
QUERY 2: temp_getTotalDefectQuantity
----------------------------------------------
SELECT tblDefectCount.DC_ITEM_NUM, tblDefectCount.DC_AREA_NUM,
Sum(tblDefectCount.DC_DF_QUAN) AS DefectsSum
FROM tblDefectCount
GROUP BY tblDefectCount.DC_ITEM_NUM, tblDefectCount.DC_AREA_NUM;

-----------------------------------------
QUERY 3: temp_getTotalQuantities
-----------------------------------------
SELECT DISTINCT temp_getTotalTestedQuantity.DTL_ITEM_NUM,
temp_getTotalTestedQuantity.DTL_AREA_NUM,
temp_getTotalTestedQuantity.ItemsReviewed,
temp_getTotalDefectQuantity.DefectsSum
FROM temp_getTotalTestedQuantity
LEFT JOIN temp_getTotalDefectQuantity ON
(temp_getTotalTestedQuantity.DTL_AREA_NUM =
temp_getTotalDefectQuantity.DC_AREA_NUM) AND
(temp_getTotalTestedQuantity.DTL_ITEM_NUM =
temp_getTotalDefectQuantity.DC_ITEM_NUM);

If anyone has any suggestions, or if you have to tell me I can't do what I
want, I'd appreciate it.

Thanks,
Jay

 

[Guest]

What do you want that is "combined"?

I want one query to do the work of all three queries.

Were you wanting the text of Query 1 and 2 in the same QueryDef as
Query 3? What for?

My goal is not to have a queryDef at all, but simply a string. This is
because the string will change based on search criteria selected by user (ie.
date range, item #, etc). I don't want to have to keep recreating/modifying
the queryDef. Seems unnecessary.

But if you want to, just replace <query name> with <query text> on
the FROM clause of Query 3, and make sure a pair of () encloses each
<query text> block.

I'm trying that, but I keep getting an error in FROM clause.
The code is as follows:

SELECT DISTINCT qryOne.DTL_ITEM_NUM,
qryOne.DTL_AREA_NUM,
qryOne.ItemsReviewed,
temp_getTotalDefectQuantity.DefectsSum
FROM (SELECT tblDetail.DTL_ITEM_NUM, tblDetail.DTL_AREA_NUM,
Sum(tblDetail.DTL_QUAN) AS ItemsReviewed
FROM tblDetail
GROUP BY tblDetail.DTL_ITEM_NUM, tblDetail.DTL_AREA_NUM) qryOne
LEFT JOIN temp_getTotalDefectQuantity ON
(qryOne.DTL_AREA_NUM = temp_getTotalDefectQuantity.DC_AREA_NUM) AND
(qryOne.DTL_ITEM_NUM = temp_getTotalDefectQuantity.DC_ITEM_NUM);

 

[Guest]

One other thing:

Can I use a <query text> block in the <query name> area of the line:
LEFT JOIN <query name> ON ...

Not really sure if that's allowed.

Thanks again,
Jay

 

[Chris2]

Hi all,

However, the query is dynamic, as it's based on search criteria specified by
the user. So what I'd really like to is create one big query which will
still create the result I need without me having to rewrite the existing
queries, every time the user changes the search criteria.

Getting the String
back and forth I can do, I'm just having issues creating one query from my
three existing queries.

Jay,

What issues are you having creating one query?

What do you want that is "combined"?

You have Query 3 that operates on Query 1 and 2. As far I can see,
Queries 1 and 2 are combined in Query 3.

Were you wanting the text of Query 1 and 2 in the same QueryDef as
Query 3? What for? Executing Query 3 with the full text of Query 1
and 2 replacing their query names in the FROM clause won't be any
different than executing Query 3 as-is.

But if you want to, just replace <query name> with <query text> on
the FROM clause of Query 3, and make sure a pair of () encloses each
<query text> block.

 

[Chris2]

I want one query to do the work of all three queries.


My goal is not to have a queryDef at all, but simply a string. This is
because the string will change based on search criteria selected by user (ie.
date range, item #, etc). I don't want to have to keep recreating/modifying
the queryDef. Seems unnecessary.


I'm trying that, but I keep getting an error in FROM clause.
The code is as follows:

SELECT DISTINCT qryOne.DTL_ITEM_NUM,
qryOne.DTL_AREA_NUM,
qryOne.ItemsReviewed,
temp_getTotalDefectQuantity.DefectsSum
FROM (SELECT tblDetail.DTL_ITEM_NUM, tblDetail.DTL_AREA_NUM,
Sum(tblDetail.DTL_QUAN) AS ItemsReviewed
FROM tblDetail
GROUP BY tblDetail.DTL_ITEM_NUM, tblDetail.DTL_AREA_NUM) qryOne
LEFT JOIN temp_getTotalDefectQuantity ON
(qryOne.DTL_AREA_NUM = temp_getTotalDefectQuantity.DC_AREA_NUM) AND
(qryOne.DTL_ITEM_NUM = temp_getTotalDefectQuantity.DC_ITEM_NUM);

Jay,

Please post all relevant sections of the VBA code generating the
error, or otherwise describe what is happening when the error
occurs.

Also, what is the error?

(The query above can be saved as a QueryDef, so it's not some basic
syntax error.)


Although meant for an sqlserver newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.


Sincerely,

Chris O.

 

[Chris2]

One other thing:

Can I use a <query text> block in the <query name> area of the line:
LEFT JOIN <query name> ON ...

Not really sure if that's allowed.

Thanks again,
Jay

Jay,

If you mean, can you:

SELECT M1.Col1
FROM (SELECT M01.Col1
FROM MyTab AS M01) AS M1
LEFT JOIN
(SELECT M02.Col1
FROM MyTab AS M02) AS M2
ON M1.Col1 = M2.Col2;

Then the answer is yes.


Sincerely,

Chris O.

 

[Guest]

Chris,

I'm not currently generating the query statement in VBA yet. What I had
planned on doing was verifying the SELECT statement worked in a normal Access
97 query. Once I got that working as I needed it, I would use that to help
me create the query in VBA. So currently the query exists as only an Access
query.

The entire SELECT statement that I've tried to create is as follows
(formatted to improve readability):

SELECT DISTINCT qryOne.DTL_ITEM_NUM, qryOne.DTL_AREA_NUM,
qryOne.ItemsReviewed, qryTwo.DefectsSum
FROM (SELECT tblDetail.DTL_ITEM_NUM AS qIN, tblDetail.DTL_AREA_NUM AS qAN,
Sum(tblDetail.DTL_QUAN) AS ItemsReviewed
FROM tblDetail
GROUP BY tblDetail.DTL_ITEM_NUM, tblDetail.DTL_AREA_NUM) AS qryOne
LEFT JOIN (SELECT tblDefectCount.DC_ITEM_NUM, tblDefectCount.DC_AREA_NUM,
Sum(tblDefectCount.DC_DF_QUAN) AS DefectsSum
FROM tblDefectCount
GROUP BY tblDefectCount.DC_ITEM_NUM,
tblDefectCount.DC_AREA_NUM) AS qryTwo
ON (qryOne.DTL_AREA_NUM = qryTwo.DC_AREA_NUM) AND (qryOne.DTL_ITEM_NUM =
qryTwo.DC_ITEM_NUM);

The error I get is: Syntax error in FROM clause.

I'm going to try running it in VBA, to see if there is also an error when
run using DoCmd.RunSQL. I haven't tried that yet. If I do and it works,
I'll post an update.

Hope that answers everything. I really appreciate your help.

Thanks,
Jay

 

[Guest]

Chris,

I noticed a small syntax error in the code i posted, but it does NOT affect
the result (still get sytax error). I renamed two of the retrieved valued as
qIN and qAN. I forgot to remove that from the code, and it doesn't change
anything when I did remove it.

Jay

 

[Chris2]

Chris,

I'm not currently generating the query statement in VBA yet. What I had
planned on doing was verifying the SELECT statement worked in a normal Access
97 query. Once I got that working as I needed it, I would use that to help
me create the query in VBA. So currently the query exists as only an Access
query.

The entire SELECT statement that I've tried to create is as follows
(formatted to improve readability):

SELECT DISTINCT qryOne.DTL_ITEM_NUM, qryOne.DTL_AREA_NUM,
qryOne.ItemsReviewed, qryTwo.DefectsSum
FROM (SELECT tblDetail.DTL_ITEM_NUM AS qIN, tblDetail.DTL_AREA_NUM AS qAN,
Sum(tblDetail.DTL_QUAN) AS ItemsReviewed
FROM tblDetail
GROUP BY tblDetail.DTL_ITEM_NUM,

tblDetail.DTL_AREA_NUM) AS qryOne

LEFT JOIN (SELECT tblDefectCount.DC_ITEM_NUM, tblDefectCount.DC_AREA_NUM,
Sum(tblDefectCount.DC_DF_QUAN) AS DefectsSum
FROM tblDefectCount
GROUP BY tblDefectCount.DC_ITEM_NUM,
tblDefectCount.DC_AREA_NUM) AS qryTwo
ON (qryOne.DTL_AREA_NUM = qryTwo.DC_AREA_NUM) AND (qryOne.DTL_ITEM_NUM =
qryTwo.DC_ITEM_NUM);

The error I get is: Syntax error in FROM clause.

I'm going to try running it in VBA, to see if there is also an error when
run using DoCmd.RunSQL. I haven't tried that yet. If I do and it works,
I'll post an update.

Hope that answers everything. I really appreciate your help.

Thanks,
Jay

:

Jay,

When I copy and paste that query into a QueryDef and try to save it,
it saves and returns no errors.

I am running Win2k SP-3, Access 2k SP-3, JET 4.0 SP-8

What are you running?


Sincerely,

Chris O.

 

[Guest]

Chris,

Maybe my setup is the issue.

I'm running WinXP S-P 1, Access 97 SR-2. I'm not sure what version of Jet
I'm running, but the only numbers I could find was 2.x and 3.5.

If I had to guess, I'd assume my problem is the fact I'm using Access 97.

Thanks again,
Jay