Concatinate values in query

G

Guest

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

Guest

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

Guest

I expect WorkOrdNum is text rather than numeric. Try:

Concatenate("SELECT Defect FROM tblqryOlga2 WHERE WorkOrdNum =""" &
[WorkOrdNum] & """") AS Defects
--
Duane Hookom
Microsoft Access MVP


Ray said:
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;


Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
Click to expand...
Click to expand...
 
G

Guest

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] & """")



Duane Hookom said:
I expect WorkOrdNum is text rather than numeric. Try:

Concatenate("SELECT Defect FROM tblqryOlga2 WHERE WorkOrdNum =""" &
[WorkOrdNum] & """") AS Defects
--
Duane Hookom
Microsoft Access MVP


Ray said:
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;


Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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


Ray said:
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] & """")



Duane Hookom said:
I expect WorkOrdNum is text rather than numeric. Try:

Concatenate("SELECT Defect FROM tblqryOlga2 WHERE WorkOrdNum =""" &
[WorkOrdNum] & """") AS Defects
--
Duane Hookom
Microsoft Access MVP


Ray said:
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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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;


Duane Hookom said:
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


Ray said:
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] & """")



Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

You didn't expand on your "Still failed...".

Is frmDateSelection open with legitimate dates entered? What happens if you
replace the form/control references with hard-coded date values?

This sql would be more efficient since the WHERE clause replaces the HAVING

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
WHERE tblAudits.AuditDate Between [forms]![frmDateSelection]![Text0]
And [forms]![frmDateSelection]![text2]) AND tblAuditSub.Defect Is Not
Null AND tblWorkOrder.Studio=7
GROUP BY tblWorkOrder.WorkOrdNum, tblAudits.AuditDate, tblAuditSub.Defect,
tblWorkOrder.Studio
ORDER BY tblWorkOrder.WorkOrdNum;


--
Duane Hookom
Microsoft Access MVP


Ray said:
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;


Duane Hookom said:
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


Ray said:
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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

The failure was the same.

BUT IT WORKED when I hard coded in the dates. Am getting numbers instead of
text, but I can fix that (I believe)

qryOlga1 and qryOlga2 both work alone using the "Between
[Forms]![frmDateSelection]![Text0] And [Forms]![frmDateSelection]![Text2]" as
criteria.

Seems strange to me. What can I do to keep the flexability of using a form
to set the dates?


Duane Hookom said:
You didn't expand on your "Still failed...".

Is frmDateSelection open with legitimate dates entered? What happens if you
replace the form/control references with hard-coded date values?

This sql would be more efficient since the WHERE clause replaces the HAVING

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
WHERE tblAudits.AuditDate Between [forms]![frmDateSelection]![Text0]
And [forms]![frmDateSelection]![text2]) AND tblAuditSub.Defect Is Not
Null AND tblWorkOrder.Studio=7
GROUP BY tblWorkOrder.WorkOrdNum, tblAudits.AuditDate, tblAuditSub.Defect,
tblWorkOrder.Studio
ORDER BY tblWorkOrder.WorkOrdNum;


--
Duane Hookom
Microsoft Access MVP


Ray said:
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;


Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

You may need to dynamically build and set the SQL property of the query.
Another alternative would be to create a table with one record and two date
fields. Enter you dates into the date fields and use the table in your query.
--
Duane Hookom
Microsoft Access MVP


Ray said:
The failure was the same.

BUT IT WORKED when I hard coded in the dates. Am getting numbers instead of
text, but I can fix that (I believe)

qryOlga1 and qryOlga2 both work alone using the "Between
[Forms]![frmDateSelection]![Text0] And [Forms]![frmDateSelection]![Text2]" as
criteria.

Seems strange to me. What can I do to keep the flexability of using a form
to set the dates?


Duane Hookom said:
You didn't expand on your "Still failed...".

Is frmDateSelection open with legitimate dates entered? What happens if you
replace the form/control references with hard-coded date values?

This sql would be more efficient since the WHERE clause replaces the HAVING

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
WHERE tblAudits.AuditDate Between [forms]![frmDateSelection]![Text0]
And [forms]![frmDateSelection]![text2]) AND tblAuditSub.Defect Is Not
Null AND tblWorkOrder.Studio=7
GROUP BY tblWorkOrder.WorkOrdNum, tblAudits.AuditDate, tblAuditSub.Defect,
tblWorkOrder.Studio
ORDER BY tblWorkOrder.WorkOrdNum;


--
Duane Hookom
Microsoft Access MVP


Ray said:
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.
Click to expand...
Click to expand...
Click to expand...
 
T

tina

make sure you define the form references as Date/Time data type in the
query. to do that, open the query in Design view and, from the menu bar,
click Query | Parameters. in the Query Parameters box, on the first line of
the Parameter column, enter the complete form reference for the first date
value. it must be *exactly* the same as the reference you entered in the
criteria grid in query Design view. on the same line, in the Data Type
column, choose Date/Time. go to the next line, and enter the complete form
reference for the second date value - again, exactly the same as the
criteria reference. and again, choose Date/Time in the Data Type column.
click OK, and save the query.

hth


Ray said:
The failure was the same.

BUT IT WORKED when I hard coded in the dates. Am getting numbers instead of
text, but I can fix that (I believe)

qryOlga1 and qryOlga2 both work alone using the "Between
[Forms]![frmDateSelection]![Text0] And [Forms]![frmDateSelection]![Text2]" as
criteria.

Seems strange to me. What can I do to keep the flexability of using a form
to set the dates?


Duane Hookom said:
You didn't expand on your "Still failed...".

Is frmDateSelection open with legitimate dates entered? What happens if you
replace the form/control references with hard-coded date values?

This sql would be more efficient since the WHERE clause replaces the HAVING

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
WHERE tblAudits.AuditDate Between [forms]![frmDateSelection]![Text0]
And [forms]![frmDateSelection]![text2]) AND tblAuditSub.Defect Is Not
Null AND tblWorkOrder.Studio=7
GROUP BY tblWorkOrder.WorkOrdNum, tblAudits.AuditDate, tblAuditSub.Defect,
tblWorkOrder.Studio
ORDER BY tblWorkOrder.WorkOrdNum;


--
Duane Hookom
Microsoft Access MVP


Ray said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Expanding query 3
Avg in a query 3
Move Values to Fields in Query? 6
Query to take count and find most likely 4
Operation must use an updatable query 1
Data/Values change when Query Analyzed in Excel 3
layer problems in certain browsers 4
Weird characters in query results 1

Top