Can I create many entries in one record's field?

[DanEaton]

I am creating a catalog database and would like some advice. In every catalog there could be many different product types (I am well aware of the one-to-many relationship). So in my catalog table I have a field for ProductID so that the two tables are linked. I was wondering if there is some way to store like five productID's in one record. For example, say I have a Honda catalog that contains info about chainsaws, lawnmowers, cars, and motorcycles, lets say with productID's 1,2,3,4 respectively. Could I store this the following way? Could I write code to store it the following way?

CatalogName CatalogDate ProductID Format Filed Under
Honda 2001 02/02/01 1,2,3,4 CD Honda

I know that four seperate records could be made to remedy this, but that would not be totally accurate of each catalog and would waste space. Thanks alot.

-Dan

 

[AWithers]

One way around this is to create a temporary table and insert the concatenated id numbers into it via a recordsets and loops

here is something I did along a similar vein, hope you can follow the logic, In my case I have many competotirs/pipelineid

Hope it helps

Andy

Private Sub CreateCompetitorsList_Click()

Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim comps As String ' string to contain list of competitors


Set db = CurrentDb
'first of all insert list of pipelineids into table tbl competitors
db.Execute ("insert into tblCompetitors (pipelineid) select distinct pipelineid from dbo_pipelinecompetitors&quot

Set rs = db.OpenRecordset("select distinct pipelineid from dbo_pipelinecompetitors&quot
'outer loop containing list of pipeline ids
Do Until rs.EOF = True
'for each pipelineid create a string of competitor names
comps = ""

Dim sql As String
sql = "select distinct companyName from dbo_pipelinecompetitors inner join dbo_company on dbo_pipelinecompetitors.companyidcompetitor = dbo_company.companyid where Pipelineid =" & rs!pipelineid & ""
'select list of company names for current pipelineid
Set rs1 = db.OpenRecordset(sql)
'inner loop to create a concatenated string of competitors/pipelineid

Do While rs1.EOF = False
comps = comps + "," + rs1!companyname
rs1.MoveNext
Loop
If Len(comps) > 0 Then
'update the field competitors using the string create but trim of leading comma
db.Execute ("Update tblcompetitors set Competitors = '" + Right(comps, Len(comps) - 1) + "' where pipelineid = " & rs!pipelineid & "&quot

Else
'dont bother with update
End If
'move to next pipelineid
rs.MoveNext
Loop

'release the memory
Set rs = Nothing
Set rs1 = Nothing
Set db = Nothing

End Sub

 

[krsherm]

NO NO NO. Intermediate simplicity can only be achieved through short-term complexity. Normalization is not a suggestion, it is an imperative. You may think that you are making a task easier by violating normalization rules but you are really making every subsequent task that you may want to accomplish more difficult (and slower... and more prone to error). Do not concern yourself with "wasted space"... concern your self with performance, ease-of-use, and maintainability. Now is the time to normalize!

Rock On!

Kevin