Using Access to figure out lots of different prices!

[Miss_Saski]

Hello - new forum user here so I apologise for any mistakes I may make



I have recently got a new job at a kitchen fitters. My role is in the office, managing payments and orders and all the jobs that come from them.
My boss has been using a very old Excel document for everything - invoices, receipts, quotes, etc. I admire who designed the spreadsheet as it works perfectly but the prices are very out of date and many products are no longer sold or have been added so it actually makes it hard work using it now. The amount of products we now sell and the amount of orders coming in, I believe Access would be better to use in the long run. I used Access and did my A-Level Computing project using it, but it has been many many years since then and although I can remember the basics, I cannot figure out the more complicated stuff.
What would help me the most at the moment is figuring out the door prices for the kitchen units and this is what I am seeking advice with today.

We get given a catalogue from our many suppliers with all the prices for each sized door.
Each kitchen unit can have up to 5 different sized doors.
I would like to be able to copy the prices from the catalogue into one table and the prices to be correct for each unit.

For example, a 3 drawer base unit has a price of £120. The door sizes are 120x560, 240x560, 240x560. A shaker-style, solid wood drawer front in grey would cost £125 (£25 for the 120, and £50 each for the 240s) so £245 in total for the unit, but in a slab-style, PVC and orange, it would be £75 (£15 for the 120, and £30 for each of the 240s) or £195 for the whole unit.

If anyone has any free time to talk me through how to set this up or just some advice on Access it would be greatly appreciated.

[mike60smart]

Hi

Would you be able to upload a zipped copy of the Excel Sheet with any Confidential data removed?

[Miss_Saski]

2017 price list blank.zip

Hopefully this works. This is the whole workbook although you should only need the DoorPrices tab to see what I want for now. Whoever created the spreadsheet originally input most of the prices manually.

[mike60smart]

On Looking at the Doors Tab it would appear that you have the following:-

Lookup a List of Door Ranges and then select the DoorCode which then populates the description and Cost

In Access this is achieved by using Cascading Combobox's

To save having to manually enter all the prices for each product from each Supplier I am sure that Suppliers can provide you with and Excel sheet of their Products with corresponding Prices.

These can then be Imported into Access and appended to tables as necessary.

[Miss_Saski]

Unfortunately, trying to get some suppliers to provide this information in a spreadsheet is near impossible. One did very kindly print off the prices in a spreadsheet and post them to me (completely missing the point of the request!) but most just send more catalogues in the post

[mike60smart]

Hi Saski?

In the spreadsheet there appears to be lots of duplication

Can you explain the purpose of each of the following Tabs:-

QuoteNoDoors
Quote
ApplianceQuote
WorkshopSheet
CustList
JobSheet

[orange]

Miss_Saski,

I recommend that you watch this Creating a Stock Control Database video from software-matters. This will help you recall your Access knowledge, and will show the essence of building a database of Customers, Orders, Parts and Suppliers. Although they mention mdb, you should opt for using accdb. They also mention User Level Security which was a component of mdb, but like mdb itself, has been deprecated.
There are many great tips in the video, but I would recommend against using Lookup fields in your tables.

There are a number of articles in the Database Planning and Design link in my signature that may be helpful.
Good luck with your project.