Combine field in several recs summarized into one record?

[mark]

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

[Mike Krous]

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" <[e-mail]> wrote in message
news:Lt********************@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, 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

[mark]

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:

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" <[e-mail]> wrote in message
news:Lt********************@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, 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

[Pieter Linden]

mark <[e-mail]> wrote in message news:<Lt********************@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, 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

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

[Mike Krous]

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" <[e-mail]> wrote in message
news:8b********************@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 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:

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" <[e-mail]> wrote in message
news:Lt********************@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, 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

[mark]

Thanks for the article.

Pieter Linden wrote:

mark <[e-mail]> wrote in message news:<Lt********************@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, 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

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

[mark]

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:

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" <[e-mail]> wrote in message
news:8b********************@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 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",

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:

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" <[e-mail]> wrote in message
news:Lt********************@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, 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

[mark]

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

mark wrote:

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:

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" <[e-mail]> wrote in message
news:8b********************@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 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:

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" <[e-mail]> wrote in message
news:Lt********************@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, 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
>

[Mike Krous]

glad to see your off and running...

"mark" <[e-mail]> wrote in message
news:bL********************@comcast.com...

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

mark wrote:

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:

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" <[e-mail]> wrote in message
news:8b********************@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 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:

> 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" <[e-mail]> wrote in message
> news:Lt********************@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, 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
>>
>
>
>