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.

 

[randy700]

Look in the FAQ section.
This may be of use to you:


How to concatenate multiple child records into a single value


Randy

 

[MacroScope]

Thanks. I used the SQL in a query, and got the message, "Undefined Function 'Concatenate' in Expression". Any ideas why?

 

[dhookom]

You need to add the function code to a standard module in your Access database file. Make sure the name of the module is not the same as the name of the function.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

Yeah, Duh! I realized after I sent the post! I am using DAO, and pasted the code as follows.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ", _
Optional pstrLastDelim As String = "") _
As Variant

' Created by Duane Hookom, 2003
' this code may be included in any application/mdb providing
' this statement is left intact
' example
' tblFamily with FamID as numeric primary key
' tblFamMem with FamID, FirstName, DOB,...
' return a comma separated list of FirstNames
' for a FamID
' John, Mary, Susan
'======= in a Query =========================
' SELECT FamID,
' Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
' FROM tblFamily
' ============================================
' to get a return like Duane, Laura, Jake, and Chelsey
' ======= in a Query =========================
' SELECT FamID,
' Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID], ",",", and ") as FirstNames
' FROM tblFamily' ============================================
' If FamID is a string rather than numeric,
' it will need to be delimited with quotes
' ======= in a Query =========================
' SELECT FamID,
' Concatenate("SELECT FirstName FROM tblFamMem
'WHERE FamID =""" & [FamID] & """", ",",", and ") as FirstNames
' FROM tblFamily
' ============================================
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)
Dim intLenB4Last As Integer

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
intLenB4Last = Len(strConcat)
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
If Len(pstrLastDelim) > 0 Then
strConcat = Left(strConcat, _
intLenB4Last - Len(pstrDelim) - 1) _
& pstrLastDelim & Mid(strConcat, intLenB4Last + 1)
End If
End If
If Len(strConcat) > 0 Then
Concatenate = strConcat
Else
Concatenate = Null
End If

End Function

It is no longer failing on calling the function, but I suspect that I got some line mixed up between DAO and ADO. The query just returns two empty fields. The query SQL reads:

SELECT SalesRep AS Expr1, Concatenate("SELECT RepOrder FROM RepsByRV WHERE RepOrder =""" & [RepOrder] & """",",",", and ") AS SalesRep
FROM RepsByRV;


Help sorting it out would be appreciated.

 

[dhookom]

The code works for me however I took out the "-1" from the line

intLenB4Last - Len(pstrDelim) [b][red]- 1[/red][/b]) _

Your expression seems flawed. It will return RepOrder values from RepsByRV where the RepOrder equals the RepOrder value. This seems quite circular to me.

Can you explain your significant tables, fields, and data types. How about provide 3-4 sample records and the desired output?


Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

The database is for an insurance company that specializes in toys, and so is sorted by category into Boat, Motorcycle, RV, and Motor Home vehicle groups. There are queries already written which show the performance of various reps in order for each vehicle. It was a simple matter of a descending sort by the Sumof field to accomplish that.

I wanted to create a single form that would display all four vehicle total sales and rep performances at once. It would have 20 fields labeled Rep1-Rep20, which would be linked to the query outputting the concatenated values I hope to derive from the code. The table RepsbyRV contains only one output field, Rep, which contains the ordered names of all the sales reps by performance with RV insurance. There are three other tables, RepsByBoat, RepsByMotorcycle, and RepsByMH similarly configured with one output field.

And no, it's not an error. The ordered values are in a table created from append queries because I could not get all the rep names to display in a single query. It would only display the names of those who had sales or activity, and no Join configuration would display what I wanted to see. The Rep field is set to No Duplicates, and first the values of the performing reps are appended in order. Then another append query runs which appends the names of all reps. Since it's set to No Duplicates, the only names added to the table are those who were not represented in the first set.

I had used the "Rep" name originally in all fields until I ran into the circualar reference problem myself. I then renamed the table field containing the ordered reps as RepOrder.

Ultimately, once I get all of the reps in order in one query record, I will display those names in the earlier mentioned text boxes by linking the form to the query. Then, I will have a bunch of unbound controls on the form which use expressions to pull the figures from queries that already generate the totals by the name in the Rep1-Rep20 field.

To try to summarize, there will be 20 fields for rep names, and to the right of each of those names will be 12 calculated fields, each pulling specific data from various queries based on the name in the field. The last part of all of this is that I intend to switch the RecordSource of the form by CommandButtons so that the order of Rep names will change by which of the four vehicle types is selected, and of course, being calculated fields based on the name field, all the appropriate numbers will follow.

I'm not sure if this description is enough but if it is not, please let me know what you need to see and I will send it.

 

[MacroScope]

My code is now failing with the debugger pointing to the 4th line, Set rs = db.OpenRecordset(pstrSQL), and the error message stating Too few parameters. Expected 1.

Any suggestions?

 

[dhookom]

I think you want more than just a text string with multiple values separated by a comma. If so, the Concatenate() function is not going to help.

Have you considered creating a crosstab query?

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

Thanks, and you're right. I do think I need more than concatenation, although I can probably use the CSV values to create what I need. I almost jumped for joy when I read your suggestion. Of course! A crosstab query! Why didn't I think of that?

Except that once I created it and read throught the reference manual, I found this. "Access sorts the column heading values in ascending order by default."

So even though it provides all the data in one row, the order has been rearranged and the information is useless for me. Any other suggestions?

 

[dhookom]

The order of the fields/columns in the crosstab doesn't dictate the order that you display them in a form or report.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

No, it doesn't. However I'm no farther along than I was before I created the crosstab if the order is not correct. I want them to display as Rep1=most productive, Rep2=2nd most productive, and so on. I have that order correct in the table values, and I'm trying to preserve that order in the single record.

 

[dhookom]

Do you need to present this in a form or can it be done in a report?

You could also use automation from Access to display the results in Excel.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

I need the results displayed in Access, but I did think about exporting to Excel, using the transpose function to change columns to rows, and then re-importing back to Access. I had just hoped there would be a way to accomplish it in Access.

The concatenate code above seems that it could be used since it outputs CSVs but the code keeps failing on me at the point noted. I'm not sure if that's the best option, but it seems at least a possibility.

 

[MacroScope]

There's another way I thought of that might get the job done but I don't know how to write the expression. Rather than basing the form on the table as I had originally thought, it occurred to me that it could be completely unbound, and the Rep-Rep20 fields could be populated by expression.

There is an ID field as well which is ordered by productivity, but it is not always sequential. The ID might start at 161, then 162, 165, 166, 168, 172, etc. The lowest number is always the most productive, and the highest number the least productive, but they are not always 1 number apart, which leaves out the possibility of any expression that includes adding +1 to the minimum ID field.

A DLookup would find the name with the lowest ID number, but I don't know how it could be written to find the next lowest, then the next lowest, etc. without specifying the actual number used or using some sort of constant to add. Is that possible?

 

[dhookom]

I expect if you are having issues with

Concatenate("SELECT RepOrder FROM RepsByRV    WHERE RepOrder =""" & [RepOrder] & """",",",", and ")

RepsByRV might have criteria referencing a form control or something.

I have a sample that displays normalized data in an crosstab like view at Seating

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

Thanks. I like that setup.

I actually came up with a workaround which provided me with what I needed. It's maybe a bit awkward, but it does what I need it to do.

I just created unbound fields named Rep1-Rep20, and used an expression as the Control source. You can see what it is by looking at the last expression for Rep20. =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 an elegant solution but it works. I'm going to look a lot closer at what you sent and see if I can change it to implement that system instead. Thanks again.

 

[dhookom]

Nasty :-0 I would expect there is a better solution but I can't get my head around your data and table structures.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

Yeah, I agree. As I said, it's not elegant but it definitely works. I've heard that the transpose command that's used in Excel to convert rows to columns works within SQL but I can't find any reference to it in help or other documents. Do you know anything about that?

 

[dhookom]

There is no "transpose" in SQL that I am aware of. The PIVOT clause is used in Access crosstab queries.

Duane
Hook'D on Access
MS Access MVP