Autopopulate a table based on entries in another table

[Guest]

Hello all. I have a question that's been ragging my brains for a while.

I am working on a project where I am scanning books with numbered pages.
The books are from 1941 to present. Each year has a certain number of books
and each book has 500 pages each although there are some books have 250
pages. The pages are numbered consecutively from 1 to the last page number
generated for that year. Most years have from 90,000 pages to 120,000 pages
all divided into books of mostly 500 pages each.

I did an inventory of the books (so far I have inventoried about 28,000
books) and created the following table:
BookID
BookNum (each book will be numbered from 1 to whatever the last number book
we have)
BeginingPageNum (the begining page number in that book)
EndingPageNum (the ending page number in that book)

I created another table that I intend to generate the page numbers for all
of the books. The table looks like this:
PageID
BookNum
PageNum

What I would like to do is create a procedure or something that will read
the book number, first page number and last page number from each entry in
the first table and automatically populate the second table with the book
number and the individual pages that should be in that book.

For example, book one will have have a page count of 500 pages starting at 1
and going to 500. How can I then have the book number and a page number from
1 to 500 inserted into the second table? This procedure should run for all
of the books and fill the table with the book number and associated pages.

After the procedure is run, I would like to open a form for the book with a
subform for the pages (I will add additional fields).

Any help you provide will be greatly appreciated. I'm thinking that it's
easy to autopopulate a table but nothing I've tried seem to work.

 

[Allen Browne]

First thing is to give Access a list of numbers from 1 to the most number of
pages any book could have.

1. Create a table with one field named CountID, type Number, marked as
primary key. Field Properties would be Long Integer for size, and nothing
for Default Value. Save the table as tblCount. Close.

2. Run this code to populate the table. Change the 1000 if you foresee any
books that could have more than 1000 pages.

Function MakeData()
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("tblCount")
For lng = 1 To 1000
rs.AddNew
rs![CountID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function

3. Create a query that uses both tblCount and your original table. If you
see any line joining the 2 tables in the upper pane of table design, delete
the line. Since there is no join, this gives you every possible combination
of the records in the tables (called a Cartesian Product.)

4. Drag the BookNum field into the grid.
In the next column, in the field row, enter:
PageNum: tblCount.CountID
In the Criteria row under this field, enter:
Between BeginingPageNum And EndingPageNum
For each book, this limits the pages to the correct range.

5. Change the query to an Append query (Append on Query menu.) Access will
ask what table to append to.

6. Run the query.

It would also have been possible to use AddNew code for the entire task, but
I suspect the Append query approach will be better.

 

[Guest]

Allen:

Thanks for the response. I think that your approach is a very intriguing
one. However whenever I run the append query, I get the following error
message: "Data type mismatch in criteria expression".

Can you let me know if I'm doing anything wrong? I followed your
instructions as you stated I should.

First thing is to give Access a list of numbers from 1 to the most number of
pages any book could have.

1. Create a table with one field named CountID, type Number, marked as
primary key. Field Properties would be Long Integer for size, and nothing
for Default Value. Save the table as tblCount. Close.

2. Run this code to populate the table. Change the 1000 if you foresee any
books that could have more than 1000 pages.

Function MakeData()
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("tblCount")
For lng = 1 To 1000
rs.AddNew
rs![CountID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function

3. Create a query that uses both tblCount and your original table. If you
see any line joining the 2 tables in the upper pane of table design, delete
the line. Since there is no join, this gives you every possible combination
of the records in the tables (called a Cartesian Product.)

4. Drag the BookNum field into the grid.
In the next column, in the field row, enter:
PageNum: tblCount.CountID
In the Criteria row under this field, enter:
Between BeginingPageNum And EndingPageNum
For each book, this limits the pages to the correct range.

5. Change the query to an Append query (Append on Query menu.) Access will
ask what table to append to.

6. Run the query.

It would also have been possible to use AddNew code for the entire task, but
I suspect the Append query approach will be better.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello all. I have a question that's been ragging my brains for a while.

I am working on a project where I am scanning books with numbered pages.
The books are from 1941 to present. Each year has a certain number of
books
and each book has 500 pages each although there are some books have 250
pages. The pages are numbered consecutively from 1 to the last page
number
generated for that year. Most years have from 90,000 pages to 120,000
pages
all divided into books of mostly 500 pages each.

I did an inventory of the books (so far I have inventoried about 28,000
books) and created the following table:
BookID
BookNum (each book will be numbered from 1 to whatever the last number
book
we have)
BeginingPageNum (the begining page number in that book)
EndingPageNum (the ending page number in that book)

I created another table that I intend to generate the page numbers for all
of the books. The table looks like this:
PageID
BookNum
PageNum

What I would like to do is create a procedure or something that will read
the book number, first page number and last page number from each entry in
the first table and automatically populate the second table with the book
number and the individual pages that should be in that book.

For example, book one will have have a page count of 500 pages starting at
1
and going to 500. How can I then have the book number and a page number
from
1 to 500 inserted into the second table? This procedure should run for
all
of the books and fill the table with the book number and associated pages.

After the procedure is run, I would like to open a form for the book with
a
subform for the pages (I will add additional fields).

Any help you provide will be greatly appreciated. I'm thinking that it's
easy to autopopulate a table but nothing I've tried seem to work.

 

[Allen Browne]

I take it you got through the first 4 steps okay, but step 6 is refusing to
execute?

If you open the table in design view, what is the Data Type of your 3
fields? I am assuming:
PageID AutoNumber
BookNum Number (size matching the BookNum field in your Book
table.)
PageNum Number (size Long Integer)

If the data types match, the query should execute. If it still fails, switch
the append query to SQL View (View menu in query design), and post the SQL
statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

Thanks for the response. I think that your approach is a very intriguing
one. However whenever I run the append query, I get the following error
message: "Data type mismatch in criteria expression".

Can you let me know if I'm doing anything wrong? I followed your
instructions as you stated I should.

First thing is to give Access a list of numbers from 1 to the most number
of
pages any book could have.

1. Create a table with one field named CountID, type Number, marked as
primary key. Field Properties would be Long Integer for size, and nothing
for Default Value. Save the table as tblCount. Close.

2. Run this code to populate the table. Change the 1000 if you foresee
any
books that could have more than 1000 pages.

Function MakeData()
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("tblCount")
For lng = 1 To 1000
rs.AddNew
rs![CountID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function

3. Create a query that uses both tblCount and your original table. If you
see any line joining the 2 tables in the upper pane of table design,
delete
the line. Since there is no join, this gives you every possible
combination
of the records in the tables (called a Cartesian Product.)

4. Drag the BookNum field into the grid.
In the next column, in the field row, enter:
PageNum: tblCount.CountID
In the Criteria row under this field, enter:
Between BeginingPageNum And EndingPageNum
For each book, this limits the pages to the correct range.

5. Change the query to an Append query (Append on Query menu.) Access
will
ask what table to append to.

6. Run the query.

It would also have been possible to use AddNew code for the entire task,
but
I suspect the Append query approach will be better.

Hello all. I have a question that's been ragging my brains for a
while.

I am working on a project where I am scanning books with numbered
pages.
The books are from 1941 to present. Each year has a certain number of
books
and each book has 500 pages each although there are some books have 250
pages. The pages are numbered consecutively from 1 to the last page
number
generated for that year. Most years have from 90,000 pages to 120,000
pages
all divided into books of mostly 500 pages each.

I did an inventory of the books (so far I have inventoried about 28,000
books) and created the following table:
BookID
BookNum (each book will be numbered from 1 to whatever the last number
book
we have)
BeginingPageNum (the begining page number in that book)
EndingPageNum (the ending page number in that book)

I created another table that I intend to generate the page numbers for
all
of the books. The table looks like this:
PageID
BookNum
PageNum

What I would like to do is create a procedure or something that will
read
the book number, first page number and last page number from each entry
in
the first table and automatically populate the second table with the
book
number and the individual pages that should be in that book.

For example, book one will have have a page count of 500 pages starting
at
1
and going to 500. How can I then have the book number and a page
number
from
1 to 500 inserted into the second table? This procedure should run for
all
of the books and fill the table with the book number and associated
pages.

After the procedure is run, I would like to open a form for the book
with
a
subform for the pages (I will add additional fields).

Any help you provide will be greatly appreciated. I'm thinking that
it's
easy to autopopulate a table but nothing I've tried seem to work.

 

[Guest]

Mr. Browne:

This is the SQL code for the append query:

INSERT INTO tblPageNum ( BookNum, PageNum )
SELECT VRBookInfo.BookNum, tblCount.CountID AS PageNum
FROM tblCount, VRBookInfo
WHERE (((tblCount.CountID) Between "BeginingPageNum" And "EndingPageNum"));

Should I be using VRBookInfo.BeginingPageNum and VRBookInfo.EndingPageNum in
the criteria field instead of BeginingPageNum and EndingPageNum?

I take it you got through the first 4 steps okay, but step 6 is refusing to
execute?

If you open the table in design view, what is the Data Type of your 3
fields? I am assuming:
PageID AutoNumber
BookNum Number (size matching the BookNum field in your Book
table.)
PageNum Number (size Long Integer)

If the data types match, the query should execute. If it still fails, switch
the append query to SQL View (View menu in query design), and post the SQL
statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

Thanks for the response. I think that your approach is a very intriguing
one. However whenever I run the append query, I get the following error
message: "Data type mismatch in criteria expression".

Can you let me know if I'm doing anything wrong? I followed your
instructions as you stated I should.

First thing is to give Access a list of numbers from 1 to the most number
of
pages any book could have.

1. Create a table with one field named CountID, type Number, marked as
primary key. Field Properties would be Long Integer for size, and nothing
for Default Value. Save the table as tblCount. Close.

2. Run this code to populate the table. Change the 1000 if you foresee
any
books that could have more than 1000 pages.

Function MakeData()
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("tblCount")
For lng = 1 To 1000
rs.AddNew
rs![CountID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function

3. Create a query that uses both tblCount and your original table. If you
see any line joining the 2 tables in the upper pane of table design,
delete
the line. Since there is no join, this gives you every possible
combination
of the records in the tables (called a Cartesian Product.)

4. Drag the BookNum field into the grid.
In the next column, in the field row, enter:
PageNum: tblCount.CountID
In the Criteria row under this field, enter:
Between BeginingPageNum And EndingPageNum
For each book, this limits the pages to the correct range.

5. Change the query to an Append query (Append on Query menu.) Access
will
ask what table to append to.

6. Run the query.

It would also have been possible to use AddNew code for the entire task,
but
I suspect the Append query approach will be better.

Hello all. I have a question that's been ragging my brains for a
while.

I am working on a project where I am scanning books with numbered
pages.
The books are from 1941 to present. Each year has a certain number of
books
and each book has 500 pages each although there are some books have 250
pages. The pages are numbered consecutively from 1 to the last page
number
generated for that year. Most years have from 90,000 pages to 120,000
pages
all divided into books of mostly 500 pages each.

I did an inventory of the books (so far I have inventoried about 28,000
books) and created the following table:
BookID
BookNum (each book will be numbered from 1 to whatever the last number
book
we have)
BeginingPageNum (the begining page number in that book)
EndingPageNum (the ending page number in that book)

I created another table that I intend to generate the page numbers for
all
of the books. The table looks like this:
PageID
BookNum
PageNum

What I would like to do is create a procedure or something that will
read
the book number, first page number and last page number from each entry
in
the first table and automatically populate the second table with the
book
number and the individual pages that should be in that book.

For example, book one will have have a page count of 500 pages starting
at
1
and going to 500. How can I then have the book number and a page
number
from
1 to 500 inserted into the second table? This procedure should run for
all
of the books and fill the table with the book number and associated
pages.

After the procedure is run, I would like to open a form for the book
with
a
subform for the pages (I will add additional fields).

Any help you provide will be greatly appreciated. I'm thinking that
it's
easy to autopopulate a table but nothing I've tried seem to work.

 

[Guest]

Mr. Browne:

Your solution worked spectacularly well with one minor tweak:

In the criteria I used VRBookInfo.BeginingPageNum and
VRBookInfo.EndingPageNum instead of BeginingPageNum and EndingPageNum. The
reason being is that the names BeginingPageNum and EndingPageNum are
interpreted as strings and not inputs from the VRBookInfo table.

Thanks a lot for this great tip!!! You saved my weekend. Now I'm faced
with another challenge and that is after the query was ran, my database grew
from 65k to over 400MB. I think will upsize to SQL Server Express.

Once again your tip was very simple and very helpful.

Mr. Browne:

This is the SQL code for the append query:

INSERT INTO tblPageNum ( BookNum, PageNum )
SELECT VRBookInfo.BookNum, tblCount.CountID AS PageNum
FROM tblCount, VRBookInfo
WHERE (((tblCount.CountID) Between "BeginingPageNum" And "EndingPageNum"));

Should I be using VRBookInfo.BeginingPageNum and VRBookInfo.EndingPageNum in
the criteria field instead of BeginingPageNum and EndingPageNum?

I take it you got through the first 4 steps okay, but step 6 is refusing to
execute?

If you open the table in design view, what is the Data Type of your 3
fields? I am assuming:
PageID AutoNumber
BookNum Number (size matching the BookNum field in your Book
table.)
PageNum Number (size Long Integer)

If the data types match, the query should execute. If it still fails, switch
the append query to SQL View (View menu in query design), and post the SQL
statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

Thanks for the response. I think that your approach is a very intriguing
one. However whenever I run the append query, I get the following error
message: "Data type mismatch in criteria expression".

Can you let me know if I'm doing anything wrong? I followed your
instructions as you stated I should.

:

First thing is to give Access a list of numbers from 1 to the most number
of
pages any book could have.

1. Create a table with one field named CountID, type Number, marked as
primary key. Field Properties would be Long Integer for size, and nothing
for Default Value. Save the table as tblCount. Close.

2. Run this code to populate the table. Change the 1000 if you foresee
any
books that could have more than 1000 pages.

Function MakeData()
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("tblCount")
For lng = 1 To 1000
rs.AddNew
rs![CountID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function

3. Create a query that uses both tblCount and your original table. If you
see any line joining the 2 tables in the upper pane of table design,
delete
the line. Since there is no join, this gives you every possible
combination
of the records in the tables (called a Cartesian Product.)

4. Drag the BookNum field into the grid.
In the next column, in the field row, enter:
PageNum: tblCount.CountID
In the Criteria row under this field, enter:
Between BeginingPageNum And EndingPageNum
For each book, this limits the pages to the correct range.

5. Change the query to an Append query (Append on Query menu.) Access
will
ask what table to append to.

6. Run the query.

It would also have been possible to use AddNew code for the entire task,
but
I suspect the Append query approach will be better.

Hello all. I have a question that's been ragging my brains for a
while.

I am working on a project where I am scanning books with numbered
pages.
The books are from 1941 to present. Each year has a certain number of
books
and each book has 500 pages each although there are some books have 250
pages. The pages are numbered consecutively from 1 to the last page
number
generated for that year. Most years have from 90,000 pages to 120,000
pages
all divided into books of mostly 500 pages each.

I did an inventory of the books (so far I have inventoried about 28,000
books) and created the following table:
BookID
BookNum (each book will be numbered from 1 to whatever the last number
book
we have)
BeginingPageNum (the begining page number in that book)
EndingPageNum (the ending page number in that book)

I created another table that I intend to generate the page numbers for
all
of the books. The table looks like this:
PageID
BookNum
PageNum

What I would like to do is create a procedure or something that will
read
the book number, first page number and last page number from each entry
in
the first table and automatically populate the second table with the
book
number and the individual pages that should be in that book.

For example, book one will have have a page count of 500 pages starting
at
1
and going to 500. How can I then have the book number and a page
number
from
1 to 500 inserted into the second table? This procedure should run for
all
of the books and fill the table with the book number and associated
pages.

After the procedure is run, I would like to open a form for the book
with
a
subform for the pages (I will add additional fields).

Any help you provide will be greatly appreciated. I'm thinking that
it's
easy to autopopulate a table but nothing I've tried seem to work.

 

[Allen Browne]

There is a fair bit of data there, but before you decide to upsize, just
run:
Tools | Database Utilities | Compact
and see if it comes down some.

 

[Guest]

Hi Don and Allen.

My name is Victor and I have almost the same problems, and this help me a
LOT!!! my new problem is that this is made in general. If you ad a new book
for example and run this it will do it for all of them not only the new one?
I Have almost the same tables and forms but with differents value. I will
use Don example to make it simple. Let say for example that you have the
same tables and query but you have a Form using VRBookInfo as source and a
subform using the second table and you want to add new books in the main form
and use a button to create all the pages for that book. It is posible to do
this??? Please let me know. Thanks.

Mr. Browne:

Your solution worked spectacularly well with one minor tweak:

In the criteria I used VRBookInfo.BeginingPageNum and
VRBookInfo.EndingPageNum instead of BeginingPageNum and EndingPageNum. The
reason being is that the names BeginingPageNum and EndingPageNum are
interpreted as strings and not inputs from the VRBookInfo table.

Thanks a lot for this great tip!!! You saved my weekend. Now I'm faced
with another challenge and that is after the query was ran, my database grew
from 65k to over 400MB. I think will upsize to SQL Server Express.

Once again your tip was very simple and very helpful.

Mr. Browne:

This is the SQL code for the append query:

INSERT INTO tblPageNum ( BookNum, PageNum )
SELECT VRBookInfo.BookNum, tblCount.CountID AS PageNum
FROM tblCount, VRBookInfo
WHERE (((tblCount.CountID) Between "BeginingPageNum" And "EndingPageNum"));

Should I be using VRBookInfo.BeginingPageNum and VRBookInfo.EndingPageNum in
the criteria field instead of BeginingPageNum and EndingPageNum?

I take it you got through the first 4 steps okay, but step 6 is refusing to
execute?

If you open the table in design view, what is the Data Type of your 3
fields? I am assuming:
PageID AutoNumber
BookNum Number (size matching the BookNum field in your Book
table.)
PageNum Number (size Long Integer)

If the data types match, the query should execute. If it still fails, switch
the append query to SQL View (View menu in query design), and post the SQL
statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

Thanks for the response. I think that your approach is a very intriguing
one. However whenever I run the append query, I get the following error
message: "Data type mismatch in criteria expression".

Can you let me know if I'm doing anything wrong? I followed your
instructions as you stated I should.

:

First thing is to give Access a list of numbers from 1 to the most number
of
pages any book could have.

1. Create a table with one field named CountID, type Number, marked as
primary key. Field Properties would be Long Integer for size, and nothing
for Default Value. Save the table as tblCount. Close.

2. Run this code to populate the table. Change the 1000 if you foresee
any
books that could have more than 1000 pages.

Function MakeData()
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("tblCount")
For lng = 1 To 1000
rs.AddNew
rs![CountID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function

3. Create a query that uses both tblCount and your original table. If you
see any line joining the 2 tables in the upper pane of table design,
delete
the line. Since there is no join, this gives you every possible
combination
of the records in the tables (called a Cartesian Product.)

4. Drag the BookNum field into the grid.
In the next column, in the field row, enter:
PageNum: tblCount.CountID
In the Criteria row under this field, enter:
Between BeginingPageNum And EndingPageNum
For each book, this limits the pages to the correct range.

5. Change the query to an Append query (Append on Query menu.) Access
will
ask what table to append to.

6. Run the query.

It would also have been possible to use AddNew code for the entire task,
but
I suspect the Append query approach will be better.

Hello all. I have a question that's been ragging my brains for a
while.

I am working on a project where I am scanning books with numbered
pages.
The books are from 1941 to present. Each year has a certain number of
books
and each book has 500 pages each although there are some books have 250
pages. The pages are numbered consecutively from 1 to the last page
number
generated for that year. Most years have from 90,000 pages to 120,000
pages
all divided into books of mostly 500 pages each.

I did an inventory of the books (so far I have inventoried about 28,000
books) and created the following table:
BookID
BookNum (each book will be numbered from 1 to whatever the last number
book
we have)
BeginingPageNum (the begining page number in that book)
EndingPageNum (the ending page number in that book)

I created another table that I intend to generate the page numbers for
all
of the books. The table looks like this:
PageID
BookNum
PageNum

What I would like to do is create a procedure or something that will
read
the book number, first page number and last page number from each entry
in
the first table and automatically populate the second table with the
book
number and the individual pages that should be in that book.

For example, book one will have have a page count of 500 pages starting
at
1
and going to 500. How can I then have the book number and a page
number
from
1 to 500 inserted into the second table? This procedure should run for
all
of the books and fill the table with the book number and associated
pages.

After the procedure is run, I would like to open a form for the book
with
a
subform for the pages (I will add additional fields).

Any help you provide will be greatly appreciated. I'm thinking that
it's
easy to autopopulate a table but nothing I've tried seem to work.

 

[Allen Browne]

You can add criteria to the query to limit it to just the one book.

For example, if the form is named Form1, and it has a BookID field, you
could add this to the Criteria row of your query under the BookID field:
[Froms].[Form1].[BookID]

The query is now limited to the active record in the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Don and Allen.

My name is Victor and I have almost the same problems, and this help me
a
LOT!!! my new problem is that this is made in general. If you ad a new
book
for example and run this it will do it for all of them not only the new
one?
I Have almost the same tables and forms but with differents value. I will
use Don example to make it simple. Let say for example that you have the
same tables and query but you have a Form using VRBookInfo as source and a
subform using the second table and you want to add new books in the main
form
and use a button to create all the pages for that book. It is posible to
do
this??? Please let me know. Thanks.

Mr. Browne:

Your solution worked spectacularly well with one minor tweak:

In the criteria I used VRBookInfo.BeginingPageNum and
VRBookInfo.EndingPageNum instead of BeginingPageNum and EndingPageNum.
The
reason being is that the names BeginingPageNum and EndingPageNum are
interpreted as strings and not inputs from the VRBookInfo table.

Thanks a lot for this great tip!!! You saved my weekend. Now I'm faced
with another challenge and that is after the query was ran, my database
grew
from 65k to over 400MB. I think will upsize to SQL Server Express.

Once again your tip was very simple and very helpful.

Mr. Browne:

This is the SQL code for the append query:

INSERT INTO tblPageNum ( BookNum, PageNum )
SELECT VRBookInfo.BookNum, tblCount.CountID AS PageNum
FROM tblCount, VRBookInfo
WHERE (((tblCount.CountID) Between "BeginingPageNum" And
"EndingPageNum"));

Should I be using VRBookInfo.BeginingPageNum and
VRBookInfo.EndingPageNum in
the criteria field instead of BeginingPageNum and EndingPageNum?
:

I take it you got through the first 4 steps okay, but step 6 is
refusing to
execute?

If you open the table in design view, what is the Data Type of your 3
fields? I am assuming:
PageID AutoNumber
BookNum Number (size matching the BookNum field in your
Book
table.)
PageNum Number (size Long Integer)

If the data types match, the query should execute. If it still fails,
switch
the append query to SQL View (View menu in query design), and post
the SQL
statement.

Allen:

Thanks for the response. I think that your approach is a very
intriguing
one. However whenever I run the append query, I get the following
error
message: "Data type mismatch in criteria expression".

Can you let me know if I'm doing anything wrong? I followed your
instructions as you stated I should.

:

First thing is to give Access a list of numbers from 1 to the most
number
of
pages any book could have.

1. Create a table with one field named CountID, type Number,
marked as
primary key. Field Properties would be Long Integer for size, and
nothing
for Default Value. Save the table as tblCount. Close.

2. Run this code to populate the table. Change the 1000 if you
foresee
any
books that could have more than 1000 pages.

Function MakeData()
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("tblCount")
For lng = 1 To 1000
rs.AddNew
rs![CountID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function

3. Create a query that uses both tblCount and your original table.
If you
see any line joining the 2 tables in the upper pane of table
design,
delete
the line. Since there is no join, this gives you every possible
combination
of the records in the tables (called a Cartesian Product.)

4. Drag the BookNum field into the grid.
In the next column, in the field row, enter:
PageNum: tblCount.CountID
In the Criteria row under this field, enter:
Between BeginingPageNum And EndingPageNum
For each book, this limits the pages to the correct range.

5. Change the query to an Append query (Append on Query menu.)
Access
will
ask what table to append to.

6. Run the query.

It would also have been possible to use AddNew code for the entire
task,
but
I suspect the Append query approach will be better.

message
Hello all. I have a question that's been ragging my brains for
a
while.

I am working on a project where I am scanning books with
numbered
pages.
The books are from 1941 to present. Each year has a certain
number of
books
and each book has 500 pages each although there are some books
have 250
pages. The pages are numbered consecutively from 1 to the last
page
number
generated for that year. Most years have from 90,000 pages to
120,000
pages
all divided into books of mostly 500 pages each.

I did an inventory of the books (so far I have inventoried about
28,000
books) and created the following table:
BookID
BookNum (each book will be numbered from 1 to whatever the last
number
book
we have)
BeginingPageNum (the begining page number in that book)
EndingPageNum (the ending page number in that book)

I created another table that I intend to generate the page
numbers for
all
of the books. The table looks like this:
PageID
BookNum
PageNum

What I would like to do is create a procedure or something that
will
read
the book number, first page number and last page number from
each entry
in
the first table and automatically populate the second table with
the
book
number and the individual pages that should be in that book.

For example, book one will have have a page count of 500 pages
starting
at
1
and going to 500. How can I then have the book number and a
page
number
from
1 to 500 inserted into the second table? This procedure should
run for
all
of the books and fill the table with the book number and
associated
pages.

After the procedure is run, I would like to open a form for the
book
with
a
subform for the pages (I will add additional fields).

Any help you provide will be greatly appreciated. I'm thinking
that
it's
easy to autopopulate a table but nothing I've tried seem to
work.