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.

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);
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"kidkosmo" <(E-Mail Removed)> wrote in message
news:47a509be-4275-43d2-b91a-(E-Mail Removed)...
> 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.

On Fri, 8 Jan 2010 18:21:15 -0800 (PST), kidkosmo <(E-Mail Removed)> wrote:

>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]

On Jan 8, 7:44*pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Fri, 8 Jan 2010 18:21:15 -0800 (PST), kidkosmo <kidkos...@yahoo.com> wrote:
> >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!

On Mon, 11 Jan 2010 08:12:00 -0800 (PST), kidkosmo <(E-Mail Removed)>
wrote:

>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.
--

John W. Vinson [MVP]

On Jan 11, 10:31*am, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Mon, 11 Jan 2010 08:12:00 -0800 (PST), kidkosmo <kidkos...@yahoo.com>
> wrote:
>
> >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.
> --
>
> * * * * * * *John W. Vinson [MVP]

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!