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.