Page 3 of 7 FirstFirst 1234567 LastLast
Results 31 to 45 of 101
  1. #31
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Jul 2017
    Posts
    148
    Thank you very much for validating my thinking.



    1. I want MarkUpPerc to be unique for a QuoteID for a customer. Therefore, I have added MarkUpPerc, CustomerID and CustName to my QuoteHeaderT Table. Also I have created a separate CustomerT Table where customer details will be stored. These fields are marked in RED in my attached diagram. Could you kindly review my updated structure now.

    Quote Header will allow user to enter a MarkUpPerc for an opportunity, for a customer, which will be saved with a unique QuoteID so the user could access anytime later as to how much MarkUp was applied to a particular opportunity for a customer. I also want to add Date to my quote, but not sure if it needs a table.

    2. Sorry for confusing you about the opportunities. Let me try to explain in a simpler way. It is CORRECT that OppDetailT has components (as shown in #4). Absolutely right. All these components(line items) shown in #4 apply to ONE opportunity. And I am hoping my DB so far would handle it well.

    Each Opportunity has multiple components (line items as shown in #4), BUT there will be a few opportunities whose TOTAL COST (that means COST OF OPPORTUNITY), will be calculated from few other opportunities.

    For Example, I am considering an opportunity 'House Construction' to explain this, which is not our business but similar process:-

    Please consider I have 4 OppIDs for different opportunities in a Main Opportunity called "Complete House Construction". OppID1 = House Roof, OppID2 = House Floor, OppID3 = House Doors, OppID4 = Windows. All of these opportunities have many line items (components) in OppDetailT Table. For instance, 'OppID3 = House Doors' has components (line items) including door frame, door handle, door latch, etc. (as shown in #4). Similarly OppID1, OppID2, OppID4 also have their components (as in #4), and these opportunities, with their components, are stored with unique OppIds in my database, as we already discussed.

    Now, there is an opportunity "OppID5 = Complete House Construction". Cost of this opportunity is calculated from the cost of other opportunities listed above (OppID1,2,3,4). While estimating Opportunity Cost of 'Complete House Construction', user will open House Floor, Door, Roof and few other opportunities (OppID1 2,3,4) to estimate the cost of these opportunities first and then will call the TotalCost of these opportunities to OppID5, where Grand sum of opportunities costs will be performed to get the total cost of OppID5 for a particular QuoteID. Hence, Total cost of house will be calculated by estimating and considering cost of various segments such as door, roof, floor etc. that makes the house, and they are also individual opportunities.

    To achieve the above, the Quote Form belonging to OppID5 will look different than the other opportunities. It will not have line items as shown in #4, rather it will have links to other OppIds, with a textbox to call TotalCost from OppID1,2,3,4, and then will perform grand total to get Cost of Pursuing OppID5. I hope this will not be difficult because i will have all Opportunities stored in my db, so it should only be the matter of calling TotalCosts from different OppIDs to a single form?

    Hope I haven't confused you more. Attached diagram of tables and opportunity form might make it clearer.

    I am very grateful for your help. This matters a lot. Thanks again. Please let me know if there is any confusion.

    UPDATED TABLE/RELATIONSHIP
    Click image for larger version. 

Name:	999.jpg 
Views:	28 
Size:	20.8 KB 
ID:	29833


    OPPORTUNITY FORM THAT I EXPLAINED ABOVE
    Click image for larger version. 

Name:	9999.jpg 
Views:	29 
Size:	42.6 KB 
ID:	29834

    Quote Originally Posted by aytee111 View Post
    1. Your thinking is absolutely on the nose! Keep up with this and your database design will be nice and stable. The one field, MarkUpPct (don't use special characters) which you would like to store on a table - what does it apply to, is there a table already that it can be added to without causing repetition of the other data fields, will it add meaning to that table or must it be in a separate table, what does this field have to do with OppID? There are some questions for you to think about.

    2. This is a search/display form showing what's already in the tables, so the question is - are your tables structured to provide such information? Is there enough detail to provide each component you have drawn on the screen? I don't quite understand it, the top portion is ONE opp/quote, the middle and bottom for multiple opps and quotes.

    This is part of the design (step 2 of the SDLC) - as in, I want to be able to show this field, does the table design allow it? The middle section where you have OppID's 1, 2 and 3 and you say they come from quote form 1, 2 and 3 - this is not strictly true. If you look at earlier posts (yours and mine) you will see that each opp appears many times with many different quote ID's, so they do not come from a form. In order to get the total cost for one opp you will have to create a query - does the current table design support this?

    Here's where I come unstuck:

    I thought ALL opportunities had multiple components (as in post # 4) but now you are talking about SubOpportunities - what are these, how do they differ from what is stored on OppDetailT?

    Then, are you trying to break the rule: never store calculated fields?

  2. #32
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Jul 2017
    Posts
    148
    Thanks very much. This was very timely.

    Quote Originally Posted by aytee111 View Post
    1. Your thinking is absolutely on the nose! Keep up with this and your database design will be nice and stable. The one field, MarkUpPct (don't use special characters) which you would like to store on a table - what does it apply to, is there a table already that it can be added to without causing repetition of the other data fields, will it add meaning to that table or must it be in a separate table, what does this field have to do with OppID? There are some questions for you to think about.

    2. This is a search/display form showing what's already in the tables, so the question is - are your tables structured to provide such information? Is there enough detail to provide each component you have drawn on the screen? I don't quite understand it, the top portion is ONE opp/quote, the middle and bottom for multiple opps and quotes.

    This is part of the design (step 2 of the SDLC) - as in, I want to be able to show this field, does the table design allow it? The middle section where you have OppID's 1, 2 and 3 and you say they come from quote form 1, 2 and 3 - this is not strictly true. If you look at earlier posts (yours and mine) you will see that each opp appears many times with many different quote ID's, so they do not come from a form. In order to get the total cost for one opp you will have to create a query - does the current table design support this?

    Here's where I come unstuck:

    I thought ALL opportunities had multiple components (as in post # 4) but now you are talking about SubOpportunities - what are these, how do they differ from what is stored on OppDetailT?

    Then, are you trying to break the rule: never store calculated fields?

  3. #33
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    148
    Thanks very much. This was very timely.

    Quote Originally Posted by ssanfu View Post
    PMFJI,
    A quick comment.
    "Description" is a reserved word, as is "Desc". In "PartsT", I would use "PartsDesc" and in "ItemsT" I would use "ItemsDesc".
    Link to reserved words http://www.allenbrowne.com/AppIssueBadWord.html


    Just watching the structure develop and enjoying the view ....

  4. #34
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    1 - excellent. Except for one broken rule, the rule of repetition ("do not repeat fields across tables except for PK's")
    "I also want to add Date to my quote" - same as before, post # 29 (1)

    2 - good explanation. What is the actual process?
    a) House construction will always contain opp's 1-4
    b) House construction is made up of user selecting multiple opp's which differ at quote entry time

    Select which one you want (or a mixture of both). The current design handles neither of these scenarios, ball is back in your court!

    (PS Don't get hung up on form design right now.)

  5. #35
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    148
    1 - Understood. Deleted 'CustName' from QuoteHeaderT Table and Added 'Date' to the QuoteHeaderT Table

    2 - Process is - a) House construction will always contain opp's 1-4
    This will be a pre-formatted Quote with a list of opps available to user (such as Opps 1-4). House Construction was an example. In my DB, there will be a few such opportunities as House construction that will contain multiple opp's (such as opp's 1-4). User can open those opps, complete the estimation (by changing quantities etc.), and then come back to main opp (such as house construction) to get the summary cost. That's it!

    3 - I have added another field to my QuoteHeaderT Table called "No.Assembly" (for No. of Assembly). This will allow user to auto populate quantities of each line item in a quote for multiple assemblies. for example, if user selects 'No.Assembly' = 3 in a doors opp, then the quantities of each line item/component will be auto populated for 3 door assemblies. Hope I am clear.

    Please advise. Thank you so much.

    Click image for larger version. 

Name:	888.jpg 
Views:	27 
Size:	39.9 KB 
ID:	29839

    Quote Originally Posted by aytee111 View Post
    1 - excellent. Except for one broken rule, the rule of repetition ("do not repeat fields across tables except for PK's")
    "I also want to add Date to my quote" - same as before, post # 29 (1)

    2 - good explanation. What is the actual process?
    a) House construction will always contain opp's 1-4
    b) House construction is made up of user selecting multiple opp's which differ at quote entry time

    Select which one you want (or a mixture of both). The current design handles neither of these scenarios, ball is back in your court!

    (PS Don't get hung up on form design right now.)

  6. #36
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    1 - well done!
    2 - where's your new table design drawing to handle this?
    3 - this was alluded to earlier during the discussion of why there needed to be 2 detail tables. Good resolution.

  7. #37
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    148
    2 - I have attempted to include it into my new table design but I might be way off I have added an 'Opp2DetailT' table for this type of opps that I explained. This table is linked with OppHeaderT and Opp1DetailT (renamed OppDetailT to Opp1DetailT). Could you please advise where i am off the track. Thank you.

    Click image for larger version. 

Name:	8888.jpg 
Views:	27 
Size:	32.4 KB 
ID:	29847

    Quote Originally Posted by aytee111 View Post
    1 - well done!
    2 - where's your new table design drawing to handle this?
    3 - this was alluded to earlier during the discussion of why there needed to be 2 detail tables. Good resolution.

  8. #38
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Think of it more in a top-down setup. And you need to change the name of it to make it totally separate. I suspect that the name is what is causing you confusion.

    From the top:
    this new thing
    which has many
    opportunities
    which has many
    opp details

    Now your quote tables will change too:
    one quote
    may/may not have one "new thing" (this is optional, right? not all quotes will have one of these?)
    which has many
    OppID's
    which has many
    details

  9. #39
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    148
    Tried to map this part of table design through the process flow. Looks any closer? Thank you!

    Click image for larger version. 

Name:	2222.jpg 
Views:	26 
Size:	30.2 KB 
ID:	29849

    Quote Originally Posted by aytee111 View Post
    Think of it more in a top-down setup. And you need to change the name of it to make it totally separate. I suspect that the name is what is causing you confusion.

    From the top:
    this new thing
    which has many
    opportunities
    which has many
    opp details

    Now your quote tables will change too:
    one quote
    may/may not have one "new thing" (this is optional, right? not all quotes will have one of these?)
    which has many
    OppID's
    which has many
    details

  10. #40
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Let's forget about quotes for now.

    The way I wrote the requirements do not match your table layout:
    From the top:
    this new thing
    which has many
    opportunities
    which has many
    opp details
    Either my read on this is incorrect or your table design is incorrect. Please write out how your system works, in sentences first, then we can worry about the tables. What you see each table containing,
    e.g. BiggOpp House Construction will contain....

  11. #41
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    148
    The requirement is exactly as you explained:

    BigOpp 'House Construction'
    which has many
    SmallOpps (roof, floor, doors etc.)
    Which has many
    SmallOpps Details
    that contains many
    Parts/items

    You are right that not all quotes will have BigOpps. Some quotes will only have SmallOpps. Did I answer your question? Thank you!

  12. #42
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Actually, it seems that it is only the BigOppDetail that I don't get, the rest is correct.

  13. #43
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Posted at the same time!

    Get this table sorted out and then we can move on to quotes.

  14. #44
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    One more question:
    SmallOpps Details
    that contains many
    Parts/items
    One detail can have many parts associated?

  15. #45
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    148
    I am creating this 'BigOppDetailT' Table and linking it to the 'BigOppHeaderT' Table for the same reason we created 'SmallOppDetailT' Table and linked it to 'SmallOppHeaderT' Table. I am thinking these two as separate opportunity types and the quote form layout will look different for both type of opportunities. When I am visualizing table design and explaining you, i am only able to visualize thru' how application will work, possibly that's why some confusion in my communication.

    Quote Originally Posted by aytee111 View Post
    Actually, it seems that it is only the BigOppDetail that I don't get, the rest is correct.

Page 3 of 7 FirstFirst 1234567 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-09-2016, 01:50 PM
  2. form - save entered data only with save button
    By cbrxxrider in forum Forms
    Replies: 3
    Last Post: 10-20-2013, 12:39 PM
  3. Replies: 3
    Last Post: 10-08-2012, 08:40 AM
  4. Unbound Form, but wish to save records...how?
    By LostInAccess in forum Forms
    Replies: 4
    Last Post: 07-18-2012, 09:55 AM
  5. Replies: 9
    Last Post: 01-20-2011, 02:22 PM

Posting Permissions

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