Union Query to Reformat Results 1

[air1access]

I have seen this trick a while back but can't remember how to do it...!

I have a table with the data like this:

Admin Ops-141 Escalated Member Status Change - 141.12
Admin Ops-141 HouseCalls Business Continuity Plan - 141.11
Admin Ops-141 HouseCalls Small County Analysis - 141.13
Analytics-132 Create Pseudoclaim for HEDIS Gap Closure - 132.16
Analytics-132 HC External Customers Coding and Billing Review - 132.20
Analytics-132 HC External Referrals Processing - 132.18
Analytics-132 Inpatient Members for Manual Dialing - 132.13

I'd like for it to look like this:

Admin Ops-141
________________Escalated Member Status Change - 141.12
________________HouseCalls Business Continuity Plan - 141.11
________________HouseCalls Small County Analysis - 141.13
Analytics-132
________________Create Pseudoclaim for HEDIS Gap Closure - 132.16
________________HC External Customers Coding and Billing Review - 132.20
________________HC External Referrals Processing - 132.18
________________Inpatient Members for Manual Dialing - 132.13

I know it takes 2 queries. one I have like this:

SELECT Team, "" AS [Process Name & ID] FROM tbl_Inventory_Report

But I can't remember how to build the 2nd query and then build them in an Union query...

Any suggestions? Ideas?

Thanks in advance..!!!

 

[dhookom]

I can't determine from you post if the values are all one field or more. Please use TGML pre tags to format your data possibly like:
[pre][FieldAName] [FieldBName] [FieldCName]
Admin Ops-141 Escalated Member Status Change - 141.12
Admin Ops-141 HouseCalls Business Continuity Plan - 141.11
Admin Ops-141 HouseCalls Small County Analysis - 141.13
Analytics-132 Create Pseudoclaim for HEDIS Gap Closure - 132.16
Analytics-132 HC External Customers Coding and Billing Review - 132.20
Analytics-132 HC External Referrals Processing - 132.18
Analytics-132 Inpatient Members for Manual Dialing - 132.13[/pre]

Also, is there a primary key field or something that determines the order?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[air1access]

Ok - I Hope this helps..!

Here is how it looks now. Just 2 fields at the moment. No primary key, but I can add one if need be.

Team[tab][tab][tab][tab][tab]Process
Admin Ops-141[tab][tab]Escalated Member Status Change - 141.12
Admin Ops-141[tab][tab]HouseCalls Business Continuity Plan - 141.11
Admin Ops-141[tab][tab]HouseCalls Small County Analysis - 141.13
Analytics-132[tab][tab]Create Pseudoclaim for HEDIS Gap Closure - 132.16
Analytics-132[tab][tab]HC External Customers Coding and Billing Review - 132.20
Analytics-132[tab][tab]HC External Referrals Processing - 132.18
Analytics-132[tab][tab]Inpatient Members for Manual Dialing - 132.13


This is how I'd like for it to look.
I found a sample database out there years ago that used 2 queries - from the same table...
Formatted them in such a way that when they were combined in a Union query - the results showed as below.

Team[tab][tab][tab][tab][tab]Process
Admin Ops-141
[tab][tab][tab][tab][tab][tab][tab]Escalated Member Status Change - 141.12
[tab][tab][tab][tab][tab][tab][tab]HouseCalls Business Continuity Plan - 141.11
[tab][tab][tab][tab][tab][tab][tab]HouseCalls Small County Analysis - 141.13
Analytics-132
[tab][tab][tab][tab][tab][tab][tab]Create Pseudoclaim for HEDIS Gap Closure - 132.16
[tab][tab][tab][tab][tab][tab][tab]HC External Customers Coding and Billing Review - 132.20
[tab][tab][tab][tab][tab][tab][tab]HC External Referrals Processing - 132.18
[tab][tab][tab][tab][tab][tab][tab]Inpatient Members for Manual Dialing - 132.13

 

[Andrzejek]

So when you will figure out how to do it, then what?
Where do you want to present this data? In the grid? In the report? Write to a file? What's the final goad here?


---- Andy

There is a great need for a sarcasm font.

 

[air1access]

The union query will be the final goal...
I'll copy it and paste it into excel...

 

[Andrzejek]

Why not just simply ask for:
[tt]
Select Team, Process
From tbl_Inventory_Report
Order By 1, 2[/tt]

and then go down the record set and show in column A Team, and (as long as Team is the same) place Process in column B.
When Team changes, repeat the process until the end of record set.

No fancy query required.



---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

This UNION query should get you close:

SELECT tbl_Inventory_Report.Team, tbl_Inventory_Report.Team AS Column1, " " AS Column2
FROM tbl_Inventory_Report
GROUP BY tbl_Inventory_Report.Team, tbl_Inventory_Report.Team, " "
UNION
SELECT Team,"", Process
FROM tbl_Inventory_Report
ORDER BY 1,3;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[air1access]

Thanks Duane..!
That's exactly what I was lookin' for. Awesome..!! \m/