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.

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Don Rowley" <(E-Mail Removed)> wrote in message
news:BD41C50E-4CFA-4CC5-B8D7-(E-Mail Removed)...
> 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:

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.

"Allen Browne" wrote:

> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Don Rowley" <(E-Mail Removed)> wrote in message
> news:BD41C50E-4CFA-4CC5-B8D7-(E-Mail Removed)...
> > 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.
>
>
>

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Don Rowley" <(E-Mail Removed)> wrote in message
news:870CB62D-4071-44A5-ACA4-(E-Mail Removed)...
> 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.
>
> "Allen Browne" wrote:
>
>> 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.
>>
>> "Don Rowley" <(E-Mail Removed)> wrote in message
>> news:BD41C50E-4CFA-4CC5-B8D7-(E-Mail Removed)...
>> > 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.
>>
>>
>>

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?
"Allen Browne" wrote:

> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Don Rowley" <(E-Mail Removed)> wrote in message
> news:870CB62D-4071-44A5-ACA4-(E-Mail Removed)...
> > 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.
> >
> > "Allen Browne" wrote:
> >
> >> 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.
> >>
> >> "Don Rowley" <(E-Mail Removed)> wrote in message
> >> news:BD41C50E-4CFA-4CC5-B8D7-(E-Mail Removed)...
> >> > 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.
> >>
> >>
> >>
>
>
>

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.





"Don Rowley" wrote:

> 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?
> "Allen Browne" wrote:
>
> > 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.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Don Rowley" <(E-Mail Removed)> wrote in message
> > news:870CB62D-4071-44A5-ACA4-(E-Mail Removed)...
> > > 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.
> > >
> > > "Allen Browne" wrote:
> > >
> > >> 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.
> > >>
> > >> "Don Rowley" <(E-Mail Removed)> wrote in message
> > >> news:BD41C50E-4CFA-4CC5-B8D7-(E-Mail Removed)...
> > >> > 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.
> > >>
> > >>
> > >>
> >
> >
> >

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.

--
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.

"Don Rowley" <(E-Mail Removed)> wrote in message
news:68142BB6-1FE6-43D9-9BEA-(E-Mail Removed)...
> 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.

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.

"Don Rowley" wrote:

> 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.
>
>
>
>
>
> "Don Rowley" wrote:
>
> > 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?
> > "Allen Browne" wrote:
> >
> > > 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.
> > > Tips for Access users - http://allenbrowne.com/tips.html
> > > Reply to group, rather than allenbrowne at mvps dot org.
> > >
> > > "Don Rowley" <(E-Mail Removed)> wrote in message
> > > news:870CB62D-4071-44A5-ACA4-(E-Mail Removed)...
> > > > 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.
> > > >
> > > > "Allen Browne" wrote:
> > > >
> > > >> 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.
> > > >>
> > > >> "Don Rowley" <(E-Mail Removed)> wrote in message
> > > >> news:BD41C50E-4CFA-4CC5-B8D7-(E-Mail Removed)...
> > > >> > 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.
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Victor Torres" <(E-Mail Removed)> wrote in message
news:C3417159-D9F4-46AD-B95F-(E-Mail Removed)...
> 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.
>
> "Don Rowley" wrote:
>
>> 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.
>>
>>
>>
>>
>>
>> "Don Rowley" wrote:
>>
>> > 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?
>> > "Allen Browne" wrote:
>> >
>> > > 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.
>> > >
>> > > "Don Rowley" <(E-Mail Removed)> wrote in message
>> > > news:870CB62D-4071-44A5-ACA4-(E-Mail Removed)...
>> > > > 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.
>> > > >
>> > > > "Allen Browne" wrote:
>> > > >
>> > > >> 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.
>> > > >>
>> > > >> "Don Rowley" <(E-Mail Removed)> wrote in
>> > > >> message
>> > > >> news:BD41C50E-4CFA-4CC5-B8D7-(E-Mail Removed)...
>> > > >> > 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.