Using Combo information to look up price via date

[Barryonepointfour]

Hey guys, I have a database I'm creating for DVD/blu-ray sales. I currently have a order system in place in a subform, which uses a combo box to find the customer and fills in all the information in the rest of the subform. One piece of information is the date of sale.

I need to be able to have the ability to change the price of the DVDs and have a history of sales with the right price. The easiest way to do this seemed to be by Date of sale. So the price is linked to the date of sale, when the price changes, all orders before that change use the old price, after use the current price.

I was wondering if there is anyway to have a second subform on the same page and us the date of sale from subform1 and have it display the price in the second subform?

Thanks guys you lot have really helped me with this database so far.

 

[jdraw]

I suggest you get your tables and relationships designed and tested before moving to forms and subforms. Typically a Sales and Orders system will have these generic tables
Customer, Order, OrderDetails, Product. Your situation may have different names, but essentially this is the base.

A Customer makes 1 or many Orders
An Order is for 1 or Many Products
If you store the Poduct Price with the Product record, you will have difficulty when you change that Price--((all of your historical sales records will change)). To avoid this, you can put the CurrentPrice of the Product in the Product table, and put the ProductID, the AgreedUponPrice and the Quantity in the OrderDetail record.

AgreedUponPrice is the negotiated Price that you sold this product to this Customer in this Order and that price could include sale price/clearance item/loyalty reward program/special discount.

For more info see http://www.access-programmers.co.uk/forums/showthread.php?t=238668#4

 

[Barryonepointfour]

Thanks for the videos jdraw, they helped me organise my Cast and Director tables.

Can you explain how your system would work?
As far as I understand you, the AgreedUponPrice would have to change with each order thats not sold for the CurrentPrice and the CurrentPrice can't change without affecting the history records?

Would there be a way to just have the current price change lets say monthly, and all orders for that month would be at the current price. Then in the records link the date of sale to a date of price change? so if the dvd was sold on the 10/2/13 it would look up the price change before that date and charge the customer that price?

Sorry if that's what your setup would do, I'm just confused about how it would do that.

Thanks for your help, it really is great being able to asked experienced members of a community for help.

 

[jdraw]

the AgreedUponPrice is the Price that was paid for this Quantity of Product on this Order for this Customer--- from that you can find the History of Sales.
You could use a CurrentPrice and EffectiveDate in your Product table, but it will:
- require a maintenance regime, and
- you will have to get the effectiveCurrentPrice from the Product or associated Price table,
- still use agreedUponPrice in the OrderDetails as the actual selling price

How would you handle ClearanceSale, Loyalty Program discount, other sales/discounts?

You may want to read/review this post for more info.
www.access-programmers.co.uk/forums/showthread.php?t=248750

 

[Barryonepointfour]

Since there is only 2 products, Blu-ray and DVDs and they are rental there won't be ClearanceSales. I hadn't thought of a loyalty program yet.

So presuming your way is best (lets be honest it is) I would have my Film(product) table contain the price? Then the Order-Film (OrderDetails) table have the AgreedUponPrice, and then when a sale is made the FilmID would be looked up, giving the price. Then if multiple DVDs had be ordered the AgreedUponPrice would calculate the total cost?

Then for the history the sales would be logged with the AgreedUponPrice?

Or have I totally misinterpreted what you're trying to say?

 

[jdraw]

In you opening post, you said

Hey guys, I have a database I'm creating for DVD/blu-ray sales.

I recommend you write a 5-6 line description of what your business is/will be. This will clarify things for the reader and will help you with the details. You may want to think about marketing schemes, loyalty programs.... Here is a sample of the level of detail suggested -- taken from Rogers Access Library.

ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

 

[Barryonepointfour]

The database I'm creating is for DVD/Blu-ray rentals. It needs to be able to store customer, order and film information. The film information will contain 3 main actors in the film, the director and what genres the film is it. It will also have the Title, length and year of release.
The customer information will have the name, address, email and phone number of the customer.
The key aspects this database needs to do are place orders for rents, have a record of sales, email customers when there film is due back/overdue. It must be able to have a flexible price.

I currently have the Tables Customer, Order, Order-Film, Film, Film-Cast, Cast, Film-Director, Director, Film-Genre, Genre and Hire Cost. As I cannot post images here I have one posted on LinusTechTips forum, in the software subforum in a post titled Microsoft Access Help - Price History.

I hope I have explained what I need this database to do.

 

[jdraw]

Here is a data model that may help with your application.