How do I approach this

[hmho]

I have been struggling how to approach this and I'm not having luck. I have table I keep track phone cards Inventory and another table to keep track phone card sales and here are the fields.

TblInventory has these fields (CardName, BookNumber, BookSerialNo, QtyinBook, Active) Serial number is the key and active is yes/no and some books has 50 cards and others has 100 or 200 each book number starts 1 to 50 or 100 and so on.

TblSales has these fields (SaleDate, BookNumber, BookSerialNo, CardEndNo)

I want to create form that I can enter each day's ending numebrs but I want to be able to see all books that active and previous day's ending card number and I want to be able to enter today's date and the card ending number for example below.
[SalesDate][BookNumber][BookSerialNo][CardEndNo]
05/20/09 12345 05599 20
05/20/09 12999 05878 30
05/20/09 12789 05874 40
I want to see the information above in the form so I can enter this information below
[SalesDate][BookNumber][BookSerialNo][CardEndNo]
05/21/09 12345 05599 35
05/21/09 12999 05878 45
05/21/09 12789 05874 55

Is this possible.

Thanks

 

[jzwp22]

Before you get too far on your forms, you should take another look at your table structure. If you have many cards associated with a book then that describes a one(book)-to-many(cards) relationship which is handled with two tables

tblBooks
-pkBookID primary key, autonumber
-BookNumber
-BookSerialNo

tblBookCards
-pkBookCardID primary key, autonumber
-fkBookID foreign key relating the card to the book in tblBooks
-CardEndNo

I assume that a sale can consist of multiple cards

tblSales
-pkSalesID primary key, autonumber
-fkCustomerID foreign key to tblCustomers
-dteSale (sale date)

tblSalesDetail
-pkSalesDetailID primary key, autonumber
-fkSalesID foreign key to tblSales
-fkBookCardID foreign key to tblBookCards

tblCustomers
-pkCustomerID primary key, autonumber
-txtFName
-txtLName


To find the number of the last card sold, you would use a totals query that utilizes the Max() function.

Do you always sell the cards in sequence based on the card end number? What do you do if you go out of sequence?

 

[hmho]

No each book is just one and it may have 50 to hundered cards from 01 to 100.

 

[jzwp22]

I'm a little confused. Do you sell an entire book or just a card or cards from the book? If you are selling individual cards then I recommend going with the structure I proposed earlier.