Solved Multiple records at a time

[Romio_1968]

Hello,
I have a database for recording books in a library
Each record have the following fields:

Inventory_No > Primary key (no duplicates)
Timestamp
Title (duplicates allowed)
Author (duplicates allowed)
Year

I am not allowed to enter quantity, so if i have 25 copies of the same book, i must enter 25 records with different Inventory_No.
Is there any way to introduce al the 25 records at a time.

For example, let introduce 25 copies of `Wealth of nations` by Adam Smith, with consecutive Inventory_No from 1 to 25.
In the future, i must be able to introduce 5 more copies, numbered from 31 to 35.

Thank you

 

[Gasman]

Well I would have an autonumber PK to start with.
Then just use a loop to insert query for how many records you want incrementing the inventory number each time. Why doesn't the next number become 26?

 

[MajP]

Yes you can but I do not think your tables are correct. You should not be repeating blocks of information in a table
You should have a generic table tblBooks (BookName, author, copy right, year etc.)
Then a specific insance of books tblBookDetails

bookID_FK a foreign key to tblBooks
Inventory_No
TimeStamp

But after that you have a form where you select a Book and an unbound control for how many to add. And loop an insert incrementing the number. If the numbers are non contiguous as you show then that form needs a unbound start value (ie. 35 instead of 26)

 

[ebs17]

Inventory_No - what is the structure of these field contents in relation to different books?
I think you need an ID (Autonumber) for a book and also a serial number for the copies of the respective book.
With simple numbers you can calculate and multiply, e.g. do something like that ...

PARAMETERS
   parBOOKID Long,
   parNumber Long
;
SELECT
   B.BookID,
   T.I AS SerNo
FROM
   tblBooks AS B,
   T999 AS T
WHERE
   T.I BETWEEN 
      (
         SELECT
            MAX(SerNo)
         FROM
            tblBooks
         WHERE
            BookID = parBOOKID
      )
   + 1
      AND
   (
         SELECT
            MAX(SerNo)
         FROM
            tblBooks
         WHERE
            BookID = parBOOKID
      )
   + parNumber

T999 is an auxiliary table I use frequently here. It contains only one field I (long, primary key) with the contents from 0 to 999.
The selection query shown can then easily be converted into an append query (adding the INSERT part).

 

[arnelgp]

here is a sample which you can follow.

 

[Romio_1968]

Thank you arnelgp
Works like a charm. Please check MajP solution too. Do you thing it is a bettrer structural approach?

MajP, this may be an approach, but in this case, Inventory_No would not be the PK for the main table, so I should use a labeling sistem for the book title.

 

[Pat Hartman]

@arnelgp 's solution answers your SPECIFIC question but it is not be best solution. It is the quick and dirty spreadsheet solution. The best solution normalizes the data as MajP suggests so that the common data is stored in tblBooks and the data relating to a specific copy of a book is stored in a separate table. the tblBookDetails has a FK to the autonumber ID in tblBooks. It also has a sequence number that is generated as you add copies to the book Details. tblBookDetails, is where you would record the condition of the book, the date it was added to the library, and a field to log who it is loaned out to. If you keep a history of loans, then you need a third table which just tracks in/out for the specific copy of a book.

Once the tables are properly normalized, you can add a button to the form that is bound to tblBooks as well as an unbound control with the number to add. Then you create a code loop similar to (but slightly different) what arnelgp coded for you. The date and condition of the books being added will all be the same. Ie todays Date() and whatever your best condition value is.