Total Restructure of Sales form from table and query foundations

[smercer]

Hi All,

I have many problems with this sales form I have been creating, one is slow on old machines, two is more complex than any other project I have created, three although it works well overall, it still has errors (Have been working on it for 4 months).

This last feature (1, 2 and 3 buttons for storing customer purchases) I have put in, has made it even more complex (but more useful).

Sales form Currently
the sales form is based around Temp tables (the tables is always there but the data is moved to a completed sales table when I am finished with it) and a subform based on a query that looks up the record that matches the scanned book(via barcode) and a text box that links to the subform, so that it is easier to write code to the linked text box. Action queries then look at the text box and copy the related record from inventory to the temp table, delete the record in inventory for current sale and same again when the user presses "sell books". Miscellaneous books are a bit different, they need a append and update query to change the quantity and a delete query when quantity reaches zero.

There is a seperate table for customer number that stores such things like discounts user has given, if the user has opened the exchange form for a customer, if the customer is crediting books from what they are exchanging to there current purchases, the results from calculation of the credits from exchange etc and another form for each ISBN being sold. This is needed because I need to have it in a many to many relationship.
_____________________________________________

I have thought of a better way of doing this, but I also think there is always someone who has a better way of doing it.


New version I thought of:
I am thinking i might have 3 new fields in the inventory table so that there is one for each customer (so that I am storing customer purchases in the inventory table) so that when the sale is made the tick box for that book is made true(via update query). the sales form will then requery with results that only have the tick boxes set to true according to which customer the user has selected. I also think I will need to merge the customer table in with the Book inventory table, but the disadvantage is that the extra empty fields of 20,000 records would make the database bloat ware (meaning the database will get very big and slow down everything)

____________________________________________
1) should I leave the sales form as it already is or,
2) use the new idea.

What does everyone else say? If anyone has a better idea I would definitely like to hear it.

Thanks to all in advance

 

[ecniv]

Sort of Amazon site?
But in the db?


I suggest you rethink and just have sales as a list with a status field. But as I don't know how this interacts with the rest of hte db there is probably a good reason.

Is this part meant to be like a shopping basket? What happens if the user doesn't sell, does the record currently vanish, float, is lost...?


What are you trying to acheive, and how are your tables structured atm?


Vince

 

[smercer]

This is not for a website. this is for a phyisical store with phyisical customers that walk in to the phyiscal store and get a book off the shelf and buy. The customer takes the book they what to buy to the counter and the sales person scans the barcode the store has printed of the genune book_ID. the book is entered into the temporary table via append, update and deletes from inventory table until the sale is finalised or canceled.

When the sale is canceled the record is then copied back to the "tbl_Inventory_Each_Book" and "tbl_inventory_Book_Description" tables. because this is a second hand book store, there are price variations on cost price, sale price (some books are in better condition then others), the cover etc. these differences are stored in the "tbl_Inventory_Each_Book" table

This time I have included a copy of the relationships (see Attachment)

Thanks Vince

 

[smercer]

I think I might have the customer number as one field and as a number format set at byte for size. then I might have the current customer number table store the other stuff.

Also here is a screen shot of the sales form with the onresize disabled so you can see the controls that you don't normaly see.

 

[smercer]

Does any one have any suggestions?

Would this work?

Would it be better?

Thanks for any advice

 

[Rich]

Why all the append delete queries etc.
Why not just store the date of sale, if it isn't sold then the date field will be null?

 

[ecniv]

This is just a thought - and may not be a good one at that


Books Table (or Items table?)
Holds ISBN etc detail

Customer Table
Holds address/tel customer details

StockItems (not sure about this)
holds books in stock - and when stocked

Requests (for getting in books - specifically)
Book and customer

Sales
Holds customer (sold), details of sale date/time/loc/till etc - customer may be unregistered (just as unknown) or via instore book card...?

Sales detail
Holds salesID, books/items and customer (for filtering) - customer may be unregistered (just as unknown) or via instore book card...? - could also hold discounts/offers applied too


Stock levels then can be calced from total in and total sold...

I know there are other part such as damaged books, returned stock, price offers etc, not sure how you'd handle them. As I said, never having done this, I am unsure of the best route, so above is an option.

As to the POS, it is only stored into the main tables once the sale is complete. Your idea of a temp table for sales is good;
Temp table
OperatorID
BookID (or ItemID)
Price

I suppose you'd need to put them into the sales table to get a sales id of some type for the receipt.

Sounds like an interesting project and with barcode reading included too - bargain Good luck


Vince

 

[Mile-O]

I also think I will need to merge the customer table in with the Book inventory table, but the disadvantage is that the extra empty fields of 20,000 records would make the database bloat ware (meaning the database will get very big and slow down everything)

Don't be mad.

1) should I leave the sales form as it already is or

No.

2) use the new idea.

No.

What does everyone else say?

Listen to Rich.

 

[smercer]

Thanks everyone,

I think I might try out Rich's Idea (Brilliant Idea!!!). I should have asked this question long ago...


Thanks again

 

[Rich]

Yes, but don't forget life in the real world's never that simple, some awkward customer may come in and buy two copies of the same book
Have a look here for some more help

 

[smercer]

Yes, but don't forget life in the real world's never that simple, some awkward customer may come in and buy two copies of the same book
Have a look here for some more help

Thanks Rich, but I had already had support for that when I first started. I am going to have to have one record per book because each has a different price, although I 'could' have a quantity field, I do not think it will work very well because the same book/customer may bring it back and exchange it for another book and it would defeat the purpose because the quantity would end up being all "1" because of the different sold dates.

 

[smercer]

I do not think I will be able to have the same for the miscellaneous books. The miscellaneous books are books that are generally the same type of book eg magazines etc that the user does not need to have author etc, and he sells them all at one price.

This is where quantity comes in, I have it set in the book entry form so that the user can just enter a number, click add and it will add the number from what he typed to the quantity that is already in the inventory in a subform.

when the user scans a miscellaneous book It first adds the book details from the main table and inserts it in to the temp table via append query, then the update query adds to the quantity in temp table that has already been scanned. another update query is needed to subtract 1 from the main miscellaneous inventory table. then the delete query is used when the quantity in the main miscellaneous inventory table becomes 0 (zero).

I could tell you how the sale is finalised but I think you get the idea of what may be involved there.

Is there a better way of doing this for the miscellaneous?

Thanks in advance