SQL: merge fields into column dependant on grouping in another field and type

Merging fields into an empty colum dpending on a grouping number and parent/child type.

The best way to explain it is through the table below where the final outcome is what appears in the spare colum.

I can do this over about 6 or 7 different sql commands but when I seem to do it in one go it fails. Can anyone help?

The structure of spare is [fname (of parent in that group) & ' ' & lname (of parent in that group) 'group : ' & fname & lname]

The table has about 50,000 records so needs to be efficient.

table structure:

fname     lname     company      group       type          spare
aaa         zzz         111              0001        parent       aaa zzz group
bbb         yyy         222              0001        child          aaa zzz group : bbb yyy
ccc          xxx         333              0001        child          aaa zzz group : ccc xxx
ddd         www      444              0002        parent       ddd www group
eee         vvv         555              0002        child          ddd www group : eee vvv
fff           uuu         666             0002         child          ddd www group : fff uuu
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff TennessenAssistant Vice PresidentCommented:
Try this:

SELECT Table1.*, fname & ' ' & lname & ' group' AS newspare
FROM Table1
WHERE type = 'parent'
UNION SELECT t1a.*, t1b.fname & ' ' & t1b.lname & ' group : ' & t1a.fname & ' ' & t1a.lname AS spare
FROM Table1 t1a INNER JOIN Table1 t1b ON t1a.group = t1b.group
WHERE t1a.type = 'child' AND t1b.type = 'parent'

Obviously, you'll need to change Table1 to the actual name of your table. Let me know if that works for you.

tippingpointAuthor Commented:
I'm still doing testing on it but it seems ok so far...

Will it work for this? The first child record will be that of the parent

fname     lname     group       type          spare
aaa         zzz         0001        parent       aaa zzz group
aaa         zzz         0001        child          aaa zzz group : aaa zzz
bbb         yyy         0001        child          aaa zzz group : bbb yyy
ccc          xxx         0001        child          aaa zzz group : ccc xxx
ddd         www      0002        parent       ddd www group
ddd         www      0002        child          ddd www group : ddd www
eee         vvv         0002        child          ddd www group : eee vvv
fff           uuu         0002         child          ddd www group : fff uuu
tippingpointAuthor Commented:
It is working now as a query but I need to update my 'spare' field in my existing table.

Jeff TennessenAssistant Vice PresidentCommented:
Oh, OK. One word of caution: storing the value of spare in the table itself is technically a violation of third normal form. There can be performance-related reasons for denormalizing a relational database, but you have to make sure you're aware of the risks and the rewards and that the benefits outweight the costs.

That having been said, and assuming you still want to proceed, accomplishing this through an update query in Access can be tricky. What would be trivial in SQL Server can be prohibitively difficult in Access, because of how Access forces you to construct update queries, leaving them non-updateable. My suggestion: create a new table using the syntax below, then replace your existing table with the new one.

SELECT q.* INTO Table2
FROM (SELECT Table1.*, fname & ' ' & lname & ' group' AS spare
    FROM Table1
    WHERE type = 'parent'
    UNION SELECT t1a.*, t1b.fname & ' ' & t1b.lname & ' group : ' & t1a.fname & ' ' & t1a.lname AS spare
    FROM Table1 t1a INNER JOIN Table1 t1b ON t1a.group = t1b.group
    WHERE t1a.type = 'child' AND t1b.type = 'parent') q

Would that work for your situation?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tippingpointAuthor Commented:

COuld you look at my follow up. Its 500 as well:

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.