Access Query Question

[BlueHorizon]

Hi all,

I created a query that returned the following information.

Part# Year Make Model
7133138 2000 Malarky SSP-R750
7133138 2001 Malarky SSP-R750
7133138 2002 Malarky SSP-R750
7133138 2003 Malarky SSP-R750
7343243 1978 Wuzzits BP250
7343243 1979 Wuzzits BP250
7343243 1979 Wuzzits BLP250
7343243 1979 Wuzzits BDP400
7343243 1980 Wuzzits BLP250
7343243 1980 Wuzzits BP125
7343245 1980 Wuzzits BDP250
7343245 1980 Wuzzits BP250
7343245 1980 Wuzzits BDP400
7343245 1981 Wuzzits BP250
7343245 1981 Wuzzits BP125

The result I'm trying to get now is to combine (concatenate) the Part, Year, Make, Model spearated by a comma. HOWEVER, I don't want the Part# to repeat on each line. In other words, the result needed on a single line is:

7133138: 2000 Malarky SSP-R750, 2001 Malarky SSP-R750, 2002 Malarky SSP-R750, 2003 Malarky SSP-R750

Any help would be greatly appreciated - TIA!!

Best,
Blue Horizon

 

[dhookom]

How far have you gotten with this? Did you search the FAQs for this forum?

Duane
Hook'D on Access
MS Access MVP

 

[BlueHorizon]

I found/read this FAQ:

Concatentating a one to many relationship ino a single column
faq701-3499

But I don't know code so I don't know how to tweak this to fit my needs. I was hoping I wouldn't need code.

Thanks,

Best,
Blue Horizon

 

[lameid]

You need code to do what you are trying to accomplish... I have to run in a moment or I might take a stab at the code...

To avoid code you will have to present the data differntly so are there underlying goals you are trying to meet that this solves the problem for? Or do you simply need this solution for some reason?

 

[BlueHorizon]

Thanks for responding...

Yes, the underlying goal is to be able to export this "concatenated" data to Excel so that "flat data" can be uploaded to Amazon's website. This is for a client who is an Amazon seller.

Any help besides code?



Best,
Blue Horizon

 

[dhookom]

Jump into the code.
Open a new, blank module and paste the code from the FAQ into the module window. Then save the module with the name "modStringFunctions".

You can then create a query with a column that uses the function. THis assumes the Part# field is numeric and your un-named query is YourQueryName.

SELECT [Part#], Concatenate("SELECT Year & ' ' & Make & ' ' & Model FROM YourQueryName WHERE [Part#] = " & [Part#]) as Combined
FROM YourQueryName 
GROUP BY [Part#], Concatenate("SELECT Year & ' ' & Make & ' ' & Model FROM YourQueryName WHERE [Part#] = " & [Part#]);

Duane
Hook'D on Access
MS Access MVP

 

[BlueHorizon]

Thanks dhookam!

I'm really uneducated when it comes to code. So here's what I did. I copied and pasted the FAQ code and named it "modStringFunctions", as instructed. Then I created a query with the SQL code you provided. I replaced Year with the field name, replaced Make with the field name, etc.

The message I'm getting when I run the query is
Undefined function 'Concatenate' in expression

?? Don't know what to do now.....

Best,
Blue Horizon

 

[dhookom]

Where did you paste the code?
You can download a working example from Rogers Access Library

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Bluehorizon,

I can infer that Duane thinks the FAQ you linked is to a different FAQ.

To get his solution to work, forget about pasting the code from the FAQ (or even delete it).

Next download the file from the site he has listed. Import the module basConcatenate from that file.

That should make the example he posted functional....

However I expect you will have to change his SQL code posted for the query slightly to get the desired result (hint: it puts spaces between items where you want commas).

 

[dhookom]

Lameid is correct. I was thinking about faq701-4233 which matches closely with the other file download I suggested. The code that I suggested with put spaces between fields from the same record but will put commas between different child records.

Duane
Hook'D on Access
MS Access MVP

 

[BlueHorizon]

Here's the code I used changed to match the fields in my table.

SELECT [Partid], Concatenate("SELECT YEARS & ' ' & MAKES & ' ' & MODELS FROM tbl_NEW_Part_Year_Make_Model WHERE [Partid] = " & [Partid]) as Combined
FROM tbl_NEW_Part_Year_Make_Model
GROUP BY [Partid], Concatenate("SELECT YEARS & ' ' & MAKES & ' ' & MODELS FROM tbl_NEW_Part_Year_Make_Model WHERE [Partid] = " & [Partid]);

Here's the error message I get:
The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and exclose the subquery in parentheses.

Any ideas how I can fix it?



Best,
Blue Horizon

 

[dhookom]

Are your field names actually YEARS, MAKES, and MODELS?
Is PartID numeric or text/string?

You can test the code by opening the debug window (press Ctrl+G) and entering:

?Concatenate("SELECT YEARS & ' ' & MAKES & ' ' & MODELS FROM tbl_NEW_Part_Year_Make_Model WHERE [Partid] = 7133138")

or if PartID is text

?Concatenate("SELECT YEARS & ' ' & MAKES & ' ' & MODELS FROM tbl_NEW_Part_Year_Make_Model WHERE [Partid] = '7133138'")

Duane
Hook'D on Access
MS Access MVP