[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1852
  • Last Modified:

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
0
tippingpoint
Asked:
tippingpoint
  • 3
  • 2
1 Solution
 
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.

Jeff
0
 
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
0
 
tippingpointAuthor Commented:
It is working now as a query but I need to update my 'spare' field in my existing table.


0
 
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?

Jeff
0
 
tippingpointAuthor Commented:
Jeff,

COuld you look at my follow up. Its 500 as well:
http://www.experts-exchange.com/Databases/MS_Access/Q_21232650.html

thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now