combining rows.. again

[jerryb]

I've tried several things I have found around the net, including here.. but this
is still kicking my butt. I'm assuming (based on the other code I have tried)
this will have to be VBA?

I am trying to
combine a cell from different rows in the table.

Job
Suffix
Sequence
Description

The job number
will be the same for all rows, as well as the suffix.
The


sequence however will be +1 every row. Meaning one row will be 1000, next row
will be 1001, next row will be 1002 etc.

Here is the kicker.. I need to combine the description field for
all matching job,suffix and 99 sequences (example 1000-1099 or 15000-15099).

table reads:
job =1, suffix =1, sequence=1000, description=abcdef
job =1, suffix =1, sequence=1001, description=ghijk
job =1, suffix =1, sequence=1002, description=lmnop
job =2, suffix =1, sequence=5000, description=abcdef
job =2, suffix =1, sequence=5001, description=ghijk
job =2, suffix =1, sequence=5002, description=lmnop

new table needs to read:
job =1, suffix =1, sequence=1000, description=abcdefghijklmnop
job=2, suffix =1, sequence=5000, description=abcdefghijklmnop


ANY help is greatly appreciated!

[John_G]

Hi -

Yes, it will require VBA, but you can do it with one pass through the table, perhaps something like this:

Dim SQL1 as string, SQL2 as string, rst as Recordset, db as Database
Dim DescriptionString as String
Dim CurrentJob as integer, CurrentSuffix As integer
set db = CurrentDB
SQL1 = "Select * from YourTable order by Job, Suffix, Sequence"
DescriptionString="" ' Initialize
Set rst = db.openrecordset(SQL1)

while not rst.EOF
CurrentJob=rst!job
Currentsuffix=rst!Suffix
descriptionstring = descriptionstring & rst!description
rst.movenext
if not rst.eof then

if rst!job <> CurrentJob or rst!suffix <> CurrentSuffix then
'
' New grouping started - add a summary record to the new table
'
Code for that here...
'
' Then re-initialize the long string
'
descriptionstring=""
endif

endif
Wend
'
' Write the last summary record to the new table
'
Code for that here...
rst.close


That may not be quite right, but it's close and should point you in the right direction. I don't how long your descriptions are, but you should probably make the long concatenated string a Memo type field in the new table.

HTH

John

[jerryb]

Whew, above my head! But I mostly follow it. Could you clarify for me or point me in the direction to find info about grouping?
And thank you very much for the help!
Jerry

[jerryb]

in the new table, description is set as a memo. In the current table they are limited to 30 char and usually just 4 or 5 of them, so should exceen the max.

Should the or be an and in this part? "if rst!job <> CurrentJob or rst!suffix <> CurrentSuffix then"
Have to excuse my access/vb skills, its been many years since I played with it.

[jerryb]

I realized I left something pretty important out. I need to group seq 1000-1099, BUT there will be seq=2000, 3000, 4000 etc that I do not want in there. I only need to group them if the sequence ends in 1-99. I think with the code above it will group all of the sequence descriptions.
so maybe something along the lines of...
sequence = 1000
if sequence =previous sequence +1 then previous sequence description+current sequence description
else move on...

[June7]

The OR operator is correct.

You don't want the concatenation to include records when seq number is x000, except for 1000?

Hope you can tell that code suggested by John_G is not a complete procedure, just rough pseudocode. Will need a nested loop to concatenate the descriptions.

[jerryb]

I need it to combine all of the descriptions that do not end in 000.. it may be 1000-1005 or it could be 4000-4009
Yes I realize that. I do a lot of crystal reports in other DB's, so I'm able to mostly follow what he means. Although this one is way more complicated than Im used to.

In my head I need to read the first record (1000), then check to see if there is a +1 (1001), if so then combine the descriptions, repeat until +1 is false. Then go on to the next record (2000)

Hopefully that makes sense to someone who knows access way better than I do.
Appreciate the response.

[June7]

Programming logic is the same no matter the language. And Access is not a programming language, VBA is used in Access project. Yes, the algorithm described looks appropriate. Now write code, debug, and when you encounter issue that defies debugging, post for analysis.

[jerryb]

Unfortunately I know as much about VB as I do bigfoots shoe size, so I'm having to learn how to do what with what as I go. But now I at least think this is possible to do. Appreciate the input.

[John_G]

Hi -

If you don't wany any records that have a sequence number x000, then change this:

SQL1 = "Select * from YourTable order by Job, Suffix, Sequence"

to this:

SQL1 = "Select * from YourTable where sequence mod 1000 <> 0 order by Job, Suffix, Sequence"

The MOD operator divides sequence by 1000 and yields the remainder ( e.g. 1024 mod 1000 = 24 ). Sequence number ending in 000 will have a remainder of 0, and the WHERE clause in the Select statement excludes them.



June - I thought about using a nested loop, but it's not needed as I have written it - I just reset the concatenated string to empty when a Job - Prefix change happens.

John

[John_G]

Hi -

I looked at your requirements again; in your new table, you want the records WITH sequence= x000, but with the descriptions for all the associated records (x001, x002,....added on - is that correct?

If so, there is one question - is (for example) , Job 1, Suffix 1 GUARANTEED to have only sequences 1xxx and not 2xxx, and the reverse - are sequences 1xxx GUARANTEED to apply only to one Job - Suffix combination?

For this set of requirements, a nested loop setup might be easier. I'll wait for your answer.

John

[jerryb]

John,

Yes that is correct.
No that is not guaranteed. Every job is different. They all start out with 1000, and by default move to 2000, 3000, 4000 etc. BUT anyone of them could have x001, x002 etc. Someone could also come back later on and add 2500..
The only unique items will be the job and suffix combination.
Example: These are 2 different jobs
032456-001
032456-002
Sequences 0001-100000 are available for every job.

Jerry

[John_G]

Hi Jerry -

OK, so your second table could look something like this:

Job=032456, Suffix=001, Sequence=1000, description=.. : Grouping all the 1000's for job 032456-001
Job=032456, Suffix=001, Sequence=2000, description=.. : Grouping all the 2000's for job 032456-001
Job=032456, Suffix=002, Sequence=1000, description=.. : Grouping all the 1000's for job 032456-002
Job=032456, Suffix=002, Sequence=2000, description=.. : Grouping all the 2000's for job 032456-002

I think I have it now?

John

[jerryb]

That's it exactly.

Jerry

[John_G]

Hi Jerry -

Here is some (untested) code to try.

Dim SQL1 As string, SQL2 As String
Dim rst1 As Recordset, rst2 As Recordset
Dim db as Database
Dim DescriptionString As String

set db = CurrentDB


SQL1 = "Select Job, Suffix, Sequence, Description from YourTable " & _
" WHERE Sequence MOD 1000 = 0 order by Job, Suffix, Sequence"

'
' In rst1, each Job-Suffix-Sequence combination is unique
' 
Set rst1 = db.OpenRecordset(SQL1)

While Not rst1.EOF
   DescriptionString = rst1!Description 
   '
   ' Now get all the records in this group
   '
   SQL2 = "Select * from Yourtable where " & _
    " Job = " & rst1!Job & " AND Suffix = " & rst1!Suffix & _
    " AND Sequence \ 1000 = " & rst1!Sequence \ 1000 & _
    " AND Sequence mod 1000 <> 0 order by Sequence"
   set rst2=db.OpenRecordset(SQL2)
   While Not rst2.EOF
      '
      ' Concatenate the description
      '
      DescriptionString = DescriptionString & rst2!Description 
      rst2.MoveNext
   Wend
   '
   ' Add a record to the new table
   ' job=rst1!Job, Suffix=rst1!suffix, Sequence=rst1!Sequence, Description=DescriptionString
   '
   ' Code to add a record goes here
   rst1.MoveNext
Wend
rst1.Close
rst2.Close
set db = Nothing

The trickiest part is SQL2 - an example of it, once it's put together by the code, might be:

Select * from Yourtable where Job = 032456 AND Suffix = 001 AND Sequence \ 1000 = 1 AND Sequence mod 1000 <> 0 order by Sequence

The "\" operator is integer division : 1234 \ 1000 = 1

Quick question - are the job and prefix values numeric, or are they text strings that just look numeric? (i.e. what is their data type?)

Hope this helps you out.

John