Simplify the records in

[Guest]

i have data in a table as below, am displaying 4 columns only, the table has
20 columns. i.e. for each phone number i have a corresponding date in columns
but is in a separate record so i have 5 time record entries as that of total
number of phone numbers

Phone Number Status Inbound GSM Inbound SMS
3002005411.00 Active 02-Jul-07
3002005411.00 Active 03-Jul-07
3002005474.00 Active 08-Jul-07
3002005474.00 Active 10-Jul-07

I want to convert it to a table which can give me following.

Phone Number Status Inbound GSM Inbound SMS
3002005411.00 Active 03-Jul-07 02-Jul-07
3002005474.00 Active 10-Jul-07 08-Jul-07

i.e. against each phone number, it can give me value of each column but in
one record

 

[John W. Vinson]

i have data in a table as below, am displaying 4 columns only, the table has
20 columns. i.e. for each phone number i have a corresponding date in columns
but is in a separate record so i have 5 time record entries as that of total
number of phone numbers

Phone Number Status Inbound GSM Inbound SMS
3002005411.00 Active 02-Jul-07
3002005411.00 Active 03-Jul-07
3002005474.00 Active 08-Jul-07
3002005474.00 Active 10-Jul-07

I want to convert it to a table which can give me following.

Phone Number Status Inbound GSM Inbound SMS
3002005411.00 Active 03-Jul-07 02-Jul-07
3002005474.00 Active 10-Jul-07 08-Jul-07

i.e. against each phone number, it can give me value of each column but in
one record

If I understand aright you have five sets of fields and you want to "unravel"
this wide-flat table into a tall-thin one? What's the .00 - is it intended to
be a separate piece of information about 3002005411 or is it an integral part
of it? If there are multiple records for 3002005411.00 how can you determine
which Inbound SMS date goes with which Inbound GSM date?

You *may* be able to do this with a Normalizing Union query based on a self
join. Based on *JUST* the data you posted, without concerns about multiple
ambiguous date matches and without knowing the names of the 16 other fields,
something like:

SELECT A.[Phone Number], A.[Status], A.[Inbound GSM], B.[Inbound SMS]
FROM yourtable AS A
INNER JOIN yourtable AS B
ON [A].[Phone Number] = .[Phone Number]
AND [A].[Status] = .[Status]
WHERE A.[Inbound SMS] IS NULL
AND B.[Inbound GSM] IS NULL

UNION ALL

SELECT A.[Phone Number 2], A.[Status 2], A.[Inbound GSM 2], B.[Inbound SMS 2]
FROM yourtable AS A
INNER JOIN yourtable AS B
ON [A].[Phone Number 2] = .[Phone Number 2]
AND [A].[Status 2] = .[Status 2]
WHERE A.[Inbound SMS 2] IS NULL
AND B.[Inbound GSM 2] IS NULL

UNION ALL

<etc through all four repeats>


John W. Vinson [MVP]