Combine record values 2

[BrenoAguiar]

I have a table that looks like this:

ID Name unit
1 john 104
2 Mary 301
2 Mary 302
3 Carl 405
3 Carl 407
3 Carl 409

And I was wondering If I can combine the "unit" field values, in another table/query that would show:

ID Name unit
1 john 104
2 Mary 301, 302
3 Carl 405, 407, 409

Any thouhgts?

 

[lespaul]

FAQ701-3499

there are also several other threads that present different approaches.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Knicks]

Looks like you have a design problem, one of the tenets of normalization has been broken.

You should have 1 table with Names and ID and another table with their related unit records. You would need to link these 2 tables via the ID field most likely, putting an ID field in the related table with Number data type.

Then you could easily view your data either through a query using both tables or a form with a subform using ID to link. To go about fixing your situation you could make table or copy table of the original and with the new table keep only the data that is not being repeated, so far that would be Units and keep the ID field to LINK. Then you can go about setting up linked tables on the ID field and setting up referential integrity.

 

[BrenoAguiar]

Thanks KNicks and Leslie. DUe to the circustances both tables talk one to many in both ways, what had to do with the design estabilished. BUt I liked the Idea creating a temp table and generate an autonumber field that would work for what I need now!

Thanks a BUnch!