Re: Make Table Query

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 11/14/04 

Date: Sun, 14 Nov 2004 09:31:13 -0500

What you could do would be to add a table (temporarily) that contains the number
1 to whatever maximum number of books you have. And then use that table in a
query with the excel.

CountTable
Counter (field) with values 1 to N

Using that in append query (note that there is NO JOIN specified between the two
tables) you can generate all the records. You would need to run one query for
each column in your Excel Table. If you wanted to do this all in one query, it
would be more difficult since you would have to normalize your Excel columns
somehow. Possibly using a UNION query?

NOTE that all these queries are untested AIR CODE and may not work

INSERT INTO NewTable([BookId],[LocationID])
SELECT E.BookId, 1 as LocValue
FROM ExcelTable as E, CountTable as C
WHERE E.Basement <= C.Counter

Possible Union query, that could be used to normalize the data in the excel ***

SELECT BookID, Basement, 1 as LocValue
FROM ExcelFile
UNION ALL
SELECT BookID, UpStairs, 2
FROM ExcelFile
UNION ALL
SELECT BookID, Warmley, 3
FROM ExcelFile

Possible Combined:

INSERT INTO NewTable([BookId],[LocationID])
SELECT E.BookId, E.LocValue
FROM [SELECT BookID, Basement, 1 as LocValue
      FROM ExcelFile
      UNION ALL
      SELECT BookID, UpStairs, 2
      FROM ExcelFile
      UNION ALL
     SELECT BookID, Warmley, 3
     FROM ExcelFile]. as E, CountTable as C
WHERE E.Basement <= C.Counter

Edgar Thoemmes wrote:
>
> Hi
>
> I have an excel file set out like:
>
> BookID Basement Upstairs Warmley
> 211 5 1 0
> 2555 2 0 1
> 321 0 0 2
>
> The numbers listed under basement, upstairs and warmley are number of stock
> in that location.
>
> What I want to do is crate a new table with the results so it would like this
>
> Book ID LocationID
> 211 1
> 211 1
> 211 1
> 211 1
> 211 1
> 211 3
>
> So that all books have a seperate record for each location. I have the
> locationID stored in tblLocations.
>
> Can anyone help me acheive this with a query?
>
> Thanks

Quantcast