Concatenating rows & records

[Alex Motilal]

Hi everybody,
I do have a database in which yarn prices are fixed for yarn counts of different units. I have the following table structures:
1) tblUnits – Fields: UnitID (PK), UnitName (eg. A, B, C ….)
2) tblCounts – Fields: CountID(PK), ContName (eg. 60S, 80S, 100S…….)


3) tblYarnPricingMeeting – Fields: MeetingID (PK), Meeting# (Text), MeetingDate, CountID(CK), UnitID(CK), RateFixed (Currency)
All the units say A, B & C (UnitID: 1,2,3)may run 100S count(CountID: 1). In the pricing meeting, eg. meeting # 001 (MeetingID: 101), prices for different units for a particular count may be same or differ. For MeetingID: 101 the tblYarnPricingMeeting is as follows:
MeetingID
Meeting#
MeetingDate
CountID
UnitID
RateFixed
101
101
22-Aug-2011
1
1
850
101
101
22-Aug-2011
1
2
850
101
101
22-Aug-2011
1
3
900

In the Underlying Query for report, the Units should be concatenated meeting wise, count wise rate wise as follows:
MeetingID
Meeting#
MeetingDate
CountName
UnitName
RateFixed
101
101
22-Aug-2011
100S
A, B
850
101
101
22-Aug-2011
100S
C
900

Is it possible? There is a solution in bluclaw-db.com. However, it is related to one ID & not for multiple ID.


Alex

[Alex Motilal]

I am rephrasing the data as follows

tblYarnPricingMeeting
MeetingID Meeting# MeetingDate CountID UnitID RateFixed
101 101 22-Aug-2011 1 1 850
101 101 22-Aug-2011 1 2 850
101 101 22-Aug-2011 1 3 900

Resultant Query
MeetingID Meeting# MeetingDate CountName UnitName RateFixed
101 101 22-Aug-2011 100S A, B 850
101 101 22-Aug-2011 100S C 900

Alex

[rpeare]

You can do this in a query but it'll be an ugly formula, it would have to have every possible unitID in the formula and I don't how possible that is with your dataset, I think I'd be more inclined to just use a crosstab query.

First step would be this:

SELECT YP.MeetingID, YP.MeetingNum, YP.MeetingDate, YP.CountID, tblUnits.UnitName, YP.RateFixed
FROM tblYarnPricingMeeting AS YP LEFT JOIN tblUnits ON YP.UnitID = tblUnits.UnitID;

Save this as QUERY1 (NOTE I changed your meeting# to meetingnum, using special characters in field names is a bad practice and will only cause problems with your coding)

next make this query

TRANSFORM Count(Query1.CountID) AS CountOfCountID
SELECT Query1.MeetingNum, Query1.MeetingDate, Query1.RateFixed
FROM Query1
GROUP BY Query1.MeetingNum, Query1.MeetingDate, Query1.RateFixed
PIVOT Query1.UnitName;

It will list rows for every unit type you have which may be more useful to you than having it concantentated. If you really must have it concantenated then you would write a new query based on the crosstab query (the second query) that would contcantenate the fields.

I've never found a reason to do this type of concantenation so someone may have a sleeker solution