Re: Need help trying to combine queries into 1 query

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

"Jay" wrote:

> 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
>
> "Chris2" wrote:
>
> >
> > "Jay" <Jay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:D4B7D092-5C11-47D7-9C30-507B3D7E56E4@xxxxxxxxxxxxxxxx
> > > > 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);
> >
> > 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.
> >
> >
> >
> >
.