Horizontal structure to Vertical Query

[Scott Duncan]

Hello,

We receive our Pricing Updates with the Item and all of the Prices as a
single record. See below:


Item Master Table Id Item 09/19/2001 01/01/2002 04/01/2002 07/01/2002
07/03/2002 09/01/2002 01/01/2003 01/01/2004 02/04/2004 07/01/2004 08/01/2004
01/01/2005 03/01/2005
1 082410020 $1.00 $0.60 $0.60 $0.60 $0.60 $0.60 $0.60 $0.60 $0.60
$0.00 $0.60 $0.00 $0.60



We need to create a Query [for the Price] that has the Item as the Header
and the prices listed vertically.

082410020 Price
09/19/2001 $1.00
01/01/2002 $0.60
04/01/2002 $0.60
07/01/2002 $0.60
07/03/2002 $0.60
And so on...

Any thoughts would be greatly appreciated.

TIA,

SD

 

[Duane Hookom]

I'm fairly certain you can use a union query. Do you have field names or are
the dates the field names (yuk)?

 

[Scott Duncan]

Duane,

Thank you for the response...

I have a Union Query that I am working with and it does work. It seems quite
slow. It could be that there
is such a large volume of data or, as you feared, the Dates are the Feild
Names.

Thanks again,

SD

I'm fairly certain you can use a union query. Do you have field names or
are the dates the field names (yuk)?

--
Duane Hookom
MS Access MVP

Hello,

We receive our Pricing Updates with the Item and all of the Prices as a
single record. See below:


Item Master Table Id Item 09/19/2001 01/01/2002 04/01/2002 07/01/2002
07/03/2002 09/01/2002 01/01/2003 01/01/2004 02/04/2004 07/01/2004
08/01/2004 01/01/2005 03/01/2005
1 082410020 $1.00 $0.60 $0.60 $0.60 $0.60 $0.60 $0.60 $0.60 $0.60
$0.00 $0.60 $0.00 $0.60



We need to create a Query [for the Price] that has the Item as the Header
and the prices listed vertically.

082410020 Price
09/19/2001 $1.00
01/01/2002 $0.60
04/01/2002 $0.60
07/01/2002 $0.60
07/03/2002 $0.60
And so on...

Any thoughts would be greatly appreciated.

TIA,

SD

 

[Duane Hookom]

Using "Union All" in your union query will generally run more quickly than
just "Union".

--
Duane Hookom
MS Access MVP

Duane,

Thank you for the response...

I have a Union Query that I am working with and it does work. It seems
quite slow. It could be that there
is such a large volume of data or, as you feared, the Dates are the Feild
Names.

Thanks again,

SD

I'm fairly certain you can use a union query. Do you have field names or
are the dates the field names (yuk)?

--
Duane Hookom
MS Access MVP

Hello,

We receive our Pricing Updates with the Item and all of the Prices as a
single record. See below:


Item Master Table Id Item 09/19/2001 01/01/2002 04/01/2002 07/01/2002
07/03/2002 09/01/2002 01/01/2003 01/01/2004 02/04/2004 07/01/2004
08/01/2004 01/01/2005 03/01/2005
1 082410020 $1.00 $0.60 $0.60 $0.60 $0.60 $0.60 $0.60 $0.60 $0.60
$0.00 $0.60 $0.00 $0.60



We need to create a Query [for the Price] that has the Item as the
Header and the prices listed vertically.

082410020 Price
09/19/2001 $1.00
01/01/2002 $0.60
04/01/2002 $0.60
07/01/2002 $0.60
07/03/2002 $0.60
And so on...

Any thoughts would be greatly appreciated.

TIA,

SD