Separation of Parent/Child Records

[Loretta Jean]

Is there a quick way in Access to take a record and its corresponding fields
and make separate records?

For instance I have a record
UserID Role1 Role 2 Role 3 and so forth....
jeandl abc123 cd342 mh987

I want to make separate the records so that what I have is a record for each
combination (with UserID repeated):

UserID Role
jeandl abc123
jeandl cd342
jeandl mh987

I have accomplished this using multiple append queries, but it's inefficient
and time-consuming. Is there a way to achieve this through a query or by
using VBA?

Thanks,
Loretta

P.S. I'm looking for the same solution when the data is in Excel...

 

[Lynn Trapp]

Select UserID, Role1 AS Role
From YourTable
UNION
Select UserID, Role2
From YourTable
UNION
Select UserID, Role3
From YourTable;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Security FAQ: http://support.microsoft.com/support/access/content/secfaq.asp

 

[Loretta Jean]

Thanks a bunch Lynn!

Select UserID, Role1 AS Role
From YourTable
UNION
Select UserID, Role2
From YourTable
UNION
Select UserID, Role3
From YourTable;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Security FAQ: http://support.microsoft.com/support/access/content/secfaq.asp

 

[Van T. Dinh]

Try the NormalizeDenormalize sample from RogersAccessLibrary:

http://www.rogersaccesslibrary.com/TableOfContents.asp