Hi All, I am looking for some guidance on a basic database to use as a back end for an Excel frontend. I currently have a complex spreadsheet with tables that I use to populate cascading drop down lists. The tabes contain different timber and sizes. When I get a new range of timber, I copy and rename the spreadsheet and then change the tables. I would like to have the ability to have a userform with pages for each timber type so I can turn on the different sizes and grades.
I have some ability with VBA in Excel so I am sure I can handle the userforms. What I am not sure about is designing the database. Attached is an example Excel workbook with two sheets. sheet "tables" is an example of the way the timber is currently used. Sheet "working sheet" is a simple example of the front end. There will be a few more entries and in time I would like to create a new userform to create new Access tables as new types of timber may be called for in future. I really want to use Excel as the front end for various reasons.
Am I correct that each column in my "tables" sheet should be table in the access database. Example: Access Table 1 called "Material" and first field will be "MaterialID" and field two would be range A2:A5. The same for all other columns in my "tables" worksheet. I assume I will also need to have an Access Table called "Customer" with a 'customerID" field and a "customerName" field.
Could anyone give me some advice on how best to plan the access database and then I can go ahead and create it. I could then upload what I create to then discuss the relationships.
Also, does anyone have a similar style Database I could look at as an example?
Thank you in advance.