Horizontal structure to Vertical Query

  • Thread starter Thread starter Scott Duncan
  • Start date Start date
S

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
 
I'm fairly certain you can use a union query. Do you have field names or are
the dates the field names (yuk)?
 
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
Duane Hookom said:
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


Scott Duncan said:
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
Click to expand...
Click to expand...
 
Using "Union All" in your union query will generally run more quickly than
just "Union".

--
Duane Hookom
MS Access MVP


Scott Duncan said:
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
Duane Hookom said:
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


Scott Duncan said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top