Query to Convert table

[dhborchardt]

Is there a way to move the admit and discharge dates into two columns?
In the first example Bob has a record for the Admit date and another record for the discharge date.
I would like to rebuild the table with the admit and discharge date on one record for each name.
I would like the result to look like the second example.



EXAMPLE 1:

Name	Date	Adm/Disc
Bob	10/1/2011	Admit
Bob	5/5/2012	Discharge
Doug	10/1/2011	Admit
Doug	5/5/2012	Discharge
Joe	10/1/2011	Admit
Joe	5/5/2012	Discharge
Phil	10/1/2011	Admit
Phil	5/5/2012	Discharge
Tim	10/1/2011	Admit
Tim	5/5/2012	Discharge
EXAMPLE2
Name	Admit	Discharge
Bob	10/1/2011	5/5/2012
Doug	10/2/2011	5/6/2012
Joe	10/3/2011	5/7/2012
Phil	10/4/2011	5/8/2012
Tim	10/5/2011	5/9/2012

[JoeM]

This query should return the data the way you want.
Note that I assumed a table name of "MyTable". Basically, you add "MyTable" to your query twice, and join both instances together (the second one gets and alias with a "_1" on the end of the table name).

SELECT 
    MyTable.Name, 
    [MyTable]![Date] AS Admit, 
    [MyTable_1]![Date] AS Discharge
FROM 
    MyTable 
INNER JOIN 
    MyTable AS MyTable_1 
ON 
    MyTable.Name = MyTable_1.Name
WHERE 
    (((MyTable.[Adm/Disc])="Admit") 
    AND ((MyTable_1.[Adm/Disc])="Discharge"));

[dhborchardt]

Thanks, this works great if the data is consistent. That is that each name has one admit and one discharge.
Problem is some of them will have:
1. An admit but no discharge.
2. A discharge but no admit.
3. Two admits and one discharge.
etc.

In the case where we have two admits I would want to take the later one. Same with two discharges and one admit.
for simplicity I did not include these in the first example. see new example

Name	Date	Adm/Disc
Bob	10/1/2011	Admit
Bob	5/5/2012	Discharge
Doug	10/1/2011	Admit
Doug	5/5/2012	Discharge
Joe	10/1/2011	Admit
Joe	5/5/2012	Discharge
Phil	10/1/2011	Admit
Phil	5/5/2012	Discharge
Tim	10/1/2011	Admit
Tim	5/5/2012	Discharge
Steve	10/1/2011	Admit
Luke	5/5/2102	Discharge
John	10/1/2011	Admit
John	5/5/2012	Discharge
John	7/7/2012	Discharge

[JoeM]

for simplicity I did not include these in the first example.

I am sure you can see now why that is generally not a good idea! If you want a soloution that will work for your scenario, it is important to accurately depict your scenario.
I also don't quite understand someone who has not been admitted can be discharged, but I guess that is besides the point.

Here is one way you can do, using a series of queries (you could try to nest some of them, if you really feel ambitious).

1. Create a query of all your members.
Create an Aggregate Query, only adding the Name field and grouping on it so that each member only shows up in the query once.

2. Create a query of the latest Admit for each member.
Create an Aggregate Query, adding all three fields. Group on the Name and Adm/Disc fields, and take the Max value of your Date field. Add Criteria to your Adm/Disc field to only return "Admit" records.

3. Create a query of the latest Discharge for each member.
Create an Aggregate Query, adding all three fields. Group on the Name and Adm/Disc fields, and take the Max value of your Date field. Add Criteria to your Adm/Disc field to only return "Discharge" records.

4. Create the Results Query
Now, create a new query adding in the previous three queries. You will join them on the Name field, doing a Left Join from Query1 to Query 2, and similarly doing a Left Join from Query1 to Query3. Return the Name from Query1, the Date from Query2 (for your Admit), and the Date from Query3 (as your Discharge).

That should handle all your scenarios.