Auto-populate one table from another

[Guest]

I have a database with two tables:

tblBookInfo - has a unique number for each book, number of pages per book
and begining and ending page numbers

tblPageNum - has a unique number for each page, related book number for each
page, page number of each page in a book

I received help on this query here before and can't seem to find it. Based
on the information in the tblBookInfo table, I would like to create a record
for each page of the book in the tblPageNum table. I remember using an
append query to do this. Any help would be great.

 

[Jeff Boyce]

Don

I may be missing something ...

It sounds like your second table has two fields that have a page number in
them. And I'm not entirely clear why you've bothered to show begin & end
page numbers in your tblBookInfo.

If the tblPageNum has a record of each page number in each book, you can
"calculate" the minimum and maximum page number for each book -- you don't
have to store it.

On the other hand, unless you are doing something more with the tblPageNum
records, what business need are you solving by having one record per page
per book?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff:

Here's the story behind the two tables. I am doing a project where we are
scanning books of birth certificates. The books have a range of individually
numbered pages. Each page will have certain checkboxes assigned to it as
well as a comment field. The purpose of the checkboxes is to let anyone that
is looking at the book know if a particular page was missing, damaged, as
well as allow us to put comments on each page. The reason each book has a
begining and ending number is that each book has a specific number of birth
certificates and the query would use that range to create the associated
records in the tblPageNum table for each book entry. In addition to other
information, the tblBookInfo table has a field showing the total number of
pages for each book which be either 250, 500 or 1000 certificates.


This is a very sensitive project and we need to account for every page in
every book and may need to add comments about pages into the database. I did
have the query before but I deleted it from the database after I had
processed the intial population of books; I did not want a curious user to go
in and re-run the query. Now I can't seem to find the code for the query.

Thx
DSR

 

[Jeff Boyce]

Don

That makes sense. I'm not aware of a way to query to add a variable number
of records into your second table, based on the begin/end numbers in your
first.

This IS doable via a procedure, which you'd need to write. The basic
concept is:

For Book = 1 to NumberOfBooks
For I = ThisBook'sBeginPageNumber to ThisBook'sEndPageNumber
Add a "page" record for Page# I
Next I
Next Book

This is pseudo-code and won't run on any compiler known to humankind.

Regards

Jeff Boyce
Microsoft Office/Access MVP