Concatentate a Single Field From Multiple Records

[shaggy]

I have a table of every reading of every electric meter. This history goes back a couple of years.

Field1 = date
Field2 = Meter#
Field3 = Single digit code identifying method of reading

The same meter is read multiple times in a year. The number of readings in the database is 1 or more (no limit).

I want to create a single line of text in 1 field that shows a history of the reading method of a particular meter.

Reading method translations
A=Radio Frequency
B=Manual
C=Not Read
X=Estimated


Here's a sample of what I would like to see

Meter# History
46521 ABAAAXAAB
42137 XAAACA
40037 C
41920 BAA
40059 AAABAAA

The 1st character in the history represents the most recent reading method, and each character to the right represents an older reading.

There is no limit to the number of readings, but I may want to limit the history to a maximum # of readings.

I tried creating a Crosstab query, but could only get it to give me counts of each unique reading method for each meter.

Any suggestions would be greatly appreciated.

 

[gemma-the-husky]

Do these bits with visual query design

first get a query that extracts just your meter numbers (unique values)
use this query to create another query with this field and another field defined

readinglist: mytranlist(meterno) {where meterno is the name of the other field in your query}


Now do this with code

now you need a function called mytranlist in a module, which will read all the records for the meterno, and form the string of readings you need.

After you have created this function, you should be able to open your query and see the results.

Function mytranlist(meterno As String) As String
[edited as per Paul's observations]

Dim rst As Recordset
Dim sqlstrg As String
Dim result As String

'note - this sql string selects a recordset of the readings for the selected meter 
'you may need an "orderby readingdate desc" at the end of this sql to get the readings in the correct order
'if meterno is a number rather than a string, then you won't need the chr(34)s

sqlstrg = "select *  from meterreadings where [meterno] = " & Chr(34) & meterno & Chr(34)

Set rst = CurrentDb.OpenRecordset(sqlstrg)

'Paul's observation - if you have a meter with no readings yet, there will be nothing to check, so exit straightaway
if rst.nomatch then
    mytranlist=""
    exit function
end if

result=""
While Not rst.eof
    result = result & rst!readingtype & ", "
'form your string here however you want it - this is comma separated so you can see what it is doing
    rst.MoveNext
Wend
rst.Close

result = left(result,len(result)-1) 'remove the trailing comma
mytranlist = result
End Function

 

[pbaldy]

If it's possible that there would be no meter readings for a given meter, you'll want to drop:

rst.MoveFirst

since it isn't really necessary and it will throw an error if the recordset is empty. Personally I'd trim off the trailing ", " after the loop, but that's just my preference.

 

[shaggy]

Thanks

I don't know if I'll be able to try this unil after Thanksgiving, but I really appreciate the help.

 

[Cosmos75]

Maybe this will help?

Concatenating unique field values for records matching a query's GROUP BY clause