Hello all, I am a new registrant here at AF.net. Please bare with me, I hope not to be too annoying with requests.
I have an intermediate knowledge of MS Excel and it's many tricks (including some VBA work), however I have taken on the role of creating a DB for our purchasing department.
I was thinking the best avenue for accomplishing my goals efficiently was to find a good community online, review my plans, seek out external input. I understand the initial planning of tables and relationships is essential, and I will have a mildly involved interconnection of tables. I think once you review the plans below you'll see it's not TOO complicated.
[Breif summary of the task at hand] As it stands, I would like to be able to create a "project materials list" for a construction project, and then go through this table of items and check off various ones using a checkbox, once I have selected the items I want, I would like to create a RFQ (request for quote) table based upon those selections. I will need to be able to create many different RFQs from this master materials list. Eventually I will create multiple POs (purchase orders) in a very similar fashion from selecting out of the materials list (they will not necessarily convert directly from RFQs). Naturally I will need to be able to print a report of the project materials list, as well as each RFQ, and as well as each PO.
Essentially something like this is what I'm expecting to end up with:
[Master Vendor Table] Populated with vendors we will send the RFQs and POs to below.
[Project Table] Create a project, and include details about it.
[Master List Table] Populate a company master material list (consisting of all materials with basic details)
--> [Project Material Table] Create a project material list, consisting of items from the master list, with additional details added. This will need to be attached to the specific project (via ID?)
----> [RFQ Tables] Create request for quotes, consisting of items selected from the project material list. This will need to be attached to the specific project and/or project material list(via ID?)
------> [PO Tables] Create purchase orders, consisting of items selected from the project material list or from independent RFQs. This will need to be attached to the specific project and/or project material list(via ID?)
[Detailed summary of the task at hand] The workflow will look something like this:
--> Create and populate a Vendor List via user input form. These vendors will be the recipients of the Request for Quotes and Purchase Orders described below.
--> Create and populate a Master Material List via user input form. This will contain any and all items that may be included on any given construction job (we do a very unique construction of large metal buildings), this table will contain the general properties of each item. Over time this list will contain every single item we have ever used on a job/project. Exa:
ID TYPE SIZE WEIGHT
1 WBeam 6x9 9lbs/ft
--> Create a Project via user input form. This will contain project number, contact information, addresses, etc unique to each job.
--> Create a Project Material List via a user input form. The input form for the project material list will be initiated from a button on the project input or project review user form. This materials list will contain items only from the master material list, there will be many duplicates, each item added to this table will carry over its general properties from the Master List, and will also provide for the user to add detailed properties.
ID TYPE SIZE WEIGHT LENGTH FINISH TOTAL WT
1 WBeam 6x9 9lbs/ft 25ft galvanized =9x25 [225]
--> Create a Request For Quote. This part I'm not certain about. The data will be essentially identical to the item data in the project material list.
I'm thinking perhaps I could have a user form to create a custom named RFQ table, and then the user can open a table view of the Project Material List with checkboxes along the left hand side. Then the purchasing department can select which items they want added to this specific RFQ. Once they have selected the items they can click a button that will provide them the option of selecting which custom named RFQ table they would like to add the selected items to. I will also need a RFQ review form, where the user can add/delete items.
--> Create a Purchase Order. Need to be able to create purchase orders based on the RFQs. The data on these will include the price received from the RFQ process, otherwise the data will be about identical to the RFQ data. TWO OPTIONS HERE
Either (A) This will function exactly the same as the RFQ phase, it will not be chained or connected to any RFQ data, the relationship will be Project Inventory>PO.
Or (B) This will work similar to the Project Inventory>RFQ described above, only it will be RFQ>PO. I will need to be able to select any items from any of the project's RFQs and copy them over to a custom named purchase order.
Please note the POs are not just duplicates of the RFQs. For example I may send out an RFQ with 5 items to 3 vendors. Vendor A may have the best price for items 1-3, while Vendor B has the best price for item 4, and Vendor C has the best price for item 5. I would have sent out 3 identical RFQs to each of these 3 vendors, but I would eventually send out 3 different POs to each vendor. Vendor A's PO would be for the items 1-3 that we will buy from them, Vendor B's PO would be for the item 4 that we will buy from them, and Vendor C's PO would be for the item 5 that we will buy from them.
--> Printable Reports for all 3 Project Material List, RFQs, and POs. The printable reports for PML will be used in house for reference and supplied to erectors/shipping departments (printed to paper or to PDF). The printable RFQs will be faxed/emailed to various vendors (see above) to receive their pricing. The printable POs will be faxed/emailed to the vendors that win the job.
Once a job is completed, I would like to be able to LOCK it to prevent any changes being made, AND I would like to be able and go back, open a job, see the project material list for that job, see each of the RFQs we sent out, print them off if needed, and ditto for the POs.
So now that that's all out there, if anyone sees any glaring problems or obvious solutions to any of the processes above please feel free to provide any input or commentary. I'm not asking or expecting anyone to do it for me or explicitly lay out instructions, though I am starting from square one, so I wanted to toss this stuff to the sounding board and see what came back (or didn't)!
Thanks in advance.