Combine duplicate record field values and then separate out again

[vicissitude]

Would really appreciate any help on this, can't seem to find a solution.

 

[ajetrumpet]

this IS possible with a recordset:

the rest I have no idea. i would suggest hiring someone if it's as complicated as it seems.

 

[Brianwarnock]

If all you want to do is get from your first table to something that looks like that form for reference purposes then why not do it to a report via a simple query, whats all the bit inbetween about?

Brian

 

[vicissitude]

Yes i have access set up to display reports of journey information but unfortunately this information is available from a different database so one has to be closed down to access the other. So i decided to link the tables from one into the other. But in one database records are by journey with many under the same name and the other database records are by name.

Thus i started using a crosstab query to change the format and leave me with one name per row to be compatible with the second database. This gives me a quick reference to basic information from the journey database which can then be filtered and called up via command buttons.

But it only works with one supplier per day per name. If there are 2 suppliers on the same day then the crosstab query i am using at the moment cannot display both of them so it picks the latest supplier entered and this can then remove any other suppliers on the same week displayed on the form.

I am very new to access and so am just working by trial and error to see what will get me the result i am looking for. So apologies if my post is a bit obscure.

Any pointers for a beginner on recordsets?

Thanks.

 

[ajetrumpet]

Any pointers for a beginner on recordsets?

Thanks.

you can build a new table by using code, but a query would probably be better. I really don't like doing things this way anymore, but so many people here post questions about how to transform tables in very strange and unusual ways, that there really is no other solution other than recordsets a lot of the time. at any rate, make a new table that looks like the CROSSTAB query result you posted initally, and run this code on your old table:

function t

dim lastrecunique as boolean
dim pname as string
dim day as string
dim supplier as string
dim db as dao.database
dim rsold as dao.recordset
dim rsnew as dao.recordset
dim rstemp as dao.recordset

set db=currentdb()
set rsold = db.openrecordset("SELECT DISTINCT pname, day " & _
                            "FROM [old table]", dbopendyanset)
set rsnew = db.openrecordset("new table name", dbopendyanset)

rsold.movefirst

with rsold
   do until .eof

      pname=!pname
      day=!day

set rstemp = db.openrecordset("SELECT supplier " & _
                             "FROM [old table] WHERE " & _
                             "[pname] = '" & pname & "' and " & _
                             "[day] = '" & day & "'", dbopendynaset)

         rstemp.movefirst

            do until rstemp.eof
               supplier = supplier & " " & !supplier
                     rstemp.movenext
            loop

               rsnew.addnew
               rsnew.fields(pname) = pname
               rsnew.fields(day) = trim(supplier)
               rsnew.update

         rstemp.close

   loop
end with

db.close
rsold.close
rsnew.close

set db=nothing
set rsold=nothing
set rsnew=nothing
set rstemp=nothing

end function

i wold hope that there would be a simpler way to do it, but that'll give you the crosstab query that you want.

 

[Brianwarnock]

If all you wish to achieve from table1 is putting the names of suppliers under each day and the Name1 etc then the attached could be worked on to look better.

Brian

 

[vicissitude]

Thank you ajetrumpet, however i really don't know what i am doing

Where does this code go?

Do you put it into a module and then call it from a query? If so what would Query sql code look like?

Thanks.

 

[ajetrumpet]

Thank you ajetrumpet, however i really don't know what i am doing

Where does this code go?

Do you put it into a module and then call it from a query? If so what would Query sql code look like?

Thanks.

I have already told you what to do vic:

make a new table that looks like the CROSSTAB query result you posted initally, and run this code on your old table:

in case you dont know what that says:

1) create a table
2) put 6 fields in it (make it look EXACTLY like the crosstab query picture you have in your orig. post
3) do not add records
4) add a new module to your application
5) copy and paste code from here to the new module

Code syntax
1) wherever you see "pname" (NOT "!pname) in the code, replace with the actual NAME field in your old table
2) replace "old table" with your old table name
3) replace "new table" with the name of the new table you created
4) run the code


If those instructions are still too complicated, I suggest using a program that you are more familiar with. I have written way more than I want to.

 

[vicissitude]

Thanks so much for clarifying. Because I am such a noobie, when i did not see the 'function()' at the top of the code it did not ring any familiarity bells so i thought i must be missing something so apologies for the extra effort, but it really is appreciated.

I think i am getting there, slowly,

set rsold = db.openrecordset("SELECT DISTINCT pname, day " & _
"FROM [old table]", dbopendyanset)
set rsnew = db.openrecordset("new table name", dbopendyanset)

That misspelling got me for a while as the debugger did not stop at it but kept flagging pname=!pname

so i got that sorted and now the debugger has moved on to

supplier = supplier & " " & !supplier (!supplier = <item not found in this collection>.)

which is a bit strange because it is giving values for pname and day when the cursor is passed across them as well as dbOpenDynaset =2 (I take it that should be 3?) and i have checked the 'old table' syntax and also there is a value for supplier with the values for day and pname given. But will keep plugging away at it.

set rstemp = db.openrecordset("SELECT supplier " & _
"FROM [old table] WHERE " & _
"[pname] = '" & pname & "' and " & _
"[day] = '" & day & "'", dbopendynaset)

Thanks again.

 

[ajetrumpet]

if you cant get it to work, upload the db and i wil take a look for yoiu

 

[vicissitude]

That is a very kind offer, thank you.

Will keep messing about with it for a while and see how i get on. Even though it can be very frustrating it is a great way to learn what is going on.