Creating multiple records from a single record

[kidkosmo]

Hi, Gang,

I'm a bit perplexed with how I can perform this task. I have a set of
data that is arranged such as this:

Function e_p_date1 e_p_date2 e_p_date3
Training1 1/1/2010 1/2/2010 1/3/2010
Training2 1/4/2010 [null] [null]

I want to turn that into something more like below in order to add
into my crosstab query to calculate the number of events per function
(currently the crosstab is only calculating one training).

Function e_p_date
Training1 1/1/2010
Training1 1/2/2010
Training1 1/3/2010
Training2 1/4/2010

Any help would be greatly appreciated.

 

[Arvin Meyer [MVP]]

You can do it with a series of append queries.

INSERT INTO tblMyTable( e_p_date)
SELECT e_p_date3
FROM tblMyTable
WHERE e_p_date3 Is Not Null);

 

[John W. Vinson]

Hi, Gang,

I'm a bit perplexed with how I can perform this task. I have a set of
data that is arranged such as this:

Function e_p_date1 e_p_date2 e_p_date3
Training1 1/1/2010 1/2/2010 1/3/2010
Training2 1/4/2010 [null] [null]

I want to turn that into something more like below in order to add
into my crosstab query to calculate the number of events per function
(currently the crosstab is only calculating one training).

Function e_p_date
Training1 1/1/2010
Training1 1/2/2010
Training1 1/3/2010
Training2 1/4/2010

Any help would be greatly appreciated.

A "Normalizing Union" query is the ticket here. You need to go into the SQL
window to build it, the query grid isn't up to the task!

SELECT [Function], [e_p_date1] AS [e_p_date]
FROM yourtable
WHERE [e_p_date1] IS NOT NULL
UNION ALL
SELECT [Function], [e_p_date2]
FROM yourtable
WHERE [e_p_date2] IS NOT NULL
UNION ALL
SELECT [Function], [e_p_date3]
FROM yourtable
WHERE [e_p_date3] IS NOT NULL;

This is in fact a more properly normalized structure for your table than what
you currently have.

 

[kidkosmo]

Hi, Gang,

I'm a bit perplexed with how I can perform this task.  I have a set of
data that is arranged such as this:

Function     e_p_date1      e_p_date2     e_p_date3
Training1      1/1/2010         1/2/2010       1/3/2010
Training2      1/4/2010         [null]            [null]

I want to turn that into something more like below in order to add
into my crosstab query to calculate the number of events per function
(currently the crosstab is only calculating one training).

Function     e_p_date
Training1     1/1/2010
Training1     1/2/2010
Training1     1/3/2010
Training2     1/4/2010

Any help would be greatly appreciated.

A "Normalizing Union" query is the ticket here. You need to go into the SQL
window to build it, the query grid isn't up to the task!

SELECT [Function], [e_p_date1] AS [e_p_date]
FROM yourtable
WHERE [e_p_date1] IS NOT NULL
UNION ALL
SELECT [Function], [e_p_date2]
FROM yourtable
WHERE [e_p_date2] IS NOT NULL
UNION ALL
SELECT [Function], [e_p_date3]
FROM yourtable
WHERE [e_p_date3] IS NOT NULL;

This is in fact a more properly normalized structure for your table than what
you currently have.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

The union query works GREAT! However, I am now having an issue with
duplicate records. In the example I provided, the e_p_dates are
different dates staff performs a follow up phone call; therefore, the
next time I import the data and parse them into different rows with
the union query, I can duplicate previous records. Any ideas on a
quick and dirty way I can enumerate the duplicate records and only
keep one of them?

Thanks!

 

[John W. Vinson]

The union query works GREAT! However, I am now having an issue with
duplicate records. In the example I provided, the e_p_dates are
different dates staff performs a follow up phone call; therefore, the
next time I import the data and parse them into different rows with
the union query, I can duplicate previous records. Any ideas on a
quick and dirty way I can enumerate the duplicate records and only
keep one of them?

If you change each instance of UNION ALL to just UNION Access will dedup the
data for you.

However, your present data structure is *wrong* and is the source of your
difficulties! Properly normalizing your table to be tall-thin rather than
wide-flat will go a long way to make your task easier.

 

[kidkosmo]

If you change each instance of UNION ALL to just UNION Access will dedup the
data for you.

However, your present data structure is *wrong* and is the source of your
difficulties! Properly normalizing your table to be tall-thin rather than
wide-flat will go a long way to make your task easier.

I agree the present data structure is wrong; unfortunately, that data
is being provided from an outside Oracle source to which I cannot
affect. I'll drop the ALL and see where that gets me.

Thanks for the assist!