Need to create 1 row per employee from a table of multiple rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I work in Human resources and am working with a History table that lists the
employee changes. Within this History table is multiple rows per
employee that lists the change and effective date.
I need to create a separate table listing all the changes the employees have
had sequentially on one row.
I work using queries and have not used VBA in my existing role. I've tried
to do this and am unsure of how. Please help!
 
My $.02 worth. Doesn't sound like you need a table so much as a crosstab
query of your History table. Without more information about the fields
involved it would be pretty hard to make a recommendation on how to proceed.


Ed G
 
Ed,

I did do a crosstab, and that didn't work. I need to do it with the 1st
change, second change, third change, etc. in the row. I don't know how to do
it. There is an auto number feature that sequences the effective dates, but
it doesn't give 1 2 3, it gives a unique number per change. Am I confusing
this? Sorry, thanks for your response.
 
Hi Duane. Thanks for replying. I'm not sure what I'm doing. I mostly write
queries and I don't exactly follow what you directed me to. Please help.
 
This is the basics from another resource. There are lines for both ADO or
DAO depending on your requirements.

To use any function like this, open a new module. Copy the code from
"Function Con..." to "End Function" into the new module. Select
Debug|Compile to make sure there are no compile errors. Then save the module
as "modConcatenate". You can then use the Concatenate() function as an
expression in a query or control source or other places.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'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
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
 
Hey guys. Thanks but I'm attempting that script and it
keeps giving me "undefined function 'Concatenate' in
expression"

I hate to be a pest but, is there a plug in or upgrade
that i have to do to my database to recognize this? Or am
I writing it wrong. I've attempted different ways, and the
database that Duane sent works when I run it.

Any suggestions would be much apppreciated. Thanks for
all of your help.
 
Did you add the function to a standard module? Did you try compile the
modules?
 
Back
Top