Putting multiple records into one row

[Ddempsii]

Hello all,

I have a query running to pull names, dates, and a calculated Test score field. The issue I am having is getting this:
_________________________


|Name |Date |Test Score|
|XXXX |xxxx |85 |
|XXXX |xxxx |90 |
|XXXX |xxxx |70 |
|XXXX |xxxx |85 |

To look like this:
________________________________________
|Name |Date |Test 1|Test 2|Test 3|Test 4|
|XXXX |xxxx |85 |90 |70 |85 |

On my report.

I have read a lot of different solutions to other people's problems with this. It seems very complex with SQL code. I would follow most of it but they all had their "test score" field already in a table. Mine doesn't populate into a table, it's a long IIf statement saying if question 1 = yes then "x" points. It looks like the best answer is to somehow make a "counter" to mark each different test in each month by the same person. Hoping this makes sense. Please let me know if there are questions.

[June7]

Yes, this requires a field that assigns sequential counter for each group of records. A query can calculate this 'ranking' but the result is not usable in subsequent queries - review http://allenbrowne.com/ranking.html

A DCount() expression might be able to generate the counter but domain aggregates can cause slow performance.

Alternatives:

1. VBA code that writes records to a 'temp' table

2. VBA code that concatenates data to a single string - review http://allenbrowne.com/func-concat.html


The calculated field should work but instead of a long IIf expression, maybe you should use a lookup table of the points for each question. Then include this table in query to make the points data available as a native field, not calculated.

[Ddempsii]

Thank you for the suggestions, you helped me last time with an issue thanks again, I'll check these out in a little while and get back to you

[Ddempsii]

June7 I'm in the weeds here, trying everything and nothing seems to work. Thank you for the help though

[Ddempsii]

Solved Thank you again