Convert table/query values to fields in a single record in Access 07

[bcmarshall]

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 one field of multiple records in a table/query to display as multiple fields on one record is attained.

All assistance would be appreciated.

[RuralGuy]

You do understand that you are trying to flatten out what seems to be a properly Normalized db right? Have you used or looked at Pivot tables yet?

[bcmarshall]

I'm unfamiliar with pivot tables. Can you explain?

[RuralGuy]

Maybe this link will help: http://office.microsoft.com/en-us/ac...006252666.aspx

[bcmarshall]

I looked at the pivot table info but I don't see how it can be used to accomplish what I want to do. If you know how I'd appreciate advice.

I know there is a function in Excel called transpose that allows such a thing to be done. I guess I could export to Excel, transpose there and re-import the transposed data back to Access, but that is a very awkward way. I am hoping there's a way to do this within Access.

[RuralGuy]

I do not use them but Access SQL honors the Transpose command.

[bcmarshall]

I would really like to know how to use the transpose command in Access. I even did a search on Access help and found no reference to it. If you know the appropriate syntax I'd like to see it.

I actually did solve my own problem. It's not very elegant, but it works. I created unbound controls and used an expression for the Control Source. Here's the last expression I used for Rep20. You can see that it would be shorter for each earlier control.

=DLookUp("[RepOrder]","[Reps Sorted by RV Binder]","[RepOrder]<>[Rep1]" & "And [RepOrder]<>[Rep2]" & "And [RepOrder]<>[Rep3]" & "And [RepOrder]<>[Rep4]" & "And [RepOrder]<>[Rep5]" & "And [RepOrder]<>[Rep6]" & "And [RepOrder]<>[Rep7]" & "And [RepOrder]<>[Rep8]" & "And [RepOrder]<>[Rep9]" & "And [RepOrder]<>[Rep10]" & "And [RepOrder]<>[Rep11]" & "And [RepOrder]<>[Rep12]" & "And [RepOrder]<>[Rep13]" & "And [RepOrder]<>[Rep14]" & "And [RepOrder]<>[Rep15]" & "And [RepOrder]<>[Rep16]" & "And [RepOrder]<>[Rep17]" & "And [RepOrder]<>[Rep18]" & "And [RepOrder]<>[Rep19]")

As I said, it's not pretty, but it definitely works. I'd much rather use transpose if I could find out more about it.

Thanks for your help.

[RuralGuy]

It is an SQL command and not Assess but Access can implement it. Maybe this will help: http://www.microsoft.com/downloads/e...displaylang=en