SELECT tblWorkOrder.WorkOrdNum, tblAudits.AuditDate, tblAuditSub.Defect,
Count(tblAuditSub.Defect) AS [Count]
FROM (tblWorkOrder LEFT JOIN tblAudits ON tblWorkOrder.WorkOrdNum =
tblAudits.WorkOrdNum) LEFT JOIN tblAuditSub ON tblAudits.AuditsPriKey =
tblAuditSub.AuditsPriKey
GROUP BY tblWorkOrder.WorkOrdNum, tblAudits.AuditDate, tblAuditSub.Defect,
tblWorkOrder.Studio
HAVING (((tblAudits.AuditDate) Between [forms]![frmDateSelection]![Text0]
And [forms]![frmDateSelection]![text2]) AND ((tblAuditSub.Defect) Is Not
Null) AND ((tblWorkOrder.Studio)=7))
ORDER BY tblWorkOrder.WorkOrdNum;
:
What is the SQL view of qryOlga2? Can we assume it has the fields WorkOrdNum
and Defect?
"Still failed.." can you be more specific?
Are you using lookup fields defined in tables?
--
Duane Hookom
Microsoft Access MVP
:
Still failed..
WorkOrdNum is Text.
Put this in and made change to FROM tblqryOlga2 to the following FROM qryOlga2
qryOlga2.Defect comes from a table where Defect is a combobox which stores a
number, if that matters.
Defects: Concatenate("SELECT Defect FROM qryOlga2 WHERE WorkOrdNum =""" &
[WorkOrdNum] & """")
:
I expect WorkOrdNum is text rather than numeric. Try:
Concatenate("SELECT Defect FROM tblqryOlga2 WHERE WorkOrdNum =""" &
[WorkOrdNum] & """") AS Defects
--
Duane Hookom
Microsoft Access MVP
:
I did my own query and got a failure. The error is listed below
MsgBox error:
No Value Given for One or more Required Parameters
Module error:
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Here is my SQL:
SELECT qryOlga1.AuditType, qryOlga1.AuditDate, qryOlga1.WorkOrdNum,
qryOlga1.Title, qryOlga1.WOQty, qryOlga1.[Audit Qty], qryOlga1.[Failed
Audits], qryOlga1.[Total Samples], qryOlga1.[Failed Samples],
Concatenate("SELECT Defect FROM tblqryOlga2 WHERE WorkOrdNum =" &
[WorkOrdNum]) AS Defects
FROM qryOlga1;
:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
--
Duane Hookom
Microsoft Access MVP
:
I have two tables: WorkOrder and Defects with a one to many relationship.
My query results are like the following
WO# Defect
1111 Bad Shrink
1111 Missing Sticker
1111 Bad Carton
2222 Bad Shrink
2222 Torn Pages
Is there anyway of getting the results to look like this??
1111 Bad Shrink, Missing Sticker, Bad Carton
2222 Bad Shrink, Torn Pages
I have no idea what to do or if it is even possible.