Using multiple values from the same field in an expression

[AW3995]

Hi folks,



Long time listener, first time caller. I have a field with about a dozen records. I want to use the values of these records like so: expression1: [record1] & [record2] & [record3]. I know how to do this with multiple fields, but not with values from the same field.

A simplier solution might also be to somehow append the different values to their own fields and just write the expression like normal, but I don't know how to do that either. Example:

Field1:
RecordA
RecordB
RecordC
RecordD

If I could somehow turn it into this:

Field1:
RecordA

Field2:
RecordB

Field3:
RecordC

Field4:
Record D

Thanks in advance for your help. I know there has to be an easy way to do this, but it has so far escaped me.

[June7]

If a CROSSTAB will not achieve what you want, then

expression1: [record1] & [record2] & [record3]

can be achieved with http://allenbrowne.com/func-concat.html

How many different values are possible in the field? Maybe:

SELECT [some field to group by], Max(IIf([fieldname]="value1", [fieldname], Null)) AS Field1, Max(IIf([fieldname]="value2", [fieldname], Null)) AS Field2, Max(IIf([fieldname]="value3", [fieldname], Null)) AS Field3, Max(IIf([fieldname]="value4", [fieldname], Null)) AS Field4 GROUP BY [some field to group by];

[AW3995]

If a CROSSTAB will not achieve what you want, then

expression1: [record1] & [record2] & [record3]

can be achieved with http://allenbrowne.com/func-concat.html

How many different values are possible in the field? Maybe:

SELECT [some field to group by], Max(IIf([fieldname]="value1", [fieldname], Null)) AS Field1, Max(IIf([fieldname]="value2", [fieldname], Null)) AS Field2, Max(IIf([fieldname]="value3", [fieldname], Null)) AS Field3, Max(IIf([fieldname]="value4", [fieldname], Null)) AS Field4 GROUP BY [some field to group by];

The code on that website scares me.. it looks too easy to mess up. I'll give it a shot later on and report back with how I did. Maybe there is a way to do it in crosstab, but I haven't figured out how. Perhaps someone could point me to an example?

Also, I have no idea what the values will be so I can't use your example above. I did consider something like that.

[June7]

If data were like:

ID	GrpID	Data	Seq
1	4	abc	1
2	2	def	2
3	2	ghi	1
4	4	jkl	1
5	3	mno	2
6	2	pqr	3
7	1	stu	1

The Seq field could be use as the column header criteria in CROSSTAB. Use First() function on the Data field. The resulting output would be:

GrpID	1	2	3
1	stu
2	def	ghi	pqr
3	mno
4	abc	jkl

The only other option I know is VBA code writing records to temp table.

[rpeare]

If you have an upper limit of 12 items (RECORDA - RECORDD in your original post) per group you might be able to get away with a cartesian query made into a crosstab query as june suggests. If your data is NOT limited to a very select list for your 12 fields (as in June's example) it would not work. In other words if you are going to have a maximum of 12 records, and there are only 12 possible values that can't be duplicated within a group of your data, you're in business.

[AW3995]

If data were like:

ID	GrpID	Data	Seq
1	4	abc	1
2	2	def	2
3	2	ghi	1
4	4	jkl	1
5	3	mno	2
6	2	pqr	3
7	1	stu	1

The Seq field could be use as the column header criteria in CROSSTAB. Use First() function on the Data field. The resulting output would be:

GrpID	1	2	3
1	stu
2	def	ghi	pqr
3	mno
4	abc	jkl

The only other option I know is VBA code writing records to temp table.

I tried this, but I have two problems: I only have two fields of info and if I use First it will take out duplicates (which I need). Like I said, I played around for a long time with crosstab, and while I can get very close I just can't get it to do exactly what I want. I'm about to try the code and see how that does.