Combine field in several recs summarized into one record?

Access2000
How do I write a query that combines the CTC field from each record below into
one record? I need to concatenate the CTC field with a separator, like below:

BattID VehicleID STDATE STTIME CTC
LKO500HF 00000000 10/27/2003 4:13:51 AM 4
LKO500HF 00000000 10/27/2003 5:13:51 AM 5
LKO500HF 00000000 10/27/2003 10:13:51 AM 6
LKO500HF 00000000 10/27/2003 11:13:51 AM 4

The records above into one record like this:
BattID VehicleID STDATE CTC
LKO500HF 00000000 10/27/2003 4,5,6,4

I need this to work in Access but how its done in SQL should help.
mark

re: Combine field in several recs summarized into one record?

I would say you do this in code. the simple answer is to open the table
containing repeated records and loop thru it. for every record that is the
"same" (based on "important" columns we use to determine unique/repeat-ness)
we will pull the CTC into a buffer variable by concatenating it to the end
of our buffer. As soon as we have a "new" record we go ahead and write out
one record of the repeated data and our new concatenated-CTC field to a new
table then repeat the process. If you want a more detailed response, let me
know.

HTH

Mike Krous

"mark" <ms@nospam.comcast.net> wrote in message
news:Lt2dnfRIENqlSi-i4p2dnA@comcast.com...[color=blue]
> Access2000
> How do I write a query that combines the CTC field from each record below[/color]
into[color=blue]
> one record? I need to concatenate the CTC field with a separator, like[/color]
below:[color=blue]
>
> BattID VehicleID STDATE STTIME CTC
> LKO500HF 00000000 10/27/2003 4:13:51 AM 4
> LKO500HF 00000000 10/27/2003 5:13:51 AM 5
> LKO500HF 00000000 10/27/2003 10:13:51 AM 6
> LKO500HF 00000000 10/27/2003 11:13:51 AM 4
>
> The records above into one record like this:
> BattID VehicleID STDATE CTC
> LKO500HF 00000000 10/27/2003 4,5,6,4
>
> I need this to work in Access but how its done in SQL should help.
> mark
>[/color]

re: Combine field in several recs summarized into one record?

Thanks. I would appreciate a more detailed response.
I did try this but wasn't able to combine the CTC (aka. Faults) field for each
set of records. Instead all CTC fields were combined then placed into the CTC
text box in the report. So every record had the same string. How do I change
the function below to gather all CTC fields that have the same STDATE, BattID,
and VehicleID and apply them to just one record in the report, then gather the
next group of CTC records for the next record in the report with a different
STDATE, BattID, and VehicleID and apply it to the next record?
Here is the function I wrote below and called it from the OnFormat property.
One thing I see wrong with my function, but not sure how to fix is the
rs.FindFirt string is only searching on STDATE when it needs to search on BattID
and VehicleID to narrow down the CTCs returned.

Function FindFaultsWkly()
Dim rs As Recordset
Dim DB As Database
Dim strSQL As String
Dim strConcatCTC As String
Dim strCounttoThree As Integer

strSQL = "SELECT STDATE, BattID, VehicleID, CTC FROM CHARGELOG
WHERE STDATE BETWEEN #" & GStartDate & "# AND " & "#" & GEndDate & _
"# AND VehicleID= '" & GAssignToVID & "' AND BattID = '" &
GAssignToBID & "'"

CurrentDb.QueryDefs("Charge_Weekly_GetCTCrecs").SQ L = strSQL

Set DB = CurrentDb()
Set rs = DB.OpenRecordset("Charge_Weekly_GetCTCrecs", dbOpenDynaset)

'-------Specify STDATE in returned recordset to get proper CTC
codes 'for each day's record on report.
rs.FindFirst "STDATE=" & Reports![ChargeWeekly_Rpt]![STDATE]

rs.MoveFirst

While Not rs.EOF 'And strCounttoThree < 3
strConcatCTC = strConcatCTC & rs.Fields(3) & ", "
'This is to limit the number of CTC or Faults returned to 3.
'strCounttoThree = strCounttoThree + 1
rs.MoveNext
Wend

'RS.FindNext

' Use this line if the function is called from report
' detail section's OnFormat property event procedure.
Reports![ChargeWeekly_Rpt]![txtFaults] = strConcatCTC
End Function

Here are more records that has more combinations of the STDATE, BattID, and
VehicleID:
BattID VehicleID STDATE STTIME CTC
LKO500HF 00000000 10/24/2003 1:55:22 PM 4
LKO500HF 00000000 10/24/2003 2:05:17 PM 3
LKO500HF 00000000 10/24/2003 3:35:55 PM 5
LKO500HF 00000000 10/24/2003 3:54:26 PM 4
LKO500HF 00000000 10/27/2003 4:13:51 AM 4
LKO500HF 00000000 10/27/2003 5:13:51 AM 5
LKO500HF 00000000 10/27/2003 10:13:51 AM 6
LKO500HF 00000000 10/27/2003 11:13:51 AM 77
LKO500HG 00000000 10/27/2003 4:13:51 AM 89
LKO500HH 00000000 10/27/2003 4:13:51 AM 9
LKO500HH 00000000 10/27/2003 5:13:02 AM 1

Thanks.
mark

Mike Krous wrote:[color=blue]
> I would say you do this in code. the simple answer is to open the table
> containing repeated records and loop thru it. for every record that is the
> "same" (based on "important" columns we use to determine unique/repeat-ness)
> we will pull the CTC into a buffer variable by concatenating it to the end
> of our buffer. As soon as we have a "new" record we go ahead and write out
> one record of the repeated data and our new concatenated-CTC field to a new
> table then repeat the process. If you want a more detailed response, let me
> know.
>
> HTH
>
> Mike Krous
>
> "mark" <ms@nospam.comcast.net> wrote in message
> news:Lt2dnfRIENqlSi-i4p2dnA@comcast.com...
>[color=green]
>>Access2000
>>How do I write a query that combines the CTC field from each record below[/color]
>
> into
>[color=green]
>>one record? I need to concatenate the CTC field with a separator, like[/color]
>
> below:
>[color=green]
>>BattID VehicleID STDATE STTIME CTC
>>LKO500HF 00000000 10/27/2003 4:13:51 AM 4
>>LKO500HF 00000000 10/27/2003 5:13:51 AM 5
>>LKO500HF 00000000 10/27/2003 10:13:51 AM 6
>>LKO500HF 00000000 10/27/2003 11:13:51 AM 4
>>
>>The records above into one record like this:
>>BattID VehicleID STDATE CTC
>>LKO500HF 00000000 10/27/2003 4,5,6,4
>>
>>I need this to work in Access but how its done in SQL should help.
>>mark
>>[/color]
>
>
>[/color]

re: Combine field in several recs summarized into one record?

mark <ms@nospam.comcast.net> wrote in message news:<Lt2dnfRIENqlSi-i4p2dnA@comcast.com>...[color=blue]
> Access2000
> How do I write a query that combines the CTC field from each record below into
> one record? I need to concatenate the CTC field with a separator, like below:
>
> BattID VehicleID STDATE STTIME CTC
> LKO500HF 00000000 10/27/2003 4:13:51 AM 4
> LKO500HF 00000000 10/27/2003 5:13:51 AM 5
> LKO500HF 00000000 10/27/2003 10:13:51 AM 6
> LKO500HF 00000000 10/27/2003 11:13:51 AM 4
>
> The records above into one record like this:
> BattID VehicleID STDATE CTC
> LKO500HF 00000000 10/27/2003 4,5,6,4
>
> I need this to work in Access but how its done in SQL should help.
> mark[/color]

Read this article:
http://www.mvps.org/access/modules/mdl0004.htm

re: Combine field in several recs summarized into one record?

Mark, here is a sample routine that will take a record, find all matching
records and retreive a concatenated string of your CTC field. Keep in mind
I only wrote this enough to illustrate how it works, I did not modulize it
as well as it should be...I'll let you "clean it up". Ok on to the code.

Place the following three lines of code in the general declarations section
of you report.

Dim strBatID As String
Dim strVecID As String
Dim dtDate As String



Place the following code inside the Detail_Format routine of the report

'This routine checks each detail row, on "new" rows it allows it to be
'printed along with a concatenated field of all the related records.
Dim strBatTmp As String
Dim strVecTmp As String
Dim dtDateTmp As String

'get current values
strBatTmp = Me!BattID
strVecTmp = Me!VehicleID
dtDateTmp = Me!STDATE

'are they new?
If strBatTmp = strBatID And _
strVecTmp = strVecID And _
dtDateTmp = dtDate Then 'not new, this is a repeat row

Cancel = -1 'lets not print repeat rows
Else 'new row print it and the concatenated ctc
Me.txtCTC = GetConcat(strBatTmp, strVecTmp, CStr(dtDateTmp))
End If

'now transfer the current values to the previous values for our next
check
strBatID = strBatTmp
strVecID = strVecTmp
dtDate = dtDateTmp



almost done...
now place the following routine in the general declarations section of your
report

'This routine will take all fields that meet a criteria, concatenate one of
those fields
'and return the concatenated result.
Private Function GetConcat(strB As String, strV As String, strD As String)
As String
Dim rs As Recordset
Dim strSQL As String
Dim strTableName As String
Dim strFieldToConCat As String
Dim strBuffer As String


'*** -->> MODIFY HERE TO FIT YOUR TABLES AND FIELDS <<-- ***
strTableName = "concate"
strFieldToConCat = "CTC"
strSQL = "SELECT BattID, VehicleID, STDATE, CTC FROM " & strTableName &
_
" WHERE BattID='" & strB & "' and VehicleID='" & strV & "' and STDATE=#"
& strD & _
"# ORDER BY BattID, VehicleID, STDATE, STTIME;"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

strBuffer = ""
While Not rs.EOF
If strBuffer = "" Then
strBuffer = rs.Fields(strFieldToConCat) '<<--- Modify to fit
your field name
Else
strBuffer = strBuffer & "," & rs.Fields(strFieldToConCat)
End If
rs.MoveNext
Wend

rs.Close
Set rs = Nothing
GetConcat = strBuffer
End Function

Ok, thats it, this should get you started on the right track. Basically
what we are doing here is placing a variable for each of the 3 fields we
care about in the general declarations section so they are global. In the
Detail_Format routine we get each current set of those same 3 values and
place them in 3 temp variables, we then check these current temp variables
against the 3 global variables, if all 3 are not equal then we have a "new"
row, then we call our GetConcat routine to concatenate a field for the
matching records to this row and place the value in an unbound textbox. had
the 3 temp variables equaled the global variables exactly then we would have
simply cancelled this format which in turn will not print the detail row.
After each comparison regardless if its equal or not we assign our current
temp variables to the global variables, that way we can check it again the
next time the Detail_Format routine is called.

The GetConCat routine takes the values of the 3 temp variables and selects
all the matching records from the same table. At this point we simply loop
through all the matching records pulling out the CTC and tacking it on to
the end of our running buffer. When we have reached the end of our
recordset then we return the results of the buffer which should be all the
CTC values from each detail row seperated by commas.

HTH

Mike Krous



"mark" <ms@nospam.comcast.net> wrote in message
news:8bednYdCuoZiVy6iRVn-sw@comcast.com...[color=blue]
> Thanks. I would appreciate a more detailed response.
> I did try this but wasn't able to combine the CTC (aka. Faults) field for[/color]
each[color=blue]
> set of records. Instead all CTC fields were combined then placed into the[/color]
CTC[color=blue]
> text box in the report. So every record had the same string. How do I[/color]
change[color=blue]
> the function below to gather all CTC fields that have the same STDATE,[/color]
BattID,[color=blue]
> and VehicleID and apply them to just one record in the report, then gather[/color]
the[color=blue]
> next group of CTC records for the next record in the report with a[/color]
different[color=blue]
> STDATE, BattID, and VehicleID and apply it to the next record?
> Here is the function I wrote below and called it from the OnFormat[/color]
property.[color=blue]
> One thing I see wrong with my function, but not sure how to fix is the
> rs.FindFirt string is only searching on STDATE when it needs to search on[/color]
BattID[color=blue]
> and VehicleID to narrow down the CTCs returned.
>
> Function FindFaultsWkly()
> Dim rs As Recordset
> Dim DB As Database
> Dim strSQL As String
> Dim strConcatCTC As String
> Dim strCounttoThree As Integer
>
> strSQL = "SELECT STDATE, BattID, VehicleID, CTC FROM CHARGELOG
> WHERE STDATE BETWEEN #" & GStartDate & "# AND " & "#" & GEndDate & _
> "# AND VehicleID= '" & GAssignToVID & "' AND BattID = '" &
> GAssignToBID & "'"
>
> CurrentDb.QueryDefs("Charge_Weekly_GetCTCrecs").SQ L = strSQL
>
> Set DB = CurrentDb()
> Set rs = DB.OpenRecordset("Charge_Weekly_GetCTCrecs",[/color]
dbOpenDynaset)[color=blue]
>
> '-------Specify STDATE in returned recordset to get proper CTC
> codes 'for each day's record on report.
> rs.FindFirst "STDATE=" & Reports![ChargeWeekly_Rpt]![STDATE]
>
> rs.MoveFirst
>
> While Not rs.EOF 'And strCounttoThree < 3
> strConcatCTC = strConcatCTC & rs.Fields(3) & ", "
> 'This is to limit the number of CTC or Faults returned to 3.
> 'strCounttoThree = strCounttoThree + 1
> rs.MoveNext
> Wend
>
> 'RS.FindNext
>
> ' Use this line if the function is called from report
> ' detail section's OnFormat property event procedure.
> Reports![ChargeWeekly_Rpt]![txtFaults] = strConcatCTC
> End Function
>
> Here are more records that has more combinations of the STDATE, BattID,[/color]
and[color=blue]
> VehicleID:
> BattID VehicleID STDATE STTIME CTC
> LKO500HF 00000000 10/24/2003 1:55:22 PM 4
> LKO500HF 00000000 10/24/2003 2:05:17 PM 3
> LKO500HF 00000000 10/24/2003 3:35:55 PM 5
> LKO500HF 00000000 10/24/2003 3:54:26 PM 4
> LKO500HF 00000000 10/27/2003 4:13:51 AM 4
> LKO500HF 00000000 10/27/2003 5:13:51 AM 5
> LKO500HF 00000000 10/27/2003 10:13:51 AM 6
> LKO500HF 00000000 10/27/2003 11:13:51 AM 77
> LKO500HG 00000000 10/27/2003 4:13:51 AM 89
> LKO500HH 00000000 10/27/2003 4:13:51 AM 9
> LKO500HH 00000000 10/27/2003 5:13:02 AM 1
>
> Thanks.
> mark
>
> Mike Krous wrote:[color=green]
> > I would say you do this in code. the simple answer is to open the table
> > containing repeated records and loop thru it. for every record that is[/color][/color]
the[color=blue][color=green]
> > "same" (based on "important" columns we use to determine[/color][/color]
unique/repeat-ness)[color=blue][color=green]
> > we will pull the CTC into a buffer variable by concatenating it to the[/color][/color]
end[color=blue][color=green]
> > of our buffer. As soon as we have a "new" record we go ahead and write[/color][/color]
out[color=blue][color=green]
> > one record of the repeated data and our new concatenated-CTC field to a[/color][/color]
new[color=blue][color=green]
> > table then repeat the process. If you want a more detailed response,[/color][/color]
let me[color=blue][color=green]
> > know.
> >
> > HTH
> >
> > Mike Krous
> >
> > "mark" <ms@nospam.comcast.net> wrote in message
> > news:Lt2dnfRIENqlSi-i4p2dnA@comcast.com...
> >[color=darkred]
> >>Access2000
> >>How do I write a query that combines the CTC field from each record[/color][/color][/color]
below[color=blue][color=green]
> >
> > into
> >[color=darkred]
> >>one record? I need to concatenate the CTC field with a separator, like[/color]
> >
> > below:
> >[color=darkred]
> >>BattID VehicleID STDATE STTIME CTC
> >>LKO500HF 00000000 10/27/2003 4:13:51 AM 4
> >>LKO500HF 00000000 10/27/2003 5:13:51 AM 5
> >>LKO500HF 00000000 10/27/2003 10:13:51 AM 6
> >>LKO500HF 00000000 10/27/2003 11:13:51 AM 4
> >>
> >>The records above into one record like this:
> >>BattID VehicleID STDATE CTC
> >>LKO500HF 00000000 10/27/2003 4,5,6,4
> >>
> >>I need this to work in Access but how its done in SQL should help.
> >>mark
> >>[/color]
> >
> >
> >[/color]
>[/color]

re: Combine field in several recs summarized into one record?

Thanks for the article.

Pieter Linden wrote:[color=blue]
> mark <ms@nospam.comcast.net> wrote in message news:<Lt2dnfRIENqlSi-i4p2dnA@comcast.com>...
>[color=green]
>>Access2000
>>How do I write a query that combines the CTC field from each record below into
>>one record? I need to concatenate the CTC field with a separator, like below:
>>
>>BattID VehicleID STDATE STTIME CTC
>>LKO500HF 00000000 10/27/2003 4:13:51 AM 4
>>LKO500HF 00000000 10/27/2003 5:13:51 AM 5
>>LKO500HF 00000000 10/27/2003 10:13:51 AM 6
>>LKO500HF 00000000 10/27/2003 11:13:51 AM 4
>>
>>The records above into one record like this:
>>BattID VehicleID STDATE CTC
>>LKO500HF 00000000 10/27/2003 4,5,6,4
>>
>>I need this to work in Access but how its done in SQL should help.
>>mark[/color]
>
>
> Read this article:
> http://www.mvps.org/access/modules/mdl0004.htm[/color]

re: Combine field in several recs summarized into one record?

Thanks. Your help is greatly appreciated. It works great, except for one thing.
The code successfully concatenates the string but does not return the record in
the report. If there is only one record returned to the report because the
BattID, VehicleID, and STDATE are all the same from the query then the record
isn't applied to the report then the report is blank.
For example, if the records from the query are:
BattID VehicleID STDATE CTC
LKO500HF 00000000 10/29/2003 3
LKO500HF 00000000 10/29/2003 4

Then only one record is returned to the report annd would be:
LKO500HF 00000000 10/29/2003 3,4

Instead, the record is missing and the report is blank. So, it not only does not
return the CTC field but the entire record is not copied to the report.

I noticed in the Detail_Format routine when Cancel = -1 is executed because the
global vars = the temp vars., Me.txtCTC field holds the concatenated CTC string.

If only one more record is added to the recordset in the query with a different
ie. BattID:
BattID VehicleID STDATE CTC
LKO500HF 00000000 10/29/2003 3
LKO500HF 00000000 10/29/2003 4
ABCDEFGH 00000000 10/29/2003 4

Then both records are returned to the report properly:
LKO500HF 00000000 10/29/2003 3,4
ABCDEFGH 00000000 10/29/2003 4



Mike Krous wrote:
[color=blue]
> Mark, here is a sample routine that will take a record, find all matching
> records and retreive a concatenated string of your CTC field. Keep in mind
> I only wrote this enough to illustrate how it works, I did not modulize it
> as well as it should be...I'll let you "clean it up". Ok on to the code.
>
> Place the following three lines of code in the general declarations section
> of you report.
>
> Dim strBatID As String
> Dim strVecID As String
> Dim dtDate As String
>
>
>
> Place the following code inside the Detail_Format routine of the report
>
> 'This routine checks each detail row, on "new" rows it allows it to be
> 'printed along with a concatenated field of all the related records.
> Dim strBatTmp As String
> Dim strVecTmp As String
> Dim dtDateTmp As String
>
> 'get current values
> strBatTmp = Me!BattID
> strVecTmp = Me!VehicleID
> dtDateTmp = Me!STDATE
>
> 'are they new?
> If strBatTmp = strBatID And _
> strVecTmp = strVecID And _
> dtDateTmp = dtDate Then 'not new, this is a repeat row
>
> Cancel = -1 'lets not print repeat rows
> Else 'new row print it and the concatenated ctc
> Me.txtCTC = GetConcat(strBatTmp, strVecTmp, CStr(dtDateTmp))
> End If
>
> 'now transfer the current values to the previous values for our next
> check
> strBatID = strBatTmp
> strVecID = strVecTmp
> dtDate = dtDateTmp
>
>
>
> almost done...
> now place the following routine in the general declarations section of your
> report
>
> 'This routine will take all fields that meet a criteria, concatenate one of
> those fields
> 'and return the concatenated result.
> Private Function GetConcat(strB As String, strV As String, strD As String)
> As String
> Dim rs As Recordset
> Dim strSQL As String
> Dim strTableName As String
> Dim strFieldToConCat As String
> Dim strBuffer As String
>
>
> '*** -->> MODIFY HERE TO FIT YOUR TABLES AND FIELDS <<-- ***
> strTableName = "concate"
> strFieldToConCat = "CTC"
> strSQL = "SELECT BattID, VehicleID, STDATE, CTC FROM " & strTableName &
> _
> " WHERE BattID='" & strB & "' and VehicleID='" & strV & "' and STDATE=#"
> & strD & _
> "# ORDER BY BattID, VehicleID, STDATE, STTIME;"
>
> Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
>
> strBuffer = ""
> While Not rs.EOF
> If strBuffer = "" Then
> strBuffer = rs.Fields(strFieldToConCat) '<<--- Modify to fit
> your field name
> Else
> strBuffer = strBuffer & "," & rs.Fields(strFieldToConCat)
> End If
> rs.MoveNext
> Wend
>
> rs.Close
> Set rs = Nothing
> GetConcat = strBuffer
> End Function
>
> Ok, thats it, this should get you started on the right track. Basically
> what we are doing here is placing a variable for each of the 3 fields we
> care about in the general declarations section so they are global. In the
> Detail_Format routine we get each current set of those same 3 values and
> place them in 3 temp variables, we then check these current temp variables
> against the 3 global variables, if all 3 are not equal then we have a "new"
> row, then we call our GetConcat routine to concatenate a field for the
> matching records to this row and place the value in an unbound textbox. had
> the 3 temp variables equaled the global variables exactly then we would have
> simply cancelled this format which in turn will not print the detail row.
> After each comparison regardless if its equal or not we assign our current
> temp variables to the global variables, that way we can check it again the
> next time the Detail_Format routine is called.
>
> The GetConCat routine takes the values of the 3 temp variables and selects
> all the matching records from the same table. At this point we simply loop
> through all the matching records pulling out the CTC and tacking it on to
> the end of our running buffer. When we have reached the end of our
> recordset then we return the results of the buffer which should be all the
> CTC values from each detail row seperated by commas.
>
> HTH
>
> Mike Krous
>
>
>
> "mark" <ms@nospam.comcast.net> wrote in message
> news:8bednYdCuoZiVy6iRVn-sw@comcast.com...
>[color=green]
>>Thanks. I would appreciate a more detailed response.
>>I did try this but wasn't able to combine the CTC (aka. Faults) field for[/color]
>
> each
>[color=green]
>>set of records. Instead all CTC fields were combined then placed into the[/color]
>
> CTC
>[color=green]
>>text box in the report. So every record had the same string. How do I[/color]
>
> change
>[color=green]
>>the function below to gather all CTC fields that have the same STDATE,[/color]
>
> BattID,
>[color=green]
>>and VehicleID and apply them to just one record in the report, then gather[/color]
>
> the
>[color=green]
>>next group of CTC records for the next record in the report with a[/color]
>
> different
>[color=green]
>>STDATE, BattID, and VehicleID and apply it to the next record?
>>Here is the function I wrote below and called it from the OnFormat[/color]
>
> property.
>[color=green]
>>One thing I see wrong with my function, but not sure how to fix is the
>>rs.FindFirt string is only searching on STDATE when it needs to search on[/color]
>
> BattID
>[color=green]
>>and VehicleID to narrow down the CTCs returned.
>>
>>Function FindFaultsWkly()
>> Dim rs As Recordset
>> Dim DB As Database
>> Dim strSQL As String
>> Dim strConcatCTC As String
>> Dim strCounttoThree As Integer
>>
>> strSQL = "SELECT STDATE, BattID, VehicleID, CTC FROM CHARGELOG
>>WHERE STDATE BETWEEN #" & GStartDate & "# AND " & "#" & GEndDate & _
>> "# AND VehicleID= '" & GAssignToVID & "' AND BattID = '" &
>>GAssignToBID & "'"
>>
>>CurrentDb.QueryDefs("Charge_Weekly_GetCTCrecs"). SQL = strSQL
>>
>> Set DB = CurrentDb()
>> Set rs = DB.OpenRecordset("Charge_Weekly_GetCTCrecs",[/color]
>
> dbOpenDynaset)
>[color=green]
>> '-------Specify STDATE in returned recordset to get proper CTC
>>codes 'for each day's record on report.
>> rs.FindFirst "STDATE=" & Reports![ChargeWeekly_Rpt]![STDATE]
>>
>> rs.MoveFirst
>>
>>While Not rs.EOF 'And strCounttoThree < 3
>> strConcatCTC = strConcatCTC & rs.Fields(3) & ", "
>> 'This is to limit the number of CTC or Faults returned to 3.
>> 'strCounttoThree = strCounttoThree + 1
>> rs.MoveNext
>> Wend
>>
>> 'RS.FindNext
>>
>> ' Use this line if the function is called from report
>> ' detail section's OnFormat property event procedure.
>> Reports![ChargeWeekly_Rpt]![txtFaults] = strConcatCTC
>>End Function
>>
>>Here are more records that has more combinations of the STDATE, BattID,[/color]
>
> and
>[color=green]
>>VehicleID:
>>BattID VehicleID STDATE STTIME CTC
>>LKO500HF 00000000 10/24/2003 1:55:22 PM 4
>>LKO500HF 00000000 10/24/2003 2:05:17 PM 3
>>LKO500HF 00000000 10/24/2003 3:35:55 PM 5
>>LKO500HF 00000000 10/24/2003 3:54:26 PM 4
>>LKO500HF 00000000 10/27/2003 4:13:51 AM 4
>>LKO500HF 00000000 10/27/2003 5:13:51 AM 5
>>LKO500HF 00000000 10/27/2003 10:13:51 AM 6
>>LKO500HF 00000000 10/27/2003 11:13:51 AM 77
>>LKO500HG 00000000 10/27/2003 4:13:51 AM 89
>>LKO500HH 00000000 10/27/2003 4:13:51 AM 9
>>LKO500HH 00000000 10/27/2003 5:13:02 AM 1
>>
>>Thanks.
>>mark
>>
>>Mike Krous wrote:
>>[color=darkred]
>>>I would say you do this in code. the simple answer is to open the table
>>>containing repeated records and loop thru it. for every record that is[/color][/color]
>
> the
>[color=green][color=darkred]
>>>"same" (based on "important" columns we use to determine[/color][/color]
>
> unique/repeat-ness)
>[color=green][color=darkred]
>>>we will pull the CTC into a buffer variable by concatenating it to the[/color][/color]
>
> end
>[color=green][color=darkred]
>>>of our buffer. As soon as we have a "new" record we go ahead and write[/color][/color]
>
> out
>[color=green][color=darkred]
>>>one record of the repeated data and our new concatenated-CTC field to a[/color][/color]
>
> new
>[color=green][color=darkred]
>>>table then repeat the process. If you want a more detailed response,[/color][/color]
>
> let me
>[color=green][color=darkred]
>>>know.
>>>
>>>HTH
>>>
>>>Mike Krous
>>>
>>>"mark" <ms@nospam.comcast.net> wrote in message
>>>news:Lt2dnfRIENqlSi-i4p2dnA@comcast.com...
>>>
>>>
>>>>Access2000
>>>>How do I write a query that combines the CTC field from each record[/color][/color]
>
> below
>[color=green][color=darkred]
>>>into
>>>
>>>
>>>>one record? I need to concatenate the CTC field with a separator, like
>>>
>>>below:
>>>
>>>
>>>>BattID VehicleID STDATE STTIME CTC
>>>>LKO500HF 00000000 10/27/2003 4:13:51 AM 4
>>>>LKO500HF 00000000 10/27/2003 5:13:51 AM 5
>>>>LKO500HF 00000000 10/27/2003 10:13:51 AM 6
>>>>LKO500HF 00000000 10/27/2003 11:13:51 AM 4
>>>>
>>>>The records above into one record like this:
>>>>BattID VehicleID STDATE CTC
>>>>LKO500HF 00000000 10/27/2003 4,5,6,4
>>>>
>>>>I need this to work in Access but how its done in SQL should help.
>>>>mark
>>>>
>>>
>>>
>>>[/color][/color]
>
>[/color]

re: Combine field in several recs summarized into one record?

I figured out the cause and resolved it.
I changed the cancel = -1 value to Exit Sub.
Again, thanks for the input.

mark wrote:[color=blue]
> Thanks. Your help is greatly appreciated. It works great, except for one
> thing.
> The code successfully concatenates the string but does not return the
> record in the report. If there is only one record returned to the report
> because the BattID, VehicleID, and STDATE are all the same from the
> query then the record isn't applied to the report then the report is blank.
> For example, if the records from the query are:
> BattID VehicleID STDATE CTC
> LKO500HF 00000000 10/29/2003 3
> LKO500HF 00000000 10/29/2003 4
>
> Then only one record is returned to the report annd would be:
> LKO500HF 00000000 10/29/2003 3,4
>
> Instead, the record is missing and the report is blank. So, it not only
> does not return the CTC field but the entire record is not copied to the
> report.
>
> I noticed in the Detail_Format routine when Cancel = -1 is executed
> because the global vars = the temp vars., Me.txtCTC field holds the
> concatenated CTC string.
>
> If only one more record is added to the recordset in the query with a
> different ie. BattID:
> BattID VehicleID STDATE CTC
> LKO500HF 00000000 10/29/2003 3
> LKO500HF 00000000 10/29/2003 4
> ABCDEFGH 00000000 10/29/2003 4
>
> Then both records are returned to the report properly:
> LKO500HF 00000000 10/29/2003 3,4
> ABCDEFGH 00000000 10/29/2003 4
>
>
>
> Mike Krous wrote:
>[color=green]
>> Mark, here is a sample routine that will take a record, find all matching
>> records and retreive a concatenated string of your CTC field. Keep in
>> mind
>> I only wrote this enough to illustrate how it works, I did not
>> modulize it
>> as well as it should be...I'll let you "clean it up". Ok on to the code.
>>
>> Place the following three lines of code in the general declarations
>> section
>> of you report.
>>
>> Dim strBatID As String
>> Dim strVecID As String
>> Dim dtDate As String
>>
>>
>>
>> Place the following code inside the Detail_Format routine of the report
>>
>> 'This routine checks each detail row, on "new" rows it allows it to be
>> 'printed along with a concatenated field of all the related records.
>> Dim strBatTmp As String
>> Dim strVecTmp As String
>> Dim dtDateTmp As String
>>
>> 'get current values
>> strBatTmp = Me!BattID
>> strVecTmp = Me!VehicleID
>> dtDateTmp = Me!STDATE
>>
>> 'are they new?
>> If strBatTmp = strBatID And _
>> strVecTmp = strVecID And _
>> dtDateTmp = dtDate Then 'not new, this is a repeat row
>>
>> Cancel = -1 'lets not print repeat rows
>> Else 'new row print it and the concatenated ctc
>> Me.txtCTC = GetConcat(strBatTmp, strVecTmp, CStr(dtDateTmp))
>> End If
>>
>> 'now transfer the current values to the previous values for our next
>> check
>> strBatID = strBatTmp
>> strVecID = strVecTmp
>> dtDate = dtDateTmp
>>
>>
>>
>> almost done...
>> now place the following routine in the general declarations section of
>> your
>> report
>>
>> 'This routine will take all fields that meet a criteria, concatenate
>> one of
>> those fields
>> 'and return the concatenated result.
>> Private Function GetConcat(strB As String, strV As String, strD As
>> String)
>> As String
>> Dim rs As Recordset
>> Dim strSQL As String
>> Dim strTableName As String
>> Dim strFieldToConCat As String
>> Dim strBuffer As String
>>
>>
>> '*** -->> MODIFY HERE TO FIT YOUR TABLES AND FIELDS <<-- ***
>> strTableName = "concate"
>> strFieldToConCat = "CTC"
>> strSQL = "SELECT BattID, VehicleID, STDATE, CTC FROM " &
>> strTableName &
>> _
>> " WHERE BattID='" & strB & "' and VehicleID='" & strV & "' and
>> STDATE=#"
>> & strD & _
>> "# ORDER BY BattID, VehicleID, STDATE, STTIME;"
>>
>> Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
>>
>> strBuffer = ""
>> While Not rs.EOF
>> If strBuffer = "" Then
>> strBuffer = rs.Fields(strFieldToConCat) '<<--- Modify to
>> fit
>> your field name
>> Else
>> strBuffer = strBuffer & "," & rs.Fields(strFieldToConCat)
>> End If
>> rs.MoveNext
>> Wend
>>
>> rs.Close
>> Set rs = Nothing
>> GetConcat = strBuffer
>> End Function
>>
>> Ok, thats it, this should get you started on the right track. Basically
>> what we are doing here is placing a variable for each of the 3 fields we
>> care about in the general declarations section so they are global. In
>> the
>> Detail_Format routine we get each current set of those same 3 values and
>> place them in 3 temp variables, we then check these current temp
>> variables
>> against the 3 global variables, if all 3 are not equal then we have a
>> "new"
>> row, then we call our GetConcat routine to concatenate a field for the
>> matching records to this row and place the value in an unbound
>> textbox. had
>> the 3 temp variables equaled the global variables exactly then we
>> would have
>> simply cancelled this format which in turn will not print the detail row.
>> After each comparison regardless if its equal or not we assign our
>> current
>> temp variables to the global variables, that way we can check it again
>> the
>> next time the Detail_Format routine is called.
>>
>> The GetConCat routine takes the values of the 3 temp variables and
>> selects
>> all the matching records from the same table. At this point we simply
>> loop
>> through all the matching records pulling out the CTC and tacking it on to
>> the end of our running buffer. When we have reached the end of our
>> recordset then we return the results of the buffer which should be all
>> the
>> CTC values from each detail row seperated by commas.
>>
>> HTH
>>
>> Mike Krous
>>
>>
>>
>> "mark" <ms@nospam.comcast.net> wrote in message
>> news:8bednYdCuoZiVy6iRVn-sw@comcast.com...
>>[color=darkred]
>>> Thanks. I would appreciate a more detailed response.
>>> I did try this but wasn't able to combine the CTC (aka. Faults) field
>>> for[/color]
>>
>>
>> each
>>[color=darkred]
>>> set of records. Instead all CTC fields were combined then placed
>>> into the[/color]
>>
>>
>> CTC
>>[color=darkred]
>>> text box in the report. So every record had the same string. How do I[/color]
>>
>>
>> change
>>[color=darkred]
>>> the function below to gather all CTC fields that have the same STDATE,[/color]
>>
>>
>> BattID,
>>[color=darkred]
>>> and VehicleID and apply them to just one record in the report, then
>>> gather[/color]
>>
>>
>> the
>>[color=darkred]
>>> next group of CTC records for the next record in the report with a[/color]
>>
>>
>> different
>>[color=darkred]
>>> STDATE, BattID, and VehicleID and apply it to the next record?
>>> Here is the function I wrote below and called it from the OnFormat[/color]
>>
>>
>> property.
>>[color=darkred]
>>> One thing I see wrong with my function, but not sure how to fix is the
>>> rs.FindFirt string is only searching on STDATE when it needs to
>>> search on[/color]
>>
>>
>> BattID
>>[color=darkred]
>>> and VehicleID to narrow down the CTCs returned.
>>>
>>> Function FindFaultsWkly()
>>> Dim rs As Recordset
>>> Dim DB As Database
>>> Dim strSQL As String
>>> Dim strConcatCTC As String
>>> Dim strCounttoThree As Integer
>>>
>>> strSQL = "SELECT STDATE, BattID, VehicleID, CTC FROM CHARGELOG
>>> WHERE STDATE BETWEEN #" & GStartDate & "# AND " & "#" & GEndDate & _
>>> "# AND VehicleID= '" & GAssignToVID & "' AND BattID = '" &
>>> GAssignToBID & "'"
>>>
>>> CurrentDb.QueryDefs("Charge_Weekly_GetCTCrecs").SQ L = strSQL
>>>
>>> Set DB = CurrentDb()
>>> Set rs = DB.OpenRecordset("Charge_Weekly_GetCTCrecs",[/color]
>>
>>
>> dbOpenDynaset)
>>[color=darkred]
>>> '-------Specify STDATE in returned recordset to get proper CTC
>>> codes 'for each day's record on report.
>>> rs.FindFirst "STDATE=" & Reports![ChargeWeekly_Rpt]![STDATE]
>>>
>>> rs.MoveFirst
>>>
>>> While Not rs.EOF 'And strCounttoThree < 3
>>> strConcatCTC = strConcatCTC & rs.Fields(3) & ", "
>>> 'This is to limit the number of CTC or Faults returned to 3.
>>> 'strCounttoThree = strCounttoThree + 1
>>> rs.MoveNext
>>> Wend
>>>
>>> 'RS.FindNext
>>>
>>> ' Use this line if the function is called from report
>>> ' detail section's OnFormat property event procedure.
>>> Reports![ChargeWeekly_Rpt]![txtFaults] = strConcatCTC
>>> End Function
>>>
>>> Here are more records that has more combinations of the STDATE, BattID,[/color]
>>
>>
>> and
>>[color=darkred]
>>> VehicleID:
>>> BattID VehicleID STDATE STTIME CTC
>>> LKO500HF 00000000 10/24/2003 1:55:22 PM 4
>>> LKO500HF 00000000 10/24/2003 2:05:17 PM 3
>>> LKO500HF 00000000 10/24/2003 3:35:55 PM 5
>>> LKO500HF 00000000 10/24/2003 3:54:26 PM 4
>>> LKO500HF 00000000 10/27/2003 4:13:51 AM 4
>>> LKO500HF 00000000 10/27/2003 5:13:51 AM 5
>>> LKO500HF 00000000 10/27/2003 10:13:51 AM 6
>>> LKO500HF 00000000 10/27/2003 11:13:51 AM 77
>>> LKO500HG 00000000 10/27/2003 4:13:51 AM 89
>>> LKO500HH 00000000 10/27/2003 4:13:51 AM 9
>>> LKO500HH 00000000 10/27/2003 5:13:02 AM 1
>>>
>>> Thanks.
>>> mark
>>>
>>> Mike Krous wrote:
>>>
>>>> I would say you do this in code. the simple answer is to open the table
>>>> containing repeated records and loop thru it. for every record that is[/color]
>>
>>
>> the
>>[color=darkred]
>>>> "same" (based on "important" columns we use to determine[/color]
>>
>>
>> unique/repeat-ness)
>>[color=darkred]
>>>> we will pull the CTC into a buffer variable by concatenating it to the[/color]
>>
>>
>> end
>>[color=darkred]
>>>> of our buffer. As soon as we have a "new" record we go ahead and write[/color]
>>
>>
>> out
>>[color=darkred]
>>>> one record of the repeated data and our new concatenated-CTC field to a[/color]
>>
>>
>> new
>>[color=darkred]
>>>> table then repeat the process. If you want a more detailed response,[/color]
>>
>>
>> let me
>>[color=darkred]
>>>> know.
>>>>
>>>> HTH
>>>>
>>>> Mike Krous
>>>>
>>>> "mark" <ms@nospam.comcast.net> wrote in message
>>>> news:Lt2dnfRIENqlSi-i4p2dnA@comcast.com...
>>>>
>>>>
>>>>> Access2000
>>>>> How do I write a query that combines the CTC field from each record[/color]
>>
>>
>> below
>>[color=darkred]
>>>> into
>>>>
>>>>
>>>>> one record? I need to concatenate the CTC field with a separator, like
>>>>
>>>>
>>>> below:
>>>>
>>>>
>>>>> BattID VehicleID STDATE STTIME CTC
>>>>> LKO500HF 00000000 10/27/2003 4:13:51 AM 4
>>>>> LKO500HF 00000000 10/27/2003 5:13:51 AM 5
>>>>> LKO500HF 00000000 10/27/2003 10:13:51 AM 6
>>>>> LKO500HF 00000000 10/27/2003 11:13:51 AM 4
>>>>>
>>>>> The records above into one record like this:
>>>>> BattID VehicleID STDATE CTC
>>>>> LKO500HF 00000000 10/27/2003 4,5,6,4
>>>>>
>>>>> I need this to work in Access but how its done in SQL should help.
>>>>> mark
>>>>>
>>>>
>>>>
>>>>[/color]
>>
>>[/color]
>[/color]

re: Combine field in several recs summarized into one record?

glad to see your off and running...

"mark" <ms@nospam.comcast.net> wrote in message
news:bL2dnZys6fNQySiiRVn-sA@comcast.com...[color=blue]
> I figured out the cause and resolved it.
> I changed the cancel = -1 value to Exit Sub.
> Again, thanks for the input.
>
> mark wrote:[color=green]
> > Thanks. Your help is greatly appreciated. It works great, except for one
> > thing.
> > The code successfully concatenates the string but does not return the
> > record in the report. If there is only one record returned to the report
> > because the BattID, VehicleID, and STDATE are all the same from the
> > query then the record isn't applied to the report then the report is[/color][/color]
blank.[color=blue][color=green]
> > For example, if the records from the query are:
> > BattID VehicleID STDATE CTC
> > LKO500HF 00000000 10/29/2003 3
> > LKO500HF 00000000 10/29/2003 4
> >
> > Then only one record is returned to the report annd would be:
> > LKO500HF 00000000 10/29/2003 3,4
> >
> > Instead, the record is missing and the report is blank. So, it not only
> > does not return the CTC field but the entire record is not copied to the
> > report.
> >
> > I noticed in the Detail_Format routine when Cancel = -1 is executed
> > because the global vars = the temp vars., Me.txtCTC field holds the
> > concatenated CTC string.
> >
> > If only one more record is added to the recordset in the query with a
> > different ie. BattID:
> > BattID VehicleID STDATE CTC
> > LKO500HF 00000000 10/29/2003 3
> > LKO500HF 00000000 10/29/2003 4
> > ABCDEFGH 00000000 10/29/2003 4
> >
> > Then both records are returned to the report properly:
> > LKO500HF 00000000 10/29/2003 3,4
> > ABCDEFGH 00000000 10/29/2003 4
> >
> >
> >
> > Mike Krous wrote:
> >[color=darkred]
> >> Mark, here is a sample routine that will take a record, find all[/color][/color][/color]
matching[color=blue][color=green][color=darkred]
> >> records and retreive a concatenated string of your CTC field. Keep in
> >> mind
> >> I only wrote this enough to illustrate how it works, I did not
> >> modulize it
> >> as well as it should be...I'll let you "clean it up". Ok on to the[/color][/color][/color]
code.[color=blue][color=green][color=darkred]
> >>
> >> Place the following three lines of code in the general declarations
> >> section
> >> of you report.
> >>
> >> Dim strBatID As String
> >> Dim strVecID As String
> >> Dim dtDate As String
> >>
> >>
> >>
> >> Place the following code inside the Detail_Format routine of the report
> >>
> >> 'This routine checks each detail row, on "new" rows it allows it to be
> >> 'printed along with a concatenated field of all the related records.
> >> Dim strBatTmp As String
> >> Dim strVecTmp As String
> >> Dim dtDateTmp As String
> >>
> >> 'get current values
> >> strBatTmp = Me!BattID
> >> strVecTmp = Me!VehicleID
> >> dtDateTmp = Me!STDATE
> >>
> >> 'are they new?
> >> If strBatTmp = strBatID And _
> >> strVecTmp = strVecID And _
> >> dtDateTmp = dtDate Then 'not new, this is a repeat row
> >>
> >> Cancel = -1 'lets not print repeat rows
> >> Else 'new row print it and the concatenated ctc
> >> Me.txtCTC = GetConcat(strBatTmp, strVecTmp, CStr(dtDateTmp))
> >> End If
> >>
> >> 'now transfer the current values to the previous values for our[/color][/color][/color]
next[color=blue][color=green][color=darkred]
> >> check
> >> strBatID = strBatTmp
> >> strVecID = strVecTmp
> >> dtDate = dtDateTmp
> >>
> >>
> >>
> >> almost done...
> >> now place the following routine in the general declarations section of
> >> your
> >> report
> >>
> >> 'This routine will take all fields that meet a criteria, concatenate
> >> one of
> >> those fields
> >> 'and return the concatenated result.
> >> Private Function GetConcat(strB As String, strV As String, strD As
> >> String)
> >> As String
> >> Dim rs As Recordset
> >> Dim strSQL As String
> >> Dim strTableName As String
> >> Dim strFieldToConCat As String
> >> Dim strBuffer As String
> >>
> >>
> >> '*** -->> MODIFY HERE TO FIT YOUR TABLES AND FIELDS <<-- ***
> >> strTableName = "concate"
> >> strFieldToConCat = "CTC"
> >> strSQL = "SELECT BattID, VehicleID, STDATE, CTC FROM " &
> >> strTableName &
> >> _
> >> " WHERE BattID='" & strB & "' and VehicleID='" & strV & "' and
> >> STDATE=#"
> >> & strD & _
> >> "# ORDER BY BattID, VehicleID, STDATE, STTIME;"
> >>
> >> Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
> >>
> >> strBuffer = ""
> >> While Not rs.EOF
> >> If strBuffer = "" Then
> >> strBuffer = rs.Fields(strFieldToConCat) '<<--- Modify to
> >> fit
> >> your field name
> >> Else
> >> strBuffer = strBuffer & "," & rs.Fields(strFieldToConCat)
> >> End If
> >> rs.MoveNext
> >> Wend
> >>
> >> rs.Close
> >> Set rs = Nothing
> >> GetConcat = strBuffer
> >> End Function
> >>
> >> Ok, thats it, this should get you started on the right track.[/color][/color][/color]
Basically[color=blue][color=green][color=darkred]
> >> what we are doing here is placing a variable for each of the 3 fields[/color][/color][/color]
we[color=blue][color=green][color=darkred]
> >> care about in the general declarations section so they are global. In
> >> the
> >> Detail_Format routine we get each current set of those same 3 values[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> >> place them in 3 temp variables, we then check these current temp
> >> variables
> >> against the 3 global variables, if all 3 are not equal then we have a
> >> "new"
> >> row, then we call our GetConcat routine to concatenate a field for the
> >> matching records to this row and place the value in an unbound
> >> textbox. had
> >> the 3 temp variables equaled the global variables exactly then we
> >> would have
> >> simply cancelled this format which in turn will not print the detail[/color][/color][/color]
row.[color=blue][color=green][color=darkred]
> >> After each comparison regardless if its equal or not we assign our
> >> current
> >> temp variables to the global variables, that way we can check it again
> >> the
> >> next time the Detail_Format routine is called.
> >>
> >> The GetConCat routine takes the values of the 3 temp variables and
> >> selects
> >> all the matching records from the same table. At this point we simply
> >> loop
> >> through all the matching records pulling out the CTC and tacking it on[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> >> the end of our running buffer. When we have reached the end of our
> >> recordset then we return the results of the buffer which should be all
> >> the
> >> CTC values from each detail row seperated by commas.
> >>
> >> HTH
> >>
> >> Mike Krous
> >>
> >>
> >>
> >> "mark" <ms@nospam.comcast.net> wrote in message
> >> news:8bednYdCuoZiVy6iRVn-sw@comcast.com...
> >>
> >>> Thanks. I would appreciate a more detailed response.
> >>> I did try this but wasn't able to combine the CTC (aka. Faults) field
> >>> for
> >>
> >>
> >> each
> >>
> >>> set of records. Instead all CTC fields were combined then placed
> >>> into the
> >>
> >>
> >> CTC
> >>
> >>> text box in the report. So every record had the same string. How do I
> >>
> >>
> >> change
> >>
> >>> the function below to gather all CTC fields that have the same STDATE,
> >>
> >>
> >> BattID,
> >>
> >>> and VehicleID and apply them to just one record in the report, then
> >>> gather
> >>
> >>
> >> the
> >>
> >>> next group of CTC records for the next record in the report with a
> >>
> >>
> >> different
> >>
> >>> STDATE, BattID, and VehicleID and apply it to the next record?
> >>> Here is the function I wrote below and called it from the OnFormat
> >>
> >>
> >> property.
> >>
> >>> One thing I see wrong with my function, but not sure how to fix is the
> >>> rs.FindFirt string is only searching on STDATE when it needs to
> >>> search on
> >>
> >>
> >> BattID
> >>
> >>> and VehicleID to narrow down the CTCs returned.
> >>>
> >>> Function FindFaultsWkly()
> >>> Dim rs As Recordset
> >>> Dim DB As Database
> >>> Dim strSQL As String
> >>> Dim strConcatCTC As String
> >>> Dim strCounttoThree As Integer
> >>>
> >>> strSQL = "SELECT STDATE, BattID, VehicleID, CTC FROM[/color][/color][/color]
CHARGELOG[color=blue][color=green][color=darkred]
> >>> WHERE STDATE BETWEEN #" & GStartDate & "# AND " & "#" & GEndDate &[/color][/color][/color]
_[color=blue][color=green][color=darkred]
> >>> "# AND VehicleID= '" & GAssignToVID & "' AND BattID = '"[/color][/color][/color]
&[color=blue][color=green][color=darkred]
> >>> GAssignToBID & "'"
> >>>
> >>> CurrentDb.QueryDefs("Charge_Weekly_GetCTCrecs").SQ L = strSQL
> >>>
> >>> Set DB = CurrentDb()
> >>> Set rs = DB.OpenRecordset("Charge_Weekly_GetCTCrecs",
> >>
> >>
> >> dbOpenDynaset)
> >>
> >>> '-------Specify STDATE in returned recordset to get proper[/color][/color][/color]
CTC[color=blue][color=green][color=darkred]
> >>> codes 'for each day's record on report.
> >>> rs.FindFirst "STDATE=" & Reports![ChargeWeekly_Rpt]![STDATE]
> >>>
> >>> rs.MoveFirst
> >>>
> >>> While Not rs.EOF 'And strCounttoThree < 3
> >>> strConcatCTC = strConcatCTC & rs.Fields(3) & ", "
> >>> 'This is to limit the number of CTC or Faults returned to[/color][/color][/color]
3.[color=blue][color=green][color=darkred]
> >>> 'strCounttoThree = strCounttoThree + 1
> >>> rs.MoveNext
> >>> Wend
> >>>
> >>> 'RS.FindNext
> >>>
> >>> ' Use this line if the function is called from report
> >>> ' detail section's OnFormat property event procedure.
> >>> Reports![ChargeWeekly_Rpt]![txtFaults] = strConcatCTC
> >>> End Function
> >>>
> >>> Here are more records that has more combinations of the STDATE,[/color][/color][/color]
BattID,[color=blue][color=green][color=darkred]
> >>
> >>
> >> and
> >>
> >>> VehicleID:
> >>> BattID VehicleID STDATE STTIME CTC
> >>> LKO500HF 00000000 10/24/2003 1:55:22 PM 4
> >>> LKO500HF 00000000 10/24/2003 2:05:17 PM 3
> >>> LKO500HF 00000000 10/24/2003 3:35:55 PM 5
> >>> LKO500HF 00000000 10/24/2003 3:54:26 PM 4
> >>> LKO500HF 00000000 10/27/2003 4:13:51 AM 4
> >>> LKO500HF 00000000 10/27/2003 5:13:51 AM 5
> >>> LKO500HF 00000000 10/27/2003 10:13:51 AM 6
> >>> LKO500HF 00000000 10/27/2003 11:13:51 AM 77
> >>> LKO500HG 00000000 10/27/2003 4:13:51 AM 89
> >>> LKO500HH 00000000 10/27/2003 4:13:51 AM 9
> >>> LKO500HH 00000000 10/27/2003 5:13:02 AM 1
> >>>
> >>> Thanks.
> >>> mark
> >>>
> >>> Mike Krous wrote:
> >>>
> >>>> I would say you do this in code. the simple answer is to open the[/color][/color][/color]
table[color=blue][color=green][color=darkred]
> >>>> containing repeated records and loop thru it. for every record that[/color][/color][/color]
is[color=blue][color=green][color=darkred]
> >>
> >>
> >> the
> >>
> >>>> "same" (based on "important" columns we use to determine
> >>
> >>
> >> unique/repeat-ness)
> >>
> >>>> we will pull the CTC into a buffer variable by concatenating it to[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >>
> >>
> >> end
> >>
> >>>> of our buffer. As soon as we have a "new" record we go ahead and[/color][/color][/color]
write[color=blue][color=green][color=darkred]
> >>
> >>
> >> out
> >>
> >>>> one record of the repeated data and our new concatenated-CTC field to[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> >>
> >>
> >> new
> >>
> >>>> table then repeat the process. If you want a more detailed response,
> >>
> >>
> >> let me
> >>
> >>>> know.
> >>>>
> >>>> HTH
> >>>>
> >>>> Mike Krous
> >>>>
> >>>> "mark" <ms@nospam.comcast.net> wrote in message
> >>>> news:Lt2dnfRIENqlSi-i4p2dnA@comcast.com...
> >>>>
> >>>>
> >>>>> Access2000
> >>>>> How do I write a query that combines the CTC field from each record
> >>
> >>
> >> below
> >>
> >>>> into
> >>>>
> >>>>
> >>>>> one record? I need to concatenate the CTC field with a separator,[/color][/color][/color]
like[color=blue][color=green][color=darkred]
> >>>>
> >>>>
> >>>> below:
> >>>>
> >>>>
> >>>>> BattID VehicleID STDATE STTIME CTC
> >>>>> LKO500HF 00000000 10/27/2003 4:13:51 AM 4
> >>>>> LKO500HF 00000000 10/27/2003 5:13:51 AM 5
> >>>>> LKO500HF 00000000 10/27/2003 10:13:51 AM 6
> >>>>> LKO500HF 00000000 10/27/2003 11:13:51 AM 4
> >>>>>
> >>>>> The records above into one record like this:
> >>>>> BattID VehicleID STDATE CTC
> >>>>> LKO500HF 00000000 10/27/2003 4,5,6,4
> >>>>>
> >>>>> I need this to work in Access but how its done in SQL should help.
> >>>>> mark
> >>>>>
> >>>>
> >>>>
> >>>>
> >>
> >>[/color]
> >[/color]
>[/color]