Make Table Query

G

Guest

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
 
J

John Spencer (MVP)

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 sheet

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
 
G

Guest

Hi

Thanks for the response, i have tried your approach but I am having problems
with it. This is what i have done so far. I imported the excel file into:

tblstockdetails1(BookID, Basement, Upstairs, Warmley)

The temp table is set up as follows:


tbltemp(ID, BookID, LocationID)

The SQL for the query is:

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

When I run this query I get a prompt: Do you want to append 0 rows.!!!

I am not very knowledgable in SQL and have tried to figure this one out but
have hit a brick wall.

Can anyone help?

Thanks


John Spencer (MVP) said:
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 sheet

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 said:
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
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Did you add the table of numbers as I suggested?

TblNumbers
FldCounter (a number field) which contains the numbers from 1 to 10 (or
whatever is the largest number of books you have) One record for each number.
So if the largest number of books is ten, you will have ten records
(1,2,3,4,5,6,7,8,9,10). Each record will have only one of the values. If you
have that table, then try the following modification to your SQL statement.

INSERT INTO tbltemp([BookId],[LocationID])
SELECT E.BookId, E.LocValue
FROM [SELECT BookID, Basement, 1 as LocValue
FROM tblstockdetails1
UNION ALL
SELECT BookID, UpStairs, 2
FROM tblstockdetails1
UNION ALL
SELECT BookID, Warmley, 3
FROM tblstockdetails1]. as E, tblNumbers as C
WHERE E.Basement <= C.FldCounter


Edgar said:
Hi

Thanks for the response, i have tried your approach but I am having problems
with it. This is what i have done so far. I imported the excel file into:

tblstockdetails1(BookID, Basement, Upstairs, Warmley)

The temp table is set up as follows:

tbltemp(ID, BookID, LocationID)

The SQL for the query is:

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

When I run this query I get a prompt: Do you want to append 0 rows.!!!

I am not very knowledgable in SQL and have tried to figure this one out but
have hit a brick wall.

Can anyone help?

Thanks

John Spencer (MVP) said:
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 sheet

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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
E

Edgar Thoemmes

Thanks John this worked perfectly

--
Thanks

Edgar Thoemmes
ethoemmes dot blueyonder dot co dot uk
-----------------------------------------------------------------




John Spencer (MVP) said:
Did you add the table of numbers as I suggested?

TblNumbers
FldCounter (a number field) which contains the numbers from 1 to 10 (or
whatever is the largest number of books you have) One record for each number.
So if the largest number of books is ten, you will have ten records
(1,2,3,4,5,6,7,8,9,10). Each record will have only one of the values. If you
have that table, then try the following modification to your SQL statement.

INSERT INTO tbltemp([BookId],[LocationID])
SELECT E.BookId, E.LocValue
FROM [SELECT BookID, Basement, 1 as LocValue
FROM tblstockdetails1
UNION ALL
SELECT BookID, UpStairs, 2
FROM tblstockdetails1
UNION ALL
SELECT BookID, Warmley, 3
FROM tblstockdetails1]. as E, tblNumbers as C
WHERE E.Basement <= C.FldCounter


Edgar said:
Hi

Thanks for the response, i have tried your approach but I am having problems
with it. This is what i have done so far. I imported the excel file into:

tblstockdetails1(BookID, Basement, Upstairs, Warmley)

The temp table is set up as follows:

tbltemp(ID, BookID, LocationID)

The SQL for the query is:

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

When I run this query I get a prompt: Do you want to append 0 rows.!!!

I am not very knowledgable in SQL and have tried to figure this one out but
have hit a brick wall.

Can anyone help?

Thanks

John Spencer (MVP) said:
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 sheet

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Total based on status in crosstab 7
Bringing conditional data from another Worksheet 2
Compare query with table. Output based on portion of field of table. 2
Complicated nested row formula - Multiple Range Compare 2
Concatenating values from a many relation 2
Query results and display nulls? 4
Subquery not integrating to query to display previous sum of quantities 3
cross tab query outer join 1

Top