Stringing Data Horizontally

[Kellen]

I need to create a query that strings data horizontally that corresponds to the primary key.

Example of what I am looking for

Fields:
Part_No,
Mfg_No,
Mfg_Name

Query Columns: Part_No, Mfg_Info (Mfg_No: Mfg_Name)

Also if the part_no (primary key) has multiple values I need the data to string horizontally like this:

Part_No, Mfg_Info (Mfg_No1: Mfg_Name1),
Mfg_Info (Mfg_No2: Mfg_Name2).......

Sorry if this isn't all too clear.

Thanks in advance.

 

[vbaInet]

You could make it clearer by uploading an Excel file that represents the before (how it currently is) and after (how you want it to be) of your table.

Or look into a Crosstab Query and see if that's what you're after.

 

[jdraw]

See Allen Browne's func-concat

 

[Kellen]

You could make it clearer by uploading an Excel file that represents the before (how it currently is) and after (how you want it to be) of your table.

Or look into a Crosstab Query and see if that's what you're after.

I uploaded an example of what I wanted.

I thought a crosstab query would work but, I get an error stating I that I created too many headers.

 

[vbaInet]

Can you include data in the spreadsheet that shows the 'before' spreadsheet as previously requested. What we want to see in one spreadsheet is before and after.

 

[Kellen]

Can you include data in the spreadsheet that shows the 'before' spreadsheet as previously requested. What we want to see in one spreadsheet is before and after.

Sorry here it is.

 

[vbaInet]

Ok that makes more sense. It's a crosstab query you still need but first you need to concatenate "Mfg_No" and "Mfg_Name" like this "Mfg_No" & "Mfg_Name". From there you can use this field to in your crosstab.

 

[Kellen]

Ok that makes more sense. It's a crosstab query you still need but first you need to concatenate "Mfg_No" and "Mfg_Name" like this "Mfg_No" & "Mfg_Name". From there you can use this field to in your crosstab.

I can't do that. I don't have enough columns and when I added one just to make it work it gives the previous error of too many headers.

 

[vbaInet]

See attached.

If your numbering is different then you need to use the Concat solution jdraw provided.

 

[Kellen]

See attached.

If your numbering is different then you need to use the Concat solution jdraw provided.

It doesn't work. It says that Access doesn't recognize A.[ID] as a valid field name.

 

[vbaInet]

What doesn't work? The file I gave you works so you need to be more specific.

 

[Kellen]

What doesn't work? The file I gave you works so you need to be more specific.

I cannot run the cross tab query. It says that Access doesn't recognize A.[ID] as a valid field name.

 

[vbaInet]

I'm still not sure whether you're talking about my example db or your own db?

 

[Kellen]

I'm still not sure whether you're talking about my example db or your own db?

I'm referring to to your db.

 

[vbaInet]

Oh I see. It's the subquery problem being referred from a Crosstab query. I initially used a DCount function and converted it to a subquery but didn't retest

See attached.

 

[Kellen]

Okay I'll see if I can get it to work with my actual query.

 

[Kellen]

Okay everything works on Access, however it turns out that I also need to replicate this query on MS SQL server. DCount isn't a function on SQL server and the Count function only supports one argument.

How can I convert this line into MS SQL server?

Mfg_Combined" & DCount("*", "tblRecords", "ID <= " & [ID] & " AND [Part_No] = '" & [Part_No] & "'") AS Mfg_Header

 

[vbaInet]

You want to ask this question in an appropriate SQL Server forum.

In any case, you could look into the Row_Number() function.