Results 1 to 12 of 12

Planning my first Access DB

  1. #1
    swankinrosco is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    5

    Planning my first Access DB

    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.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    At the heart of any relational database application is the table structure (tables, fields and relationships), so it is important to make sure it is correct before you even worry about forms, reports, queries etc. I will focus on the table structure moving forward.

    With that said there are rules that need to be followed when designing your table structure. This site has a general over view of the rules of normalization.

    Some other general recommendations
    1. Do not uses spaces or special characters in your table or field names (I follow the same rules for query, form and report names as well)
    2.Do not use reserved words as table or field names. This site has a list of reserved words
    3.Do not use lookups (combo/list boxes) in your tables. They are best left for forms. This site has more detailed info as to why table level lookups are not a good idea.
    4.Use descriptive field names, just using ID is insufficient

    I use prefixes for the field names that identify the data type of the field

    pk=primary key field (always autonumber)
    fk=foreign key field (always a long integer number)
    txt=text field
    lng=long integer number, but not a foreign key field
    curr=currency
    sp=single precision number
    dp=double precision number
    dte=date/time
    log=logical/boolean i.e. yes/no field

    Starting with this:
    [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?)
    I assume that you buy your materials from vendors and do projects for customers. Given that vendors and customers are companies, we need 1 table (one of those rules) to hold company information

    tblCompany
    -pkCompanyID primary key, autonumber
    -txtCompanyName
    -txtAddress
    etc.

    Now since you have 2 types of companies (vendors and customers), we can add a field to tblCompany to distinguish them, but I must first ask a question. Can a company EVER be both a customer and a vendor to you?


    For the materials, we need a table to hold all materials (we will select specific ones from this table to add to projects/RFQs etc).

    tbl Materials
    -pkMatID primary key, autonumber
    -txtMaterialName

    You will have to provide more detail on what info you plan to capture for a material.

    If you are doing RFQ to vendors you will probably have many vendors that can supply the same material; that describes a one-to-many relalationship. Further a vendor may supply many materials to you, so another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (material and vendor in your case), you need a junction table.

    tblMaterialCompany
    -pkMatCoID primary key, autonumber
    -fkCompanyID foreign key to tblCompany (i.e. the vendor)
    -fkMatID foreign key to tblMaterials

    Now, over time or from project to project, the vendor may have different pricing for a material, so another one-to-many relationship (a vendor/material combination will have many prices). Those prices are related to a particular RFQ I assume.


    Now a question, can an RFQ for a material apply to many projects or is each RFQ applicable to only 1 project?

  3. #3
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,015
    Looks like your sending out RFQ's and PO's for the materials you need for each project.

    You would need the following tables:

    Vendor Table
    Materials Table
    Linking table between Vendor and Materials as you would probably have multiple vendors for each material and multiple materials for each vendor.

    Project Table Including a status field which would tell you what was going on with the project (RFQ's Sent out, Puchase Orders Sent out, Project Completed maybe some others like Canceled, In progress depends on your needs)

    Puchase Order Table to show which materiels were actually purchased for the project

    RFQ Table to show which vendors were sent an RFQ the materials in the RFQ and if the RFQ was accepted or rejected.

    You might have to break the Project, Purchase Orders and RFQ Tables into a Master Detail. Depending on your final structure.

    All of your RFQ's, PO's and Reports would be based on those tables.

    RFQ and PO tables actually should be combined into 1 table with a type of RFQ or PO. Since the fields would essentially be the same but that's your choice.

  4. #4
    swankinrosco is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    5
    Quote Originally Posted by jzwp11 View Post
    Welcome to the forum!
    Thanks so much for the detailed response! My many thanks in advance.

    I must first ask a question. Can a company EVER be both a customer and a vendor to you?
    Nope


    If you are doing RFQ to vendors you will probably have many vendors that can supply the same material; that describes a one-to-many relalationship. Further a vendor may supply many materials to you, so another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (material and vendor in your case), you need a junction table.

    tblMaterialCompany
    -pkMatCoID primary key, autonumber
    -fkCompanyID foreign key to tblCompany (i.e. the vendor)
    -fkMatID foreign key to tblMaterials
    I am very novice, but I believe this is overkill. For each RFQ we create, we will send an identical copy to maybe 3-6 vendors. I don't believe I have the need to attach any vendor information to any data within the DB.

    My plan is to create the RFQ, then from a form select which vendor to "make it out to" then print the report that would have that vendors info attached to it. And proceed to fax/email it out.

    Once the vendors respond to our RFQs we will mix and match the best prices to the vendors selling at those prices, and create a new PO for each order. Our POs will be comprised of the items from the RFQs, but each PO will only have a % of the items from the original RFQ. Essentially each RFQ will break down into two or more POs.

    Perhaps my limited ability to visualize this stuff in my head is holding me back? I just don't see the need to attach vendor data to the materials, other than at the point of creating the actual PO and RFQ reports.

    Now, over time or from project to project, the vendor may have different pricing for a material, so another one-to-many relationship (a vendor/material combination will have many prices). Those prices are related to a particular RFQ I assume.
    Our material prices fluctuate from order to order, so every project requires us to send out RFQs and submit POs. The pricing we apply to the PO is based entirely on the results of our submitted RFQs.

    Now a question, can an RFQ for a material apply to many projects or is each RFQ applicable to only 1 project?
    Each project is unique, each project material list is unique, each project RFQ is unique, and each PO is unique.
    ********************


    Quote Originally Posted by RayMilhon View Post
    Looks like your sending out RFQ's and PO's for the materials you need for each project.
    Thanks so much for the time you took to help me with feedback. You had some really good ideas in here about features I could add above and beyond my intended goals.

    You would need the following tables:

    Vendor Table
    Materials Table
    Linking table between Vendor and Materials as you would probably have multiple vendors for each material and multiple materials for each vendor.
    Again, as per my response to jzwp11's post above, I'm not sure its necessary to link vendors to materials. I will have 1000's of materials over time. This is for large metal building construction projects.

    Project Table Including a status field which would tell you what was going on with the project (RFQ's Sent out, Puchase Orders Sent out, Project Completed maybe some others like Canceled, In progress depends on your needs)
    Excellent idea, something to really think about for later on. You'll see in my attachment below I have a status field for each RFQ and PO, perhaps this can be aggregated into a general "Project Details" view form. I know very little about how I will query the data and pull it into forms, but first things first.

    Puchase Order Table to show which materiels were actually purchased for the project

    RFQ Table to show which vendors were sent an RFQ the materials in the RFQ and if the RFQ was accepted or rejected.

    You might have to break the Project, Purchase Orders and RFQ Tables into a Master Detail. Depending on your final structure.

    All of your RFQ's, PO's and Reports would be based on those tables.

    RFQ and PO tables actually should be combined into 1 table with a type of RFQ or PO. Since the fields would essentially be the same but that's your choice.
    I would really appreciate if you looked at the diagrams and info I attached below and please offer me your feedback.
    ********************


    Based on all the help I received from you guys, I brainstormed the following (rough) table diagram:



    Uploaded with ImageShack.us


    Notes on the diagram below--

    Category table. This will be a placeholder for the various types of categories used (steel, channel, rod, pipe, cable, coldform, etc)

    Master Items table. This is the master list of all items that may be ordered. Nothing can be ordered if it is not in this table.

    Projects table. This is the master table of all projects.

    Project Lists table. This table will hold the material list for each project IDs. I have the option to create more than one material list for each project should I choose to do so, otherwise each project will typically only have just one material list.

    Project Items table. This table will hold the actual materials associated with the project material list(s). It will include the basic material information from the Master Items table, and will also contain additional information for each item as they are added to their respective project material list (color, weight, size, length, etc)

    RFQ Lists table. This table will hold the RFQ IDs for each project's various RFQs. It is linked to the project ID so as only to be bound to the specific project, in case I want to have multiple project material lists on a project. I should then be able to add items to the RFQ from multiple project material lists (so long as they are all from the SAME project).

    RFQ Items table. This table will hold the actual materials added to the RFQ for the vendors to price out. This data is linked to the project items table as it will pull the materials from this table.

    PO Lists table. This table will hold the PO IDs for each project's various POs. It is linked to the project ID so as only to be bound to the specific project.

    PO Items table. This table will hold the materials data that will go on the POs we submit to the vendors once we have selected what items will be purchased from what vendors. It is linked to RFQ item ID as the data in here will be pulled from the various RFQs created.



    Thoughts and opinions!?

    Thanks in advance!
    Last edited by swankinrosco; 01-29-2012 at 02:55 PM.

  5. #5
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I just don't see the need to attach vendor data to the materials, other than at the point of creating the actual PO and RFQ reports.
    In order to do a report from Access the data must exist, so you might as well have it in there when you create the RFQ and update it with the pricing when the vendors respond to the RFQ.


    How can both of the following statements be true? If an RFQ is unique to a project and you get several responses to your RFQ with each being tied to a vendor and a PO is unique (and applies only to 1 vendor) how can you pick and choose the pricing across multiple RFQ responses?


    Once the vendors respond to our RFQs we will mix and match the best prices to the vendors selling at those prices, and create a new PO for each order.
    Each project is unique, each project material list is unique, each project RFQ is unique, and each PO is unique.
    I'm confused!

  6. #6
    swankinrosco is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    5
    Quote Originally Posted by jzwp11 View Post
    How can both of the following statements be true? If an RFQ is unique to a project and you get several responses to your RFQ with each being tied to a vendor and a PO is unique (and applies only to 1 vendor) how can you pick and choose the pricing across multiple RFQ responses?

    I'm confused!
    Okay maybe this will clear it up.

    Current workflow: Take project material list, break it down into several different RFQs based on material types. One for steel, one for hardware, one for coldform, one for wood, one for cable, one for rafters. Each RFQ will have multiple items of each type on it. Send out each RFQ to the vendors that correspond with the RFQ material type.

    So, example. I could send out an RFQ with 7 different pieces of steel to 3 different vendors. Vendor A might have the best price on pieces 1-3, Vendor B might have the best price on 4-5, and Vendor C might have the best price on 6-7. In which case I would then create 3 POs. PO#1 to Vendor A for pieces 1-3. PO#2 to Vendor B for pieces 4-5. PO#3 to Vendor C for pieces 6-7.

    Does that make sense? I any event, no RFQ will be used on multiple jobs, the RFQs are unique to the job (they are comprised of the items on the project's unique material list). There will be various RFQs, each for a different type or grouping of materials. These RFQs will be sent to relevant vendors for those grouping of materials. Vendors X Y & Z are the structural steel guys we send RFQs to, while Vendors T U & V are the roll-form steel guys we send RFQs to. Vendors XY&Z would each receive the same RFQ#1, and Vendors TU&V would receive the same RFQ#2. RFQ 1 and 2 are different from one another (obviously).

    Am I making any sense? I'm on a prescription that's making me a bit loopy, apologies in advance!

  7. #7
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,015
    One suggestion I have that will save you many headaches is before you go any further read up on Database Normalization and make sure your data structure conforms to at least 3rd Normal form. Otherwise you will feel loopy even when your not on your prescription.

    For example your purchase orders should consist of 2 tables 1 for the Purchase Order and 1 for the Line Item Detail

    something like

    Purchase Order Table
    PONumber
    PODate
    PaidDate
    Vendor
    PaidAMT

    Purchase Order Detail Table
    LineItemNumer
    PONumber Foreign Key to Purchase Order Table
    MatID Foreign Key to Materials table
    Quantity

    Other fields as you require just make sure they're in the right table.

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I've attached a database that includes a first draft of how I would set up your application. It will probably still need some work.

  9. #9
    swankinrosco is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    5
    Quote Originally Posted by jzwp11 View Post
    I've attached a database that includes a first draft of how I would set up your application. It will probably still need some work.
    Wow that's excellent. My first thought was how close my setup resembled yours, that was a huge relief to see I'm not far off base here.

    I combined our two attempts and came up with the following (see attached img).



    Uploaded with ImageShack.us

    The primary changes I made were:

    1) I added a junction table for RFQs between project materials table (project items) and RFQs assigned table. Unless I am mistaken, this will allow me to first create the RFQ, for example "RFQ Steel". Then I can take that RFQ and apply it to whichever vendors I deem necessary at that time via the RFQAssigned table. Is this not an intelligent way of assigning one RFQ to multiple vendors? My end goal is to be able to create various reports of an identical RFQ, with the only difference being WHO that RFQ is being submitted to (this will be displayed on the printed report).

    2) Removed price from RFQ. There will not be any pricing here, this is for the vendors to submit back to us. (there will be a blank "price" box on each item-listing where they will fill in by hand OR they will fax back their own listing with pricing. We will take these prices and input them into the POs)

    3) I changed the way the PO material list inherits its materials. Your setup had these items coming from the general project material list, however the POs will inherit their material lists from the superseding RFQ from which they are compiled.

    EXA: Steel PO #1 and #2 will be compiled from items all found on Steel RFQ.



    General Question on data input: I am unfamiliar with the options we will have in form creation. How do you view the GUI/workflow of the follwing?

    A) Creating the project material list from items in the master material list? will this be done by hand? Will this be a simple item-list-box where I can click "add item" ?

    B) Creating the RFQs from the project material list, and POs from the RFQs... same question as above?

    C) Would it be possible to create a new item in the master material list WHILE inputting it into the project-specific material list? IE: make it go upstream and downstream? This could help us with redundant data entry because as far as I can tell, we will first need to add every item into the master material list table, AND THEN go and add it to the project material list table. That is NOT a major issue, but it would be pretty neat if I could just directly input it into the project list and have it automatically go upstream into the master list as well. If not possible, perhaps the best solution is when at the project-material-list-input-form to have a small pop-up form for inputting new materials to the master-material-list..


    I'm wondering if I should be thinking ahead about these things...

    Thanks again for all your help so far!

    *********

    Update:

    Okay, I'm entirely confused how I could create a single input form that has the following blank text fields-- [txtCompanyName] [txtCompanyDetails] and a combo-dropdown for [txtCompanyType]... I am lost



    Uploaded with ImageShack.us

    Update 2: I saw the prefix typos on item categories id and company id, both are now fixed on my end
    Last edited by swankinrosco; 01-29-2012 at 05:56 PM.

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    OK on your #1.

    Regarding #2, as you will have many RFQ's to evaluate, why not put in the prices and have Access find the lowest for you?
    Regarding #3, my reasoning for going back to the project-material list is so that you will have not more or no less than those materials listed in the project. If you go back to the RFQ's, you have many materials over many RFQ's all under the same project which could open you up to potential errors (too many materials or too few materials being purchased for a given project).

    With respect to A, I would probably have a form to enter your materials.
    I would have a separate project main form and in that form would be a subform base on the tblProjectMaterials table. Within the subform you would use a combo box based on the tblMaterials to populate the materials for the project. You could also use the On Not in List event of that combo box to open a form to enter new materials if you wanted to do it at the time of project creation (relative to your question C).

    Basically the same approach for B): main form/subform setup with a combo box in the subform. But you would base the combo box on a query that joins tblProjectMaterials & tblMaterials so that you only choose materials already linked to the project (hence my response relative to your question #3)

    Okay, I'm entirely confused how I could create a single input form that has the following blank text fields-- [txtCompanyName] [txtCompanyDetails] and a combo-dropdown for [txtCompanyType]... I am lost
    You would have a form based on tblCompany. You can use the form wizard and then remove the control for the type and replace it with a combo box based on the company type table.

  11. #11
    swankinrosco is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    5
    Quote Originally Posted by jzwp11 View Post
    OK on your #1.
    Cool

    Regarding #2, as you will have many RFQ's to evaluate, why not put in the prices and have Access find the lowest for you?
    Given #1 above, Will it be possible to have access evaluate the prices of the multiple assigned RFQs - as they will now be separate tables? Considering #1 above - if I turn "Steel RFQ" into 3 "assigned" RFQs (say Steel RFQ A, Steel RFQ B, Steel RFQ C), once the prices come back to me from the vendors and I input them into their 3 respective assigned RFQ tables, how will it compare and evaluate to determine lowest price? and where will this price be provided? The parent "Steel RFQ" ?


    Regarding #3, my reasoning for going back to the project-material list is so that you will have not more or no less than those materials listed in the project. If you go back to the RFQ's, you have many materials over many RFQ's all under the same project which could open you up to potential errors (too many materials or too few materials being purchased for a given project).
    Understand your logic, not a significant derivation. I was considering the workflow via GUI may be simpler going from RFQ to PO, but obviously I really have no clue on that and ultimately this is something easy to tweak down the road once the GUI aspects start to come to life I reckon.

    With respect to A, I would probably have a form to enter your materials.
    I would have a separate project main form and in that form would be a subform base on the tblProjectMaterials table. Within the subform you would use a combo box based on the tblMaterials to populate the materials for the project. You could also use the On Not in List event of that combo box to open a form to enter new materials if you wanted to do it at the time of project creation (relative to your question C).
    Alright, sounds simple enough. I played around with form creation today and had trouble when I wanted to base a form off of a table containing a foreign key to another table. I'm not sure exactly how to set it up since it's the fkID on the form's primary table, yet I want to select the name or some value from the fkID's main table. I think I stay away from queries here, so is it possible to create the form off of multiple tables?

    Basically the same approach for B): main form/subform setup with a combo box in the subform. But you would base the combo box on a query that joins tblProjectMaterials & tblMaterials so that you only choose materials already linked to the project (hence my response relative to your question #3)
    Well, shoot. I guess I use queries in the form after all. I had a heck of a time today attempting to understand how to do this.


    You would have a form based on tblCompany. You can use the form wizard and then remove the control for the type and replace it with a combo box based on the company type table.
    Alright, so the form is based entirely on the main table (Company) and for the inherited text value referenced from fkCompanyTypeID, I use a separate combo-box control, whose values I direct to "tblCompanyType//txtCompanyType" .. My question is then how does the form know to read the selected value of "tblCompanyType//txtCompanyType" then take the value of "tblCompanyType//pkCompanyType", and finally write this value as "tblCompany//fkCompanyTypeID" .. I'm guessing this will work similarly to the notions above about implementing queries into the form/combo-box. I'm sure once I get it set right and see how it all works the lightbulb will come on and everything will fall into place!

    Can't say thanks enough for helping me along thus far!

  12. #12
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Given #1 above, Will it be possible to have access evaluate the prices of the multiple assigned RFQs - as they will now be separate tables? Considering #1 above - if I turn "Steel RFQ" into 3 "assigned" RFQs (say Steel RFQ A, Steel RFQ B, Steel RFQ C), once the prices come back to me from the vendors and I input them into their 3 respective assigned RFQ tables, how will it compare and evaluate to determine lowest price? and where will this price be provided? The parent "Steel RFQ" ?
    You would use an aggregate query with the Min() function and probably group by project and material.

    Understand your logic, not a significant derivation. I was considering the workflow via GUI may be simpler going from RFQ to PO, but obviously I really have no clue on that and ultimately this is something easy to tweak down the road once the GUI aspects start to come to life I reckon.
    The table structure takes precendence over the GUI. It is up to the developer to make the GUI as user friendly as possible but maintain the correct table structure.

    Alright, so the form is based entirely on the main table (Company) and for the inherited text value referenced from fkCompanyTypeID, I use a separate combo-box control, whose values I direct to "tblCompanyType//txtCompanyType" .. My question is then how does the form know to read the selected value of "tblCompanyType//txtCompanyType" then take the value of "tblCompanyType//pkCompanyType", and finally write this value as "tblCompany//fkCompanyTypeID" .. I'm guessing this will work similarly to the notions above about implementing queries into the form/combo-box. I'm sure once I get it set right and see how it all works the lightbulb will come on and everything will fall into place!
    You will want to use the combo box wizard. It will first ask you what table or query on which to base the combo box. In the company form, you will want to base the combo box on the company type table. Be sure to include the primary key as well as the text field. The wizard will ask which field (i.e. pkCompanyTypeID) of the company type table needs to be related to a given field in the forms underlying recordsource (i.e. the company table). This is where you would select fkCompanyTypeID. What Access will do is then tie the pkCompanyTypeID field of the combo box to the fkCompanyTypeID of the form/tblCompany. The pkCompanyTypeID thus becomes the bound field/column of the combo box.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Initial "Customers" Table Planning
    By justgeig in forum Database Design
    Replies: 7
    Last Post: 01-18-2012, 03:33 PM
  2. Project Milestone Planning
    By Clucky in forum Database Design
    Replies: 1
    Last Post: 07-19-2011, 02:01 PM
  3. Planning/strategy?
    By Bryan021 in forum Access
    Replies: 4
    Last Post: 04-18-2011, 11:40 AM
  4. Creating a calendar-like layout with a planning
    By ThermalSloth in forum Access
    Replies: 0
    Last Post: 11-24-2009, 06:22 AM
  5. advice on planning tables
    By justinm1015 in forum Access
    Replies: 1
    Last Post: 09-16-2009, 02:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums