Hello,

I'm hoping someone might be able to offer some guidance to my problem.
I have one query in MS Access which consists of 2 tables joined by a
SEQUENCE_ID. By joining the two tables I am able to produce a query
that gives me two fields, Part_Number and Product_Type. Below you can
see some sample data:

Part_Number Product_Type
10MC35231 XYZ1A
10MC35231 XYZ1B
23XB94142 XYZ1A
23XB94142 XYZ1B
23XB94142 XYZ1C
42PA76251 XYZ1D

Ideally, it would be nice for the query to have an additional field
which would concatenate the Product_Type's:

10MC35231 XYZ1A, XYZ1B
23XB94142 XYZ1A, XYZ1B, XYZ1C
42PA76251 XYZ1D

I found a few concatenate functions but it seemed that the either it
did not like that I was joining two tables in the query or that the
data was in only one table. I would really appreciate if someone could
help give me a direction with this.

Thanks in advance,
Aaron

Aaron Hoffman wrote:

Hello,
>
I'm hoping someone might be able to offer some guidance to my problem.
I have one query in MS Access which consists of 2 tables joined by a
SEQUENCE_ID. By joining the two tables I am able to produce a query
that gives me two fields, Part_Number and Product_Type. Below you can
see some sample data:
>
Part_Number Product_Type
10MC35231 XYZ1A
10MC35231 XYZ1B
23XB94142 XYZ1A
23XB94142 XYZ1B
23XB94142 XYZ1C
42PA76251 XYZ1D
>
Ideally, it would be nice for the query to have an additional field
which would concatenate the Product_Type's:
>
10MC35231 XYZ1A, XYZ1B
23XB94142 XYZ1A, XYZ1B, XYZ1C
42PA76251 XYZ1D
>
I found a few concatenate functions but it seemed that the either it
did not like that I was joining two tables in the query or that the
data was in only one table. I would really appreciate if someone could
help give me a direction with this.
>
Thanks in advance,
Aaron

Is there any way you could use a PivotTable/Crosstab query?

On Sep 4, 12:48*pm, Salad <o...@vinegar.comwrote:

Aaron Hoffman wrote:

>

>

>

>

>

>

>
Is there any way you could use a PivotTable/Crosstab query?- Hide quoted text -
>
- Show quoted text -

Thanks for your response. Actually I had the same idea. I created a
Crosstab query which gives me the following output with a count for
each appropriate Product_Type.

Part_Number XYZ1A XYZ1B XYZ1C XYZ1D
10MC35231 1 1
23XB94142 1 1 1
42PA76251 1

What I really need is the value of the count to be the Product_Type.

Part_Number XYZ1A XYZ1B XYZ1C XYZ1D
10MC35231 XYZ1A XYZ1B
23XB94142 XYZ1A XYZ1B XYZ1C XYZ1D
42PA76251 XYZ1D

If someone can figure out how I can replace the count with the field
name (Product_Type) then each of the fields could be concatenated
giving the result. I would really like to do this from within Access
and not have to manipulate manually in Excel. I'm not sure if this is
the best approach but I would appreciate any advice.

Thanks,
Aaron

On Sep 4, 11:22*am, Aaron Hoffman <bikefas...@gmail.comwrote:

Hello,
>
I'm hoping someone might be able to offer some guidance to my problem.
I have one query in MS Access which consists of 2 tables joined by a
SEQUENCE_ID. By joining the two tables I am able to produce a query
that gives me two fields, Part_Number and Product_Type. Below you can
see some sample data:
>
Part_Number * * * *Product_Type
10MC35231 * * * * XYZ1A
10MC35231 * * * * XYZ1B
23XB94142 * * * * *XYZ1A
23XB94142 * * * * *XYZ1B
23XB94142 * * * * *XYZ1C
42PA76251 * * * * *XYZ1D
>
Ideally, it would be nice for the query to have an additional field
which would concatenate the Product_Type's:
>
10MC35231 * * * * XYZ1A, XYZ1B
23XB94142 * * * * *XYZ1A, XYZ1B, XYZ1C
42PA76251 * * * * *XYZ1D
>
I found a few concatenate functions but it seemed that the either it
did not like that I was joining two tables in the query or that the
data was in only one table. I would really appreciate if someone could
help give me a direction with this.
>
Thanks in advance,
Aaron

Here's where I'd use a VBA function to retrieve all the product types
for a given part number and concatenate them all into a comma
delimited string. For example, your initial query would look
something like:

select distinct part_number, sequence_id,
prodtypes_get([sequence_id]) from tblParts

and your VBA function would look something like:

function prodtypes_get(varSeqID as variant) as variant

dim rst as DAO.recordset
dim varRtn as variant

set rst = dbengine(0)(0).openrecordset("select Product_type from
tblProductTypes where Sequence_ID = " & varSeqID)
do until rst.eof
varRtn = varRtn & rst!Product_type & ","
loop

if isnull(varRtn) then
prodtypes_get = varRtn
else
prodtypes_get = left(varRtn,len(varRtn)-1)
end if

end function

You'd probably want to spruce that up with some error handling, etc.
but this is the basic idea.

Bruce

'Fraid you are going to have to use a function.


Air code
Briefly it will look like
Function Concatenate(Part_Number as String) As String

Dim MyDb as database
Dim PartSet as Recordset
dim SQLStg as string
Dim ProductStg as String

SQLStg = "SELECT Part_Number, Product_Type FROM MyQuery " ' This is
the query that joins the 2 tables
SQLStg = SQLStg & "WHERE Part_Number = " & Part_Number & ";"
' That should give a query showing all the Product_Types for that
Part_Number

Set MyDb = CurrentDb
Set PartSet = MyDb.OpenRecordset(SQLStg)
With PartSet
Do Until .EOF
ProductStg = ProductStg & !Product_Type & "," ' Add the
Product_Types and a comma
.MoveNext
Loop
.Close
Set PartSet = Nothing
End With

Concatenate = Left(ProductStg, Len(ProductStg) -1) ' Remove
last Comma

Exit Function

Then in your original query (MyQuery) add a field
P_Types:Concatenate(Part_Number)

HTH

Phil
"Aaron Hoffman" <bikefaster@gmail.comwrote in message
news:8d16dc34-6fb2-4d1f-a6c4-4f0d9e6dee10@b30g2000prf.googlegroups.com...
On Sep 4, 12:48 pm, Salad <o...@vinegar.comwrote:

Aaron Hoffman wrote:

>

>

>

>

>

>

>
Is there any way you could use a PivotTable/Crosstab query?- Hide quoted
text -
>
- Show quoted text -

Thanks for your response. Actually I had the same idea. I created a
Crosstab query which gives me the following output with a count for
each appropriate Product_Type.

Part_Number XYZ1A XYZ1B XYZ1C XYZ1D
10MC35231 1 1
23XB94142 1 1 1
42PA76251 1

What I really need is the value of the count to be the Product_Type.

Part_Number XYZ1A XYZ1B XYZ1C XYZ1D
10MC35231 XYZ1A XYZ1B
23XB94142 XYZ1A XYZ1B XYZ1C XYZ1D
42PA76251 XYZ1D

If someone can figure out how I can replace the count with the field
name (Product_Type) then each of the fields could be concatenated
giving the result. I would really like to do this from within Access
and not have to manipulate manually in Excel. I'm not sure if this is
the best approach but I would appreciate any advice.

Thanks,
Aaron

On Sep 4, 7:44*pm, "Phil Stanton" <p...@myfamilyname.co.ukwrote:

'Fraid you are going to have to use a function.
>
Air code
Briefly it will look like
Function Concatenate(Part_Number as String) As String
>
* * Dim MyDb as database
* * Dim PartSet as Recordset
* * dim SQLStg as string
* * Dim ProductStg as String
>
* * SQLStg = "SELECT Part_Number, Product_Type FROM MyQuery " * *' This is
the query that joins the 2 tables
* * SQLStg = SQLStg & "WHERE Part_Number = " & Part_Number & ";"
' That should give a query showing all the Product_Types for that
Part_Number
>
* * Set MyDb = CurrentDb
* * Set PartSet = MyDb.OpenRecordset(SQLStg)
* * With PartSet
* * * * Do Until .EOF
* * * * * * ProductStg = ProductStg & !Product_Type & "," * * * *' Add the
Product_Types and a comma
* * * * * * .MoveNext
* * * * Loop
* * * * .Close
* * * * Set PartSet = Nothing
* * End With
>
* * Concatenate = Left(ProductStg, Len(ProductStg) -1) * * * * * *' Remove
last Comma
>
Exit Function
>
Then in your original query (MyQuery) add a field
P_Types:Concatenate(Part_Number)
>
HTH
>
Phil"Aaron Hoffman" <bikefas...@gmail.comwrote in message
>
news:8d16dc34-6fb2-4d1f-a6c4-4f0d9e6dee10@b30g2000prf.googlegroups.com...
On Sep 4, 12:48 pm, Salad <o...@vinegar.comwrote:
>
>
>
>
>

>

>

>

>

>

>

>

>

>
Thanks for your response. Actually I had the same idea. I created a
Crosstab query which gives me the following output with a count for
each appropriate Product_Type.
>
Part_Number * * *XYZ1A * XYZ1B * XYZ1C *XYZ1D
10MC35231 * * * * * 1 * * * * * *1
23XB94142 * * * * * *1 * * * * * *1 * * * * * 1
42PA76251 * * * * * * * * * * * * * * * ** * * * * * * * *1
>
What I really need is the value of the count to be the Product_Type.
>
Part_Number * * *XYZ1A * XYZ1B * XYZ1C *XYZ1D
10MC35231 * * * *XYZ1A * XYZ1B
23XB94142 * * * * XYZ1A * XYZ1B * XYZ1C *XYZ1D
42PA76251 * * * * * * * * * * * * * * * ** * * * * * *XYZ1D
>
If someone can figure out how I can replace the count with the field
name (Product_Type) then each of the fields could be concatenated
giving the result. I would really like to do this from within Access
and not have to manipulate manually in Excel. I'm not sure if this is
the best approach but I would appreciate any advice.
>
Thanks,
Aaron- Hide quoted text -
>
- Show quoted text -

I really appreciate your help with this. I'm just not exaclty sure
about when you assign SQLStg the Select statement. My current query
looks like the following:

SELECT tblPart.PART_NUMBER, tblPartModel. PRODUCT_TYPE
FROM tblPartModel INNER JOIN tblPart ON tblPartModel.PART_SEQ_ID =
tblPart.PART_SEQ_ID;
GROUP BY tblPart.PART_NUMBER, tblPartModel.PRODUCT_TYPE;

As you can see i'm joining these two tables by the PART_SEQ_ID and
then using group by to get only the unique records. Do I simply use my
exisiting query and replace MyQuery in your code with my table name?
Are there any changes that need to be made to this query?

I went ahead and added P_Types:Concatenate(Part_Number) into my
existing query and attempted to see the Datasheet View. I then
recieved a compile error saying, "Only comments may appear after End
Sub, End Function, or End Property." Thinking that the comments were
causing a problem, I removed them all from the code but this did not
fix the problem. Any assistance getting this to work would be greatly
appreciated.

Thanks for the help,
Aaron

Ok Aaron Lets take it step by step

save


SELECT tblPart.PART_NUMBER, tblPartModel. PRODUCT_TYPE
FROM tblPartModel INNER JOIN tblPart ON tblPartModel.PART_SEQ_ID =
tblPart.PART_SEQ_ID;
GROUP BY tblPart.PART_NUMBER, tblPartModel.PRODUCT_TYPE;

as My query. Can you see it in query design and does ir run OK

Phil
________________________-
"Aaron Hoffman" <bikefaster@gmail.comwrote in message
news:d6644eaf-7a7e-4939-8271-a1cd6471beed@l43g2000hsh.googlegroups.com...
On Sep 4, 7:44 pm, "Phil Stanton" <p...@myfamilyname.co.ukwrote:

'Fraid you are going to have to use a function.
>
Air code
Briefly it will look like
Function Concatenate(Part_Number as String) As String
>
Dim MyDb as database
Dim PartSet as Recordset
dim SQLStg as string
Dim ProductStg as String
>
SQLStg = "SELECT Part_Number, Product_Type FROM MyQuery " ' This is
the query that joins the 2 tables
SQLStg = SQLStg & "WHERE Part_Number = " & Part_Number & ";"
' That should give a query showing all the Product_Types for that
Part_Number
>
Set MyDb = CurrentDb
Set PartSet = MyDb.OpenRecordset(SQLStg)
With PartSet
Do Until .EOF
ProductStg = ProductStg & !Product_Type & "," ' Add the
Product_Types and a comma
.MoveNext
Loop
.Close
Set PartSet = Nothing
End With
>
Concatenate = Left(ProductStg, Len(ProductStg) -1) ' Remove
last Comma
>
Exit Function
>
Then in your original query (MyQuery) add a field
P_Types:Concatenate(Part_Number)
>
HTH
>
Phil"Aaron Hoffman" <bikefas...@gmail.comwrote in message
>
news:8d16dc34-6fb2-4d1f-a6c4-4f0d9e6dee10@b30g2000prf.googlegroups.com...
On Sep 4, 12:48 pm, Salad <o...@vinegar.comwrote:
>
>
>
>
>

>

>

>

>

>

>

>

>

>
Thanks for your response. Actually I had the same idea. I created a
Crosstab query which gives me the following output with a count for
each appropriate Product_Type.
>
Part_Number XYZ1A XYZ1B XYZ1C XYZ1D
10MC35231 1 1
23XB94142 1 1 1
42PA76251 1
>
What I really need is the value of the count to be the Product_Type.
>
Part_Number XYZ1A XYZ1B XYZ1C XYZ1D
10MC35231 XYZ1A XYZ1B
23XB94142 XYZ1A XYZ1B XYZ1C XYZ1D
42PA76251 XYZ1D
>
If someone can figure out how I can replace the count with the field
name (Product_Type) then each of the fields could be concatenated
giving the result. I would really like to do this from within Access
and not have to manipulate manually in Excel. I'm not sure if this is
the best approach but I would appreciate any advice.
>
Thanks,
Aaron- Hide quoted text -
>
- Show quoted text -

I really appreciate your help with this. I'm just not exaclty sure
about when you assign SQLStg the Select statement. My current query
looks like the following:

SELECT tblPart.PART_NUMBER, tblPartModel. PRODUCT_TYPE
FROM tblPartModel INNER JOIN tblPart ON tblPartModel.PART_SEQ_ID =
tblPart.PART_SEQ_ID;
GROUP BY tblPart.PART_NUMBER, tblPartModel.PRODUCT_TYPE;

As you can see i'm joining these two tables by the PART_SEQ_ID and
then using group by to get only the unique records. Do I simply use my
exisiting query and replace MyQuery in your code with my table name?
Are there any changes that need to be made to this query?

I went ahead and added P_Types:Concatenate(Part_Number) into my
existing query and attempted to see the Datasheet View. I then
recieved a compile error saying, "Only comments may appear after End
Sub, End Function, or End Property." Thinking that the comments were
causing a problem, I removed them all from the code but this did not
fix the problem. Any assistance getting this to work would be greatly
appreciated.

Thanks for the help,
Aaron

Sorry, the last line should be "End Function", not "Exit Function"

Phil

"Aaron Hoffman" <bikefaster@gmail.comwrote in message
news:d6644eaf-7a7e-4939-8271-a1cd6471beed@l43g2000hsh.googlegroups.com...
On Sep 4, 7:44 pm, "Phil Stanton" <p...@myfamilyname.co.ukwrote:

'Fraid you are going to have to use a function.
>
Air code
Briefly it will look like
Function Concatenate(Part_Number as String) As String
>
Dim MyDb as database
Dim PartSet as Recordset
dim SQLStg as string
Dim ProductStg as String
>
SQLStg = "SELECT Part_Number, Product_Type FROM MyQuery " ' This is
the query that joins the 2 tables
SQLStg = SQLStg & "WHERE Part_Number = " & Part_Number & ";"
' That should give a query showing all the Product_Types for that
Part_Number
>
Set MyDb = CurrentDb
Set PartSet = MyDb.OpenRecordset(SQLStg)
With PartSet
Do Until .EOF
ProductStg = ProductStg & !Product_Type & "," ' Add the
Product_Types and a comma
.MoveNext
Loop
.Close
Set PartSet = Nothing
End With
>
Concatenate = Left(ProductStg, Len(ProductStg) -1) ' Remove
last Comma
>
Exit Function
>
Then in your original query (MyQuery) add a field
P_Types:Concatenate(Part_Number)
>
HTH
>
Phil"Aaron Hoffman" <bikefas...@gmail.comwrote in message
>
news:8d16dc34-6fb2-4d1f-a6c4-4f0d9e6dee10@b30g2000prf.googlegroups.com...
On Sep 4, 12:48 pm, Salad <o...@vinegar.comwrote:
>
>
>
>
>

>

>

>

>

>

>

>

>

>
Thanks for your response. Actually I had the same idea. I created a
Crosstab query which gives me the following output with a count for
each appropriate Product_Type.
>
Part_Number XYZ1A XYZ1B XYZ1C XYZ1D
10MC35231 1 1
23XB94142 1 1 1
42PA76251 1
>
What I really need is the value of the count to be the Product_Type.
>
Part_Number XYZ1A XYZ1B XYZ1C XYZ1D
10MC35231 XYZ1A XYZ1B
23XB94142 XYZ1A XYZ1B XYZ1C XYZ1D
42PA76251 XYZ1D
>
If someone can figure out how I can replace the count with the field
name (Product_Type) then each of the fields could be concatenated
giving the result. I would really like to do this from within Access
and not have to manipulate manually in Excel. I'm not sure if this is
the best approach but I would appreciate any advice.
>
Thanks,
Aaron- Hide quoted text -
>
- Show quoted text -

I really appreciate your help with this. I'm just not exaclty sure
about when you assign SQLStg the Select statement. My current query
looks like the following:

SELECT tblPart.PART_NUMBER, tblPartModel. PRODUCT_TYPE
FROM tblPartModel INNER JOIN tblPart ON tblPartModel.PART_SEQ_ID =
tblPart.PART_SEQ_ID;
GROUP BY tblPart.PART_NUMBER, tblPartModel.PRODUCT_TYPE;

As you can see i'm joining these two tables by the PART_SEQ_ID and
then using group by to get only the unique records. Do I simply use my
exisiting query and replace MyQuery in your code with my table name?
Are there any changes that need to be made to this query?

I went ahead and added P_Types:Concatenate(Part_Number) into my
existing query and attempted to see the Datasheet View. I then
recieved a compile error saying, "Only comments may appear after End
Sub, End Function, or End Property." Thinking that the comments were
causing a problem, I removed them all from the code but this did not
fix the problem. Any assistance getting this to work would be greatly
appreciated.

Thanks for the help,
Aaron

On Sep 5, 12:24*pm, "Phil Stanton" <p...@myfamilyname.co.ukwrote:

Sorry, the last line should be "End Function", not "Exit Function"
>
Phil
>
"Aaron Hoffman" <bikefas...@gmail.comwrote in message
>
news:d6644eaf-7a7e-4939-8271-a1cd6471beed@l43g2000hsh.googlegroups.com...
On Sep 4, 7:44 pm, "Phil Stanton" <p...@myfamilyname.co.ukwrote:
>
>
>
>
>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>
I really appreciate your help with this. I'm just not exaclty sure
about when you assign SQLStg the Select statement. My current query
looks like the following:
>
SELECT tblPart.PART_NUMBER, tblPartModel. PRODUCT_TYPE
FROM tblPartModel INNER JOIN tblPart ON tblPartModel.PART_SEQ_ID =
tblPart.PART_SEQ_ID;
GROUP BY tblPart.PART_NUMBER, tblPartModel.PRODUCT_TYPE;
>
As you can see i'm joining these two tables by the PART_SEQ_ID and
then using group by to get only the unique records. Do I simply use my
exisiting query and replace MyQuery in your code with my table name?
Are there any changes that need to be made to this query?
>
I went ahead and added P_Types:Concatenate(Part_Number) into my
existing query and attempted to see the Datasheet View. I then
recieved a compile error saying, "Only comments may appear after End
Sub, End Function, or End Property." Thinking that the comments were
causing a problem, I removed them all from the code but this did not
fix the problem. Any assistance getting this to work would be greatly
appreciated.
>
Thanks for the help,
Aaron- Hide quoted text -
>
- Show quoted text -

Ok, I changed the last line to "End Function."

The query runs fine before I put "P_Types:Concatenate(Part_Number)"
into the query. Once I try to view the Datasheet, I still get the same
compile error. What am I doing wrong?

Ok If the query is OK then the problem is in the function

I presume this is saved in Module1 (or some other name)

Have you compiled it as this should show the errors.

What version of Access are you using?

Phil

"Aaron Hoffman" <bikefaster@gmail.comwrote in message
news:6ca4df56-bdb9-47e9-9d55-4b5c4eff1aaf@p25g2000hsf.googlegroups.com...
On Sep 5, 12:24 pm, "Phil Stanton" <p...@myfamilyname.co.ukwrote:

Sorry, the last line should be "End Function", not "Exit Function"
>
Phil
>
"Aaron Hoffman" <bikefas...@gmail.comwrote in message
>
news:d6644eaf-7a7e-4939-8271-a1cd6471beed@l43g2000hsh.googlegroups.com...
On Sep 4, 7:44 pm, "Phil Stanton" <p...@myfamilyname.co.ukwrote:
>
>
>
>
>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>
I really appreciate your help with this. I'm just not exaclty sure
about when you assign SQLStg the Select statement. My current query
looks like the following:
>
SELECT tblPart.PART_NUMBER, tblPartModel. PRODUCT_TYPE
FROM tblPartModel INNER JOIN tblPart ON tblPartModel.PART_SEQ_ID =
tblPart.PART_SEQ_ID;
GROUP BY tblPart.PART_NUMBER, tblPartModel.PRODUCT_TYPE;
>
As you can see i'm joining these two tables by the PART_SEQ_ID and
then using group by to get only the unique records. Do I simply use my
exisiting query and replace MyQuery in your code with my table name?
Are there any changes that need to be made to this query?
>
I went ahead and added P_Types:Concatenate(Part_Number) into my
existing query and attempted to see the Datasheet View. I then
recieved a compile error saying, "Only comments may appear after End
Sub, End Function, or End Property." Thinking that the comments were
causing a problem, I removed them all from the code but this did not
fix the problem. Any assistance getting this to work would be greatly
appreciated.
>
Thanks for the help,
Aaron- Hide quoted text -
>
- Show quoted text -

Ok, I changed the last line to "End Function."

The query runs fine before I put "P_Types:Concatenate(Part_Number)"
into the query. Once I try to view the Datasheet, I still get the same
compile error. What am I doing wrong?

On Sep 5, 2:36*pm, "Phil Stanton" <p...@myfamilyname.co.ukwrote:

Ok If the query is OK then the problem is in the function
>
I presume this is saved in Module1 (or some other name)
>
Have you compiled it as this should show the errors.
>
What version of Access are you using?
>
Phil
>
"Aaron Hoffman" <bikefas...@gmail.comwrote in message
>
news:6ca4df56-bdb9-47e9-9d55-4b5c4eff1aaf@p25g2000hsf.googlegroups.com...
On Sep 5, 12:24 pm, "Phil Stanton" <p...@myfamilyname.co.ukwrote:
>
>
>
>
>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>
Ok, I changed the last line to "End Function."
>
The query runs fine before I put "P_Types:Concatenate(Part_Number)"
into the query. Once I try to view the Datasheet, I still get the same
compile error. What am I doing wrong?- Hide quoted text -
>
- Show quoted text -

That is correct. It is saved as Module1.

When I try to compile all it says is "Compile error: Only comments may
appear after End Sub, End Function, or End Property."

I'm using Access 2000 (9.0.8968 SP-3).

I don't know if this matters but like I said the Query works fine
without "P_Types: Concatenate([Part_Number])". When I add this into
field in the Query Design view it automatically makes it "Group By"
because the other fields (PART_NUMBER) and (PRODUCT_TYPE) are grouped.
I thought I would mention this in the event that it is causing
problems.

Thanks,
Aaron