New to the forum, need some recordset help

[secondangel]

Hi there im new , been watching and using for help but i need some specifcs. If anyone can help im greatly appreciated.

Problem://

Table is called CDTRACKS

Field 1 = UPC (barcode)
Field 2 = Position
Field 3 = Artist
Field 4 = Title


Ok. If a cd has 10 tracks then there will be 10 records, with Field 1 having duplicate entries and Position going from number 1 to 10.


I know i can concatenate Position/Artist/Title

To give for ex. 01 BlocParty Ares
02 BlocParty Mercury

But then what i need is like a ' while field 1 ,next record = field 1 current record (i.e same cd) then copy pos/art/title and ammend and add to current pos/art/title

to give me

5055036261852 01 BlocParty Ares 02 BlocParty Mercury

in 2 fields

field 1 = code
field 2 = info



IS this possible, and if so whats the code liekly to be like.

Thanks so much

Ashley

 

[namliam]

Presuming you have a table that should contain UPC, Code and INFO it is a simple matter of joining the tables on the UPC.

 

[secondangel]

hmm. care to explain. sorry - soemtimes i have gt knowledge in access and can do complex things and then soemtimes i dont even know how to do the simplest of things.

i presume you mean in a join query ? in like the sql editor bit of the design. ? rough idea of code id need.

edit. a join is no good. im using one table, i want to merge within the one table. like i can concatenate fields 2 3 and 4 into field 2 giving me fields 1 and fields 2.

i then want all of the field 2 entries to be inside each otherso instead of having



CDTRACKGROUP CDTRACKGROUP
UPC (Barcode) TracksInfo
008811237929 01. BLINK182- DUMPWEED
008811237929 02. BLINK182- DON'T LEAVE ME
008811237929 03. BLINK182- ALIENS EXIST
008811237929 04. BLINK182- FAMILY REUNION
008811237929 05. BLINK182- GOING AWAY TO COLLEGE
008811237929 06. BLINK182- WHAT'S MY AGE AGAIN?
008811237929 07. BLINK182- DICK LIPS
008811237929 08. BLINK182- BLOW JOB
008811237929 09. BLINK182- UNTITLED
008811237929 10. BLINK182- VOYEUR
008811237929 11. BLINK182- PATHETIC
008811237929 12. BLINK182- ADAM'S SONG
008811237929 13. BLINK182- PEGGY SUE
008811237929 14. BLINK182- WENDY CLEAR
008811237929 15. BLINK182- CAROUSEL
008811237929 16. BLINK182- ALL THE SMALL THINGS
008811237929 17. BLINK182- MUTT
008811237929 18. BLINK182- THE COUNTRY SONG

etc etc

but i just want

CDTRACKGROUP CDTRACKGROUP
UPC (Barcode) TracksInfo
008811237929 01. BLINK182- DUMPWEED 02. BLINK182- DON'T LEAVE ME 03. BLINK182- ALIENS EXIST 04. BLINK182- FAMILY REUNION

as an example pasted from my db


SO basically only one record per unique UPC with all the data held in one record

 

[secondangel]

i.e heres some duff code (what do programmers call mock code again - i graduated 7 years ago with a computer degree and its all like dust in my brain these days) i kinda threw together which obivously doesnt work


Set rs = db.OpenRecordset(CDTRACKGROUP)

' Table is CDTRACKGROUP
' UPC(Barcode) IS column A
' TracksInfo IS column B


Dim strLastCode As String
strLastCode = rs.UPC(Barcode)


While Not rs.EOF
Dim strTracklist As String
strTracklist = ""
While Not rs.EOF And strLastCode = rs.UPC(Barcode) ' so while we are not end of file and on the same barcode then
strTracklist = strTracklist & rs.TracksInfo & ", " ' copy the trackinfo from the row with spacings
rs.MoveNext
Wend
' strTracklist = substr(strTracklist, 0, -2) ' delete the last 2 characters which are ", "
' Dump the cell
If Not rs.EOF Then strLastCode = rs.UPC(Barcode)
Wend

 

[jal]

Someone just asked a similar question, wanting to do concatenation of strings. I gave him this sample code, which puts the resulting string in a "destination" table. Looks to me like you were on the right "track" (pardon the pun).


Dim cn as Adodb.connection
Set cn = CurrentProject.Connection
Dim rsSource as New Adodb.Recordset
rsSource.Open "SELECT * FROM table1", cn, adOpenKeyset, adLockOptimistic
Dim str as String
Do While rsSource.EOF = false
'concatenate
str = str & rs("Col1") & rs("Col2") ....etc
rs.MoveNext
Loop
rsSource.close
set rsSource = Nothing
Dim rsDest as New ADODB.RecordSet
'pull zero records
rsDest.Open "SELECT * FROM DestTable WHERE 1 = 0", cn, adOpenKeyset, adLockOptimistic
rsDest.AddNew 'adds a blank row
rsDest("DestCol") = str
rsDest.MoveFirst
rsDest.Close
set rsDest = Nothing

 

[namliam]

OK
I think I understand... there used to be a sample for this with unlimited possibility in code, but if you have limited options, which I think you do... CDs can only contain so many numbers right?
So you can do this in a series of queries...

1) qryStart
SELECT UPC, Position, [Position] & " " & [Artist] & " " & [Title] AS Expr1
FROM YourTable;

2) qryCrossIt
TRANSFORM Max(qryStart.Expr1) AS MaxOfExpr1
SELECT qryStart.UPC
FROM qryStart
GROUP BY qryStart.UPC
PIVOT qryStart.Position in (1,2,3,4,5,6,7,8,9,10) ;

Note:
The "in (1,2,3,4)" determains what columns are forced to be there. So if you want always 30 numbers, fill it to 30
If you want 50, fill it to 50.

3) qryEndResult
SELECT qryCrossIt.UPC
, Trim([1] & " " & [2] & " " & [3] & " " & [4] & " " & [5] & " " & [6] & " " & [7] & " " & [8] & " " & [9] & " " & [10]) AS Expr1
FROM qryCrossIt;

Note
Here also I go to 10, if you want 30 extend it...

qryEndResult should give you what you are looking for.

 

[secondangel]

Someone just asked a similar question, wanting to do concatenation of strings. I gave him this sample code, which puts the resulting string in a "destination" table. Looks to me like you were on the right "track" (pardon the pun).


Dim cn as Adodb.connection
Set cn = CurrentProject.Connection
Dim rsSource as New Adodb.Recordset
rsSource.Open "SELECT * FROM table1", cn, adOpenKeyset, adLockOptimistic
Dim str as String
Do While rsSource.EOF = false
'concatenate
str = str & rs("Col1") & rs("Col2") ....etc
rs.MoveNext
Loop
rsSource.close
set rsSource = Nothing
Dim rsDest as New ADODB.RecordSet
'pull zero records
rsDest.Open "SELECT * FROM DestTable WHERE 1 = 0", cn, adOpenKeyset, adLockOptimistic
rsDest.AddNew 'adds a blank row
rsDest("DestCol") = str
rsDest.MoveFirst
rsDest.Close
set rsDest = Nothing

copied this in and ammended the bold items for my actual table name and field names however i get an error

i get the rs sub or function not defined

do i need to add into the above code

set rs=db.OpenRecordset(CDTRACKGROUP)

 

[secondangel]

OK
I think I understand... there used to be a sample for this with unlimited possibility in code, but if you have limited options, which I think you do... CDs can only contain so many numbers right?
So you can do this in a series of queries...

1) qryStart
SELECT UPC, Position, [Position] & " " & [Artist] & " " & [Title] AS Expr1
FROM YourTable;

2) qryCrossIt
TRANSFORM Max(qryStart.Expr1) AS MaxOfExpr1
SELECT qryStart.UPC
FROM qryStart
GROUP BY qryStart.UPC
PIVOT qryStart.Position in (1,2,3,4,5,6,7,8,9,10) ;

Note:
The "in (1,2,3,4)" determains what columns are forced to be there. So if you want always 30 numbers, fill it to 30
If you want 50, fill it to 50.

3) qryEndResult
SELECT qryCrossIt.UPC
, Trim([1] & " " & [2] & " " & [3] & " " & [4] & " " & [5] & " " & [6] & " " & [7] & " " & [8] & " " & [9] & " " & [10]) AS Expr1
FROM qryCrossIt;

Note
Here also I go to 10, if you want 30 extend it...

qryEndResult should give you what you are looking for.

tried this

qryStart runs fine
qryCrossIt gives me an error thats got an unknown message 1038
qryEndResult gives the same error - probably becuase qrycrossit gives me an error in the first place.


heres a copy of my EXACT code in the 3 seperate queries . i am also trying the db record set code in vb too a suggested by the other poster

SELECT UPC, Position, [Position] & ". " & [Artist] & "~ " & [Title] AS Expr1
FROM CDTRACKS;


TRANSFORM Max(qryStart.Expr1) AS MaxOfExpr1
SELECT qryStart.UPC
FROM qryStart
GROUP BY qryStart.UPC
PIVOT qryStart.Position in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30);

SELECT qryCrossIt.UPC, Trim([1] & " " & [2] & " " & [3] & " " & [4] & " " & [5] & " " & [6] & " " & [7] & " " & [8] & " " & [9] & " " & [10] & " " & [11] & " " & [12] & " " & [13] & " " & [14] & " " & [15] & " " & [16] & " " & [17] & " " & [18] & " " & [19] & " " & [20] & " " & [21] & " " & [22] & " " & [23] & " " & [24] & " " & [25] & " " & [26] & " " & [27] & " " & [28] & " " & [29] & " " & [30]) AS Expr1
FROM qryCrossIt;

 

[namliam]

In some versions (older ones I think) I think that the syntax is
Transform
In (1,2,......)
Select
etc.

Please find attached a working sample for you to look at (in access 97, compiled from my 2002 version)

The "rs" in Jal's sample should read "rsSource", his way will be (considerably) slower than my way...

 

[secondangel]

Wow thanks for teh code, i converted it to a 2003 db and tried it and saw that its fine.

Differences in code were in the first query where it just added brackets around the UPC and Position in the select statement.

SELECT [UPC],[Position], [Position] & " " & [Artist] & " " & [Title]] AS Expr1 FROM CDTRACKS


The other 2 queries looked the same.

So i copied and pasted the code into mine but it didnt work (query 2 and 3)

So i dbl checked and in mine i had position set to text so i changed that to number.

SO in exported your queries into my db

and they dont work. So it must be soemthing in my db or something not installed - objects maybe. not sure

Any ideas.

Its got to be the syntax for the TRANSFORM command maybe

 

[secondangel]

Still for some reason cannot get the queries to work in my db.

I exported all the 3 queries and your table25 adn then they work in my db so it has to be somethign to do with the design of my table etc.

Ill go through and debug and comapre

Ashley


edit: debugged and it was my table field settings.

i have a problem with expr1.

i set in crossit to go up to 60 and it works fine
but then if endresult is set to 60 it gives em an error. If i ammend down to 59,58,57,56,55,54,53,52,52,51 is till get error but when its set to 50 it works.

this isnt to do with t h fieldsizes beign 50 is it ?


if its down to expr1 being able to only handle certain amount of characters should i just split it into 2 queries one to go from upc[1] to [30] to give expr1 and two to go from upc[31] to [60 as expr2 then do a select and [expr1] &" "& [expr2]

?

surely it shouldnt need this

 

[namliam]

Can you post your non-working version? Maybe I can find the problem?

If your position is set to a text that may be the problem I dont know... But it is hard to figure from here what the problem may be.

As you have noticed my version works nicely

 

[secondangel]

Ok here goes.

Thanks again

Ashley



Its fine as is, qryCrossIt is set to 60 but qryEndResult is set to 50. Set it any higher and you get an error result. As soem cds have 54 tracks etc i need it to do 60

 

[namliam]

54 numbers on one CD? Wow... I dont think I have ever seen that many on one CD....

I have never seen this, but I have the same problem. It seems you cannot concatinate more than what 99 strings??

You can get around it by cutting qryEndResult up to concatinate by i.e. tens.... like so:

SELECT qryCrossIt.UPC
, Trim( [1] & " " &  [2] & " " &  [3] & " " &  [4] & " " &  [5] & " " &  [6] & " " &  [7] & " " &  [8] & " " &  [9] & " " & [10] ) AS Expr1
, trim([11] & " " & [12] & " " & [13] & " " & [14] & " " & [15] & " " & [16] & " " & [17] & " " & [18] & " " & [19] & " " & [20]) AS Expr2
, trim([21] & " " & [22] & " " & [23] & " " & [24] & " " & [25] & " " & [26] & " " & [27] & " " & [28] & " " & [29] & " " & [30]) AS Expr3
, trim([31] & " " & [32] & " " & [33] & " " & [34] & " " & [35] & " " & [36] & " " & [37] & " " & [38] & " " & [39] & " " & [40]) AS Expr4
, trim([41] & " " & [42] & " " & [43] & " " & [44] & " " & [45] & " " & [46] & " " & [47] & " " & [48] & " " & [49] & " " & [50]) AS Expr5
, Trim([51] & " " & [52] & " " & [53] & " " & [54] & " " & [55] & " " & [56] & " " & [57] & " " & [58] & " " & [59] & " " & [60]) AS Expr6
FROM qryCrossIt;

Then concatinating the 6 seperate fields in a fourth query...

SELECT qryEndResult.UPC, qryEndResult.Expr1, qryEndResult.Expr2, qryEndResult.Expr3, qryEndResult.Expr4, qryEndResult.Expr5, qryEndResult.Expr6
, [qryEndResult]![Expr1] & " " 
& [qryEndResult]![Expr2] & " " 
& [qryEndResult]![Expr3] & " " 
& [qryEndResult]![Expr4] & " " 
& [qryEndResult]![Expr5] & " " 
& [qryEndResult]![Expr6] AS Expr14
FROM qryEndResult

Hope this resolves your issue.

 

[secondangel]

Thanks a lot ill give it a go.

Yeh crazy cds with like tiny tracks or interludes. Mostly folk or hiphop.



I figured id have to split the queries, will give it a try and let you know.

Thanks so much for your help

Ashley