Consolidate data into new record format

[jpkeller]

I have data in a table that currently looks like what is in blue. I would like to create a new table with the data looking like what is in red. Any help is greatly appreciated.

INFO____________DESCRIPTION____________GROUPING
Smith, Harry.....................MD.......................................1
123 Main St......................Street...................................1
Internal Medicine...............Specialty................................1
Jones, Tom........................MD.........................................2
456 State St.....................Street.....................................2
Pulmonary Medicine.............Specialty.................................2

Change to Data like this:

RecordID____ MD________________Street____________Specialty
1..............Smith, Harry...............123 Main St.............Internal Medicine
2..............Jones, Tom.................456 State St...........Pulmonary Medicine

 

[Louie]

Assuming Table1 and table2 are already created and you don't mind executing 3 queries the following will work:

INSERT INTO Table2 ( RecordID, MD )
SELECT Table1.GROUPING, First(Table1.Info) AS FirstOfInfo
FROM Table1
GROUP BY Table1.GROUPING;

UPDATE Table2 INNER JOIN Table1 ON Table2.RecordID = Table1.GROUPING
SET Table2.Street = [Info]
WHERE (Table1.DESCRIPTION="Street");

UPDATE Table2 INNER JOIN Table1 ON Table2.RecordID = Table1.GROUPING
SET Table2.Specialty = [Info]
WHERE (Table1.DESCRIPTION="Specialty");

 

[Jon K]

To display the data like what is in red, you can use a crosstab query.

Query1 (Type/Paste in the SQL View of a new query, replacing with the correct table name):-
TRANSFORM First([INFO])
SELECT [GROUPING]
FROM [TableName]
GROUP BY [GROUPING]
PIVOT [DESCRIPTION] IN (MD, Street, Specialty);


To create a new table, you can build a make-table query from Query1:-
SELECT Query1.* INTO [NewTableName]
FROM Query1;
.

 

[jpkeller]

Thanks

Thank you both for your reply. Jon, the query you sent works perfectly.

Thanks much!