Desperately need help with a Query

[Guest]

Hi

I need to know if it's possible to do one thing and if yes how !!!

Here's an example of an output of my DB (DB is Fiction of course,but the
question that follow is for real)

ID report# Test# Location depth
Serial
10951 1 1 CH: 0+100 -900

10952 1 2 CH: 0+110 -900
AB-05
10953 1 3 CH: 0+100 -300
10954 1 4 CH: 0+110 -300
AB-06
10955 1 5 CH: 0+100 Final
AB-07
10956 1 6 CH: 0+110 Final
10957 2 1 xxxxxxxxxx Final


Now I would like to have a query that would give me the following output

Report count(location) ListOfSerial
1 6 AB-05,AB-06,AB-07
2 1

Ok I know that the first two column are easy to have using the GROUP BY
clause, my problem is on the third column ... I'm not even sure it's possible
to do so but if it's possible to do it ... I'd like very much to have a hint
cause i'm clueless for now.

IF Any one can help ... Please do so !!!

Many Thanks for your help,

Stephane Pelletier

 

[Guest]

Sorry for the mess,
Column are ID,report#,test#,location,serial

Any value like 10951 are from ID and any value like AB-05 are from Serial

TIA

Stephane Pelletier

 

[PC Datasheet]

You need to create a function that iterates through your records and for
each Report# builds a comma separated string of all the Serials associated
with the Report#. The function goes in a standard module and then you enter
the function in an empty field in your query to give you ListOfSerial.
Assuming your function is named SerialList, it would look like this in your
query:
SerialList(ReportNum)

Note that your query would be a totals query.

 

[Guest]

Hi, Stephane.

I need to know if it's possible to do one thing and if yes how !!!

Of course it's possible.

I'd like very much to have a hint

First, create a new query and paste the following SQL into it:

SELECT [Report#], Count(Location) AS CntLoc,
fConcatFld("Serials", "Report#", "Serial", "Long", [Report#]) AS
ListOfSerials
FROM Serials
GROUP BY [Report#];

Does that help? No? Then please see the following Web page for Dev
Ashish's fConcatFld( ) VBA function, then copy and paste it into a standard
module, then save and compile:

http://www.mvps.org/access/modules/mdl0008.htm

Does that help? Almost? Just two slight problems? Okay. Make the
following changes in the code:

Replace:

lovConcat = lovConcat & lors(stFldToConcat) & "; "

with:

If (Nz(lors(stFldToConcat).Value, "") <> "") Then
lovConcat = lovConcat & lors(stFldToConcat) & ", "
End If

And replace:

fConcatFld = Left(lovConcat, Len(lovConcat) - 2)

with:

If (Nz(lovConcat, "") <> "") Then
fConcatFld = Left(lovConcat, Len(lovConcat) - 2)
End If


HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Hi Gunny,

I'M not sure it would work (I'LL try but ...)

What I need to do is

If you have the following data

Repport date test
1 2005-09-10 A
1 2005-09-10 B
1 2005-09-10 C
2 2005-09-11 A

the output of the query should give :

#Repport #Date #Test
1 2005-09-10 A,B,C
2 2005-09-11 A

In your example you try to concatened different field from the same row What
I need to do is concatened the data for one field but coming from different
rows where rows have the same report# and same date.

Do you think it still possible ???

Thanks for your help

Stephane

Hi, Stephane.

I need to know if it's possible to do one thing and if yes how !!!

Of course it's possible.

I'd like very much to have a hint

First, create a new query and paste the following SQL into it:

SELECT [Report#], Count(Location) AS CntLoc,
fConcatFld("Serials", "Report#", "Serial", "Long", [Report#]) AS
ListOfSerials
FROM Serials
GROUP BY [Report#];

Does that help? No? Then please see the following Web page for Dev
Ashish's fConcatFld( ) VBA function, then copy and paste it into a standard
module, then save and compile:

http://www.mvps.org/access/modules/mdl0008.htm

Does that help? Almost? Just two slight problems? Okay. Make the
following changes in the code:

Replace:

lovConcat = lovConcat & lors(stFldToConcat) & "; "

with:

If (Nz(lors(stFldToConcat).Value, "") <> "") Then
lovConcat = lovConcat & lors(stFldToConcat) & ", "
End If

And replace:

fConcatFld = Left(lovConcat, Len(lovConcat) - 2)

with:

If (Nz(lovConcat, "") <> "") Then
fConcatFld = Left(lovConcat, Len(lovConcat) - 2)
End If


HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Hi

I need to know if it's possible to do one thing and if yes how !!!

Here's an example of an output of my DB (DB is Fiction of course,but the
question that follow is for real)

ID report# Test# Location depth
Serial
10951 1 1 CH: 0+100 -900

10952 1 2 CH: 0+110 -900
AB-05
10953 1 3 CH: 0+100 -300
10954 1 4 CH: 0+110 -300
AB-06
10955 1 5 CH: 0+100 Final
AB-07
10956 1 6 CH: 0+110 Final
10957 2 1 xxxxxxxxxx Final


Now I would like to have a query that would give me the following output

Report count(location) ListOfSerial
1 6 AB-05,AB-06,AB-07
2 1

Ok I know that the first two column are easy to have using the GROUP BY
clause, my problem is on the third column ... I'm not even sure it's possible
to do so but if it's possible to do it ... I'd like very much to have a hint
cause i'm clueless for now.

IF Any one can help ... Please do so !!!

Many Thanks for your help,

Stephane Pelletier

 

[Guest]

Thanks, Thats what I thought, was hoping that there was an easiest way to do so

Stephane

 

[Guest]

Hi, Stephane.

From your first sentence, it appears to me that when you tried the example
in your own database, the last argument of the function was placed inside a
pair of double quotes. It's important that the last argument be a value from
the field, not the field name. If you put the last argument inside double
quotes like the other arguments, the result will concatenate all values in
that field -- for the entire table. For example, look at how the last
argument differs from the second argument, even though it is the same field:

fConcatFld("Serials", "Report#", "Serial", "Long", [Report#])

From your second sentence, it sounds like you need to pass two fields to the
function, both report# and date, not just a single field that the query
groups on. If so, this is a horse of a different color, and you'll need a
different query and a different user-defined VBA function.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Hi Gunny,

I'M not sure it would work (I'LL try but ...)

What I need to do is

If you have the following data

Repport date test
1 2005-09-10 A
1 2005-09-10 B
1 2005-09-10 C
2 2005-09-11 A

the output of the query should give :

#Repport #Date #Test
1 2005-09-10 A,B,C
2 2005-09-11 A

In your example you try to concatened different field from the same row What
I need to do is concatened the data for one field but coming from different
rows where rows have the same report# and same date.

Do you think it still possible ???

Thanks for your help

Stephane

Hi, Stephane.

I need to know if it's possible to do one thing and if yes how !!!

Of course it's possible.

I'd like very much to have a hint

First, create a new query and paste the following SQL into it:

SELECT [Report#], Count(Location) AS CntLoc,
fConcatFld("Serials", "Report#", "Serial", "Long", [Report#]) AS
ListOfSerials
FROM Serials
GROUP BY [Report#];

Does that help? No? Then please see the following Web page for Dev
Ashish's fConcatFld( ) VBA function, then copy and paste it into a standard
module, then save and compile:

http://www.mvps.org/access/modules/mdl0008.htm

Does that help? Almost? Just two slight problems? Okay. Make the
following changes in the code:

Replace:

lovConcat = lovConcat & lors(stFldToConcat) & "; "

with:

If (Nz(lors(stFldToConcat).Value, "") <> "") Then
lovConcat = lovConcat & lors(stFldToConcat) & ", "
End If

And replace:

fConcatFld = Left(lovConcat, Len(lovConcat) - 2)

with:

If (Nz(lovConcat, "") <> "") Then
fConcatFld = Left(lovConcat, Len(lovConcat) - 2)
End If


HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Hi

I need to know if it's possible to do one thing and if yes how !!!

Here's an example of an output of my DB (DB is Fiction of course,but the
question that follow is for real)

ID report# Test# Location depth
Serial
10951 1 1 CH: 0+100 -900

10952 1 2 CH: 0+110 -900
AB-05
10953 1 3 CH: 0+100 -300
10954 1 4 CH: 0+110 -300
AB-06
10955 1 5 CH: 0+100 Final
AB-07
10956 1 6 CH: 0+110 Final
10957 2 1 xxxxxxxxxx Final


Now I would like to have a query that would give me the following output

Report count(location) ListOfSerial
1 6 AB-05,AB-06,AB-07
2 1

Ok I know that the first two column are easy to have using the GROUP BY
clause, my problem is on the third column ... I'm not even sure it's possible
to do so but if it's possible to do it ... I'd like very much to have a hint
cause i'm clueless for now.

IF Any one can help ... Please do so !!!

Many Thanks for your help,

Stephane Pelletier

 

[Guest]

Thanks Gunny,

I'll try it correctly this time ... Again thanks for your time.

Hi, Stephane.

From your first sentence, it appears to me that when you tried the example
in your own database, the last argument of the function was placed inside a
pair of double quotes. It's important that the last argument be a value from
the field, not the field name. If you put the last argument inside double
quotes like the other arguments, the result will concatenate all values in
that field -- for the entire table. For example, look at how the last
argument differs from the second argument, even though it is the same field:

fConcatFld("Serials", "Report#", "Serial", "Long", [Report#])

From your second sentence, it sounds like you need to pass two fields to the
function, both report# and date, not just a single field that the query
groups on. If so, this is a horse of a different color, and you'll need a
different query and a different user-defined VBA function.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Hi Gunny,

I'M not sure it would work (I'LL try but ...)

What I need to do is

If you have the following data

Repport date test
1 2005-09-10 A
1 2005-09-10 B
1 2005-09-10 C
2 2005-09-11 A

the output of the query should give :

#Repport #Date #Test
1 2005-09-10 A,B,C
2 2005-09-11 A

In your example you try to concatened different field from the same row What
I need to do is concatened the data for one field but coming from different
rows where rows have the same report# and same date.

Do you think it still possible ???

Thanks for your help

Stephane

Hi, Stephane.

I need to know if it's possible to do one thing and if yes how !!!

Of course it's possible.

I'd like very much to have a hint

First, create a new query and paste the following SQL into it:

SELECT [Report#], Count(Location) AS CntLoc,
fConcatFld("Serials", "Report#", "Serial", "Long", [Report#]) AS
ListOfSerials
FROM Serials
GROUP BY [Report#];

Does that help? No? Then please see the following Web page for Dev
Ashish's fConcatFld( ) VBA function, then copy and paste it into a standard
module, then save and compile:

http://www.mvps.org/access/modules/mdl0008.htm

Does that help? Almost? Just two slight problems? Okay. Make the
following changes in the code:

Replace:

lovConcat = lovConcat & lors(stFldToConcat) & "; "

with:

If (Nz(lors(stFldToConcat).Value, "") <> "") Then
lovConcat = lovConcat & lors(stFldToConcat) & ", "
End If

And replace:

fConcatFld = Left(lovConcat, Len(lovConcat) - 2)

with:

If (Nz(lovConcat, "") <> "") Then
fConcatFld = Left(lovConcat, Len(lovConcat) - 2)
End If


HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Hi

I need to know if it's possible to do one thing and if yes how !!!

Here's an example of an output of my DB (DB is Fiction of course,but the
question that follow is for real)

ID report# Test# Location depth
Serial
10951 1 1 CH: 0+100 -900

10952 1 2 CH: 0+110 -900
AB-05
10953 1 3 CH: 0+100 -300
10954 1 4 CH: 0+110 -300
AB-06
10955 1 5 CH: 0+100 Final
AB-07
10956 1 6 CH: 0+110 Final
10957 2 1 xxxxxxxxxx Final


Now I would like to have a query that would give me the following output

Report count(location) ListOfSerial
1 6 AB-05,AB-06,AB-07
2 1

Ok I know that the first two column are easy to have using the GROUP BY
clause, my problem is on the third column ... I'm not even sure it's possible
to do so but if it's possible to do it ... I'd like very much to have a hint
cause i'm clueless for now.

IF Any one can help ... Please do so !!!

Many Thanks for your help,

Stephane Pelletier