August 5, 2003 at 4:42 am
I am currently developing an Access2000 application with an ODBC connection to SQL Server 2000. I have a main table that stores names and addresses of Recording Companies, this has a one-to-many link with another table that stores the different musical genres they accept. Each record company can have as many genres attached to it as required. My problem however, is in outputting this data. I would like to be able to add each genre associated with a record company to a single field within the main table seperated by commas. This is probably easily achieved with a stored proceedure, but I don't really know where to begin. Any help would be greatly appreciated.
Thanks
Ben.
August 5, 2003 at 6:13 am
I think it makes more sense to do it on the client. Procs arent especially good at string handling, Access is.
Why wouldnt you normalize the relationship and store each genre as a separate row?
Andy
August 5, 2003 at 6:57 am
Thanks Andy,
I think the relationship is normalized, the genres are stored as follows:
Main Table:
EntryID Name Address Pcode etc.......
1 Abc Abc Abc
2 def def def
Genre Table:
GenreID EntryID MusicID
1 1 4
2 1 6
3 1 8
4 2 4
5 2 5
6 3 9
Music Type:
MusicID MusicName
1 Dance
2 Rock
3 Pop
4 Reggae etc......
EntryID.Main Linked to EntryID.GenreTable
GenreTable.MusicID linked to MusicType.MusicID
I'm trying to put the name of each genre associated with a record company into a single field in the main table (or copy of it) so the main table would have
EntryID Name Address Pcode Genres
1 abc abc abc "Blues, Soul"
2 def def def "Funk, Rock, Jazz"
3 hij hij hij "Metal, Grunge"
Perhaps I could write some VB code in access that reads in each genre associated with each record company and creates a string of them all separated by commas, this could then be written back to the appropriate record in the main table. Is this a right way of doing it? Is there a way of arranging the data so this isn't necessary? Any further suggestions most welcome.
Nice one,
Ben.
August 5, 2003 at 10:22 am
I've managed to do it, by simply creating an extra column in the main entry table called genres, and executing the following module on the form_close event:
Public Sub AddGenres()
Dim refID As Integer
Dim benConnect As ADODB.Connection
Dim rcTable As New ADODB.Recordset
Dim Entry As New ADODB.Recordset
Set benConnect = CurrentProject.Connection
rcTable.Open "recordcompanies", benConnect
Entry.Open "entry", benConnect, adOpenKeyset, adLockOptimistic
rcTable.MoveFirst
Entry.MoveFirst
Do While rcTable.EOF = False
Let refID = rcTable!EntryID
Entry.Find "EntryID = " & refID
Let Entry!genres = ""
rcTable.MoveNext
Entry.MoveFirst
Loop
rcTable.MoveFirst
Entry.MoveFirst
Do While rcTable.EOF = False
Let refID = rcTable!EntryID
Entry.Find "EntryID = " & refID 'rcTable!EntryID
If Entry!genres = "" Then
Let Entry!genres = rcTable!MusicType
Else
Let Entry!genres = Entry!genres & "/" & rcTable!MusicType
End If
rcTable.MoveNext
Entry.MoveFirst
Loop
MsgBox ("Genres Added Successfully")
End Sub
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply