Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / June 2007

Tip: Looking for answers? Try searching our database.

This should be simple ...

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Intrinicity - 27 Jun 2007 12:13 GMT
I'm sure this would be a common requirement

I have a table (imported from excel) that is a pricing grid for
blinds. The header row at the top is widths(mm) and the header row
down the left is heights(mm). The data is the price for each
combination. The import process makes the widths the column names.

var1     600  800  1200 ...
300     $60   $80   $100
600     $80   $100  $120
900    $120  $140  $160
.
.
all I want to do is to take that table and make it flattened, like
this:

height  width  price
300      600    $60
300      800    $80
300    1200    $100
600     600     $80
600     800     $100
600    1200    $120
900     600     $120
900     800     $140
900    1200    $160

I can't think how to do this in a query - I could take the flat
version and create a crosstab to go the other way, but not in this
direction. Any clues - I don't want to change too much about the way
the imported excel file looks because I have no control over that (I
already discard miscellaneous headings and junk before I import it)

Hope you can help
John Spencer - 27 Jun 2007 12:49 GMT
You could use a normalizing union query if there are not too many widths.

Something like the following
SELECT Height, 600 as Width, [600] as Price
FROM The Table
UNION ALL
SELECT Height, 800 as Width, [800] as Price
FROM The Table
UNION ALL
SELECT Height, 1200 as Width, [1200] as Price
FROM The Table

Then you could use the Union query as the source to populate a table or just
use the Union query as if it were the table.  Using the query itself might
be slow since there won't be any indexes available, but it may be fast
enough for you.

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> I'm sure this would be a common requirement
>
[quoted text clipped - 30 lines]
>
> Hope you can help
Allen Browne - 27 Jun 2007 12:52 GMT
The direction you are headed is spot on.

If this is a one-off import, it's probably easiest to create the target
table with its 3 fields, and then use an Append query to append one column
at a time from the original spreadsheet. (Append on Query menu, in query
design.)

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I'm sure this would be a common requirement
>
[quoted text clipped - 30 lines]
>
> Hope you can help
David Cox - 27 Jun 2007 13:42 GMT
i did a google groups search on swap rows and columns and *excel* and the
first response included:
'Mark the range of your data. Copy it all. Choose another sheet. Paste
Special>Transpose '

> I'm sure this would be a common requirement
>
[quoted text clipped - 30 lines]
>
> Hope you can help
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.