Need help trying to combine queries into 1 query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 29 Nov 2005 19:10 GMT

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

Reply to this Message

Chris2 - 29 Nov 2005 20:29 GMT

> Hi all,

<snip>

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

<snip>

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

<snip>

> 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

Reply to this Message

Jay - 29 Nov 2005 20:50 GMT

> 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);

Reply to this Message

Chris2 - 29 Nov 2005 21:59 GMT

> > What do you want that is "combined"?
>
[quoted text clipped - 18 lines]
> 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
[quoted text clipped - 3 lines]
> (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.

Reply to this Message

Jay - 30 Nov 2005 13:46 GMT

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

> > > What do you want that is "combined"?
> >
[quoted text clipped - 57 lines]
>
> Chris O.

Reply to this Message

Jay - 30 Nov 2005 14:08 GMT

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

> Chris,
>
[quoted text clipped - 93 lines]
> >
> > Chris O.

Reply to this Message

Chris2 - 01 Dec 2005 00:07 GMT

> Chris,
>
[quoted text clipped - 31 lines]
> 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.

Reply to this Message

Jay - 01 Dec 2005 15:03 GMT

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

> > Chris,
> >
[quoted text clipped - 55 lines]
>
> Chris O.

Reply to this Message

Jay - 29 Nov 2005 20:56 GMT

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

> > Hi all,
>
[quoted text clipped - 41 lines]
> > Thanks,
> > Jay

Reply to this Message

Chris2 - 29 Nov 2005 21:59 GMT

> One other thing:
>
[quoted text clipped - 5 lines]
> 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.

Reply to this Message