I need help transforming a weird data structure into another weird data structure. If anyone has any ideas, I would really appreciate them.
THANKS!
Starting Table
Name Address Phone Wife
Doe,John 123 Easy St
Doe,John 555-555
Doe,John 666-666
Smith, Justin 444-444
Smith, Justin Margaret
Smith, Justin Jessica
Smith, Justin 333-333
NAME TYPE DATA
Doe, John Address 123 Easy St
Doe, John Phone 555-555
Doe, John Phone 666-666
Doe, John Wife ""
Smith, Justin Phone 444-444
Smith, Justin Phone 333-333
Smith, Justin Wife Margaret
Smith, Justin Wife Jessica
Smith, Justin Address ""
Notice that it creates a blank entry when there is no data present. Every name needs at least 1 of every type of data
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
The blank entries will require more work. The split rows is fairly straightforward:
--INSERT INTO newTable (name, type, data)
SELECT name,
CASE
WHEN Address <> '' THEN 'Address'
WHEN Phone <> '' THEN 'Phone'
WHEN Wife <> '' THEN 'Wife'
END AS type,
CASE
WHEN Address <> '' THEN Address
WHEN Phone <> '' THEN Phone
WHEN Wife <> '' THEN Wife
END AS data
FROM startingTable
Currently I have:
SELECT Name, "Address" as Type, Address as Data FROM table WHERE Address <> Null
UNION SELECT Name, "Phone" as Type, Phone as Data FROM table WHERE Phone <> Null
UNION SELECT Name, "Wife" as Type, Wife as Data FROM table WHERE Wife <> Null
This works except for the blank records. What I am hearing is that this can't be done with tables and queries. I'll give it a day, and then accept that if need be.
OK, please try this, which generates the missing-rows data right after extracting the existing data.
--INSERT INTO newTable (name, type, data)
SELECT name,
CASE
WHEN Address <> '' THEN 'Address'
WHEN Phone <> '' THEN 'Phone'
WHEN Wife <> '' THEN 'Wife'
END AS type,
CASE
WHEN Address <> '' THEN Address
WHEN Phone <> '' THEN Phone
WHEN Wife <> '' THEN Wife
END AS data
FROM startingTable
UNION ALL
SELECT name, CASE missingType
WHEN 'address' THEN 'Address'
WHEN 'phone' THEN 'Phone'
WHEN 'wife' THEN 'Wife'
END, ''
FROM (
SELECT name,
MAX(CASE WHEN address = '' THEN 0 ELSE 1 END) AS addressFound,
MAX(CASE WHEN phone = '' THEN 0 ELSE 1 END) AS phoneFound,
MAX(CASE WHEN wife = '' THEN 0 ELSE 1 END) AS wifeFound
FROM startingTable
GROUP BY name
HAVING MAX(CASE WHEN address = '' THEN 0 ELSE 1 END) = 0
OR MAX(CASE WHEN phone = '' THEN 0 ELSE 1 END) = 0
OR MAX(CASE WHEN wife = '' THEN 0 ELSE 1 END) = 0
) AS nameMissingTypes
INNER JOIN (
SELECT 'address' AS missingType UNION ALL
SELECT 'phone' UNION ALL
SELECT 'wife'
) AS missingTypes ON (missingType = 'address' AND addressFound = 0) OR
(missingType = 'phone' AND phoneFound = 0) OR (missingType = 'wife' AND wifeFound = 0)
Thanks to all of you. Scott Pletcher put me on the right track, but his code didn't work in Access. I ended up taking a different approach. I create a table like this:
Field1
------
Address
Phone
Wife
Then I did a SELECT StartingTable.Name, NewTable.Field1 FROM StartingTable, NewTable;
This gave me a results table with every combination of Name and Field1. I then did another query where I did a LEFT JOINed that table on both fields to the results table I had already, and it created the blank rows where there was no info.
Does that make sense?
Try this: Original table named TestUnion. Small table Types with three records containing the field Type. Your original query modified as below to reflect my table name:
SELECT [Name], "Address" as Type, Address as Data FROM testunion WHERE Address <> Null
UNION SELECT [Name], "Phone" as Type, Phone as Data FROM testunion WHERE Phone <> Null
UNION SELECT [Name], "Wife" as Type, Wife as Data FROM testunion WHERE Wife <> Null;
Running this query:
Select a.Name, a.Type, b.Data FROM (SELECT DISTINCT Name, Type From testunion, Types) as a LEFT JOIN qryTestUnion AS b ON a.Name=b.Name and a.Type=b.type
Produced the table in the code snippet. This eliminates a few of the 'steps' you took by using a sub-query with DISTINCT.
Business Accounts
Answer for Membership
by: capricorn1Posted on 2008-01-18 at 12:42:04ID: 20693957
you will need vba to do this using recordset