How to create departmental pricing, Adding To A Form

[pv003220]

Hi Guys I wonder if you could help me.

I currently have an access database that is designed to show what room has what printers around my school. One of the forms that run allows the user to enter the room number, and it brings up the printer (from the printer table), the cartridges assigned to that printer (from the cartridge table which shows up as a sub-form) and the room information (from the room table).

However this is what I need:

1) I Need to somehow implement buttons so that she can take 1 cartridge number off the quantity value each time she clicks the button. However it somehow also needs to ask the user which department to charge the cartridge for. I dont currently have any department table as I dont know how is best to contstruct this. It also needs to somehow read the cost of the cartridge from another table and insert that price to the department (so the price does not need to be entered by the user, it automatically knows the price of the cartridge when the button is clicked.)

2) Talking about the cost of the cartridges. The user who is the database is for has told me that she gets her cartridges off four different suppliers, and each supplier offers her a different price. So I need to also create a report that writes out the cartidge model and the cheapest price, followed by the correct supplier. Just to make it a little bit more difficult, She also told me that one supplier can charge multiple prices, depending on how many cartridge orders she places each time.

Thanks for taking the time to read this and any help will be much appreciated, I have spent alot of time trying to work a way around this, And seem to struggle coming up with a working answer.

[nico5038]

Guess you have a daunting task :-)

Personally I would "drop" the pricelist per supplier as this would require the registration of all prices and the maintenance of them, even without the certainty that they are ordered for that price. I would leave the "buy decision" up to the employee, based on the actual prices (probably found on the internet).

For your application I would design a tblPrinterCartr idge holding the type(s) available for your printers. Next I would create a tblCartridgeAva ilable with the "In Stock" cartridges where the actual purchase price is recorded.

Finally you'll need to decide how to assign a Cartridge to a department when there are multiple "In Stock" with different prices. You can use the "oldest" purchased or the cheapest with the risk that an expensive old cartridge remains in stock forever....

Getting the idea ?

Nic;o)

[pv003220]

Thanks Nico, Your ideas seem great. How do I go about creating an automated proccess so that the oldest cartridge in stock is always assigned first to the department that asks for it, regardless of price?

thanks, mark

[nico5038]

Use a query with a descending "Received date" like:

SELECT TOP 1 FROM tblCartridgeAvailable ORDER BY ReceivedDate DESC;

Nic;o)