Best Chance...?

[Guest]

Hi again,

Alright, I've been playing around with the SQL code, and I think I'm close,
but I can't seem to get over the hump.

I found an example is using a SELECT statement in the FROM clause of the SQL
statement, but Access doesn't seem to like it. Is it not allowed in Access?
(I'm using A97).

I tried the following:
SELECT DISTINCT temp_getTotalTestedQuantity.DTL_ITEM_NUM,
temp_getTotalTestedQuantity.DTL_AREA_NUM,
temp_getTotalTestedQuantity.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)
temp_getTotalTestedQuantity,
(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)
temp_getTotalDefectQuantity
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);

No luck yet. Any suggestions, let me know.

Thanks,
Jay

 

[Guest]

My mistake, I hit the wrong button, please see post titled:
Need help trying to combine queries into 1 query

Sorry,
Jay

 

[Tom Ellison]

Dear Jay:

Well, Jet is not as capable as MSDE in such matters.

You should try making a separate saved query of this portion, then
referencing that portion in your larger "outer" query.

Tom Ellison

 

[Guest]

Yes, that's looking more and more likely. Chris2 seems to agree with you.

Thanks for the response.

Jay