Convert table/query values to fields in a single record in access 2007

[MacroScope]

I have a query which displays the names of sales reps in order of sales productivity in the Rep field. I want to put all of those names into one record, so that name 1 would go to the Rep1 field, name 2 to Rep2 field, and so on. That way I can base a form on that table/query and be able to display them all on one form, in the order of productivity.

I know that I could use a subform which would create the required display without any conversion, but because of other calculations I want to do based on those names it is an impractical solution.

If there is a better way to accomplish this of course I'm open to it, as long as my goal of getting the values from multiple table/query records to display on one record is attained.

All assistance would be appreciated.

 

[MacroScope]

Thanks. I had heard it but could find no reference to it.

I am doing what I'm doing pro-bono. This company has spent a lot of money over the last six months and they have a user friendly sales database that that does everything they ever hoped it would do, and much that they never expected.

They have informed me that they're about done for the year for financial reasons, but the owner did want to get this routine operating at some time in the future. I told him that I'd get the list populated in the correct order for each vehicle type for him as a way of giving something back, and that he can pay me for actually completing and implementing it which is pretty straightforward but still will take several hours to accomplish. It was a real struggle to get it working, and I spent a lot of my own time getting it right.

While I know the solution I came up with is nowhere near as clean as it could be, I'm nonetheless confident that it's absolutely sound. It's a simple DLookup expression which is forced down one record for each lookup until I get all the way through the list. The expressions are properly evaluated every time, and it will always return what I want to see, even though it's done in the controls. For that reason I'm probably not going to do any more just to make it nicer, yet who knows? I may anyway, since it's in my nature.

Regardless I do thank you for your time and help, and I have another question. If I get the concatenate to work correctly, what's the best way to convert the delimited text to column headings? How time consuming that task is will probably bear heavily on whether I want to put any more effort into this.

 

[dhookom]

I don't think you want to use the Concatenate() function to create a comma-delimited string just to have to undelimit it again. There are some parse type functions that can take a multi-part expression and return the individual items.

Duane
Hook'D on Access
MS Access MVP