Page 2 of 7 FirstFirst 1234567 LastLast
Results 16 to 30 of 101
  1. #16
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You are doing mix-and-match! First, do you have both quotes and orders on your system (4 tables) or only orders (2 tables)? You need to call them by the same name, e.g. tblOrders/tblOrderItems.



    The header, as you say, will contain the ID number, the OppID and other fields. Only the ID number will be stored on the Items table, as a foreign key (give it the same name as on the header table). In that way you will link the header information to the items.

    The code I posted for adding a new quote does this - adds a record to the header table, finds out what the new ID number is, then adds records to the Items table using the records from the Opportunities table (the selected OppID). When adding the header, add other fields there, such as rst!OppID=Me!SelectOpp, and any other fields that you have at that time. The form will show this new record and the user can enter other data as required.

    The query that is run after adding the header appends to the items table as described.

  2. #17
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Jul 2017
    Posts
    148
    Thanks again for directions. Much appreciated. I am going to digest and implement your instructions step by step. Still new to Access so getting my head around.

    Currently I have only 3 tables - "QuotesT (Fields - QuoteID, description)", "OpportunityT (Fields - OppID, Description)", and "OrdersT (Fields - ItemID, Description, UnitPrice, Qty, TotalPrice, OppID, QuoteID)

    Do I need to have more tables to accomplish what you have explained in your above comment? I want QuoteID and OppID to be in Header of my Orders Form and then I want Orders Form details to contain item details (ID, description, price etc.)

    Once I achieve this far, then I will attack on the code you have provided to make sure the form function properly as desired. Kindly advise. Thanks again.




    Quote Originally Posted by aytee111 View Post
    You are doing mix-and-match! First, do you have both quotes and orders on your system (4 tables) or only orders (2 tables)? You need to call them by the same name, e.g. tblOrders/tblOrderItems.

    The header, as you say, will contain the ID number, the OppID and other fields. Only the ID number will be stored on the Items table, as a foreign key (give it the same name as on the header table). In that way you will link the header information to the items.

    The code I posted for adding a new quote does this - adds a record to the header table, finds out what the new ID number is, then adds records to the Items table using the records from the Opportunities table (the selected OppID). When adding the header, add other fields there, such as rst!OppID=Me!SelectOpp, and any other fields that you have at that time. The form will show this new record and the user can enter other data as required.

    The query that is run after adding the header appends to the items table as described.

  3. #18
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    To make your structure totally normalized, you would have an Opportunities header table containing the OppID and the name/description of the opportunity, and an OppDetail table which would contain the structure as shown in post #4. Then on your order/quote header table you would have the OppID and on each item on the items table you would have the OppDetailID. One rule of normalization is to never repeat anything except for keys, and having only one Opp table the name would be repeated on each record - breaking that rule.

    Also, do not store calculated fields on tables, one example is Total Cost.

    Otherwise you are heading in the right direction. Doing it with pen and paper (post # 10) is the way to go, getting it all straight in your mind prior to attempt forms and queries.

    For instance, your main form would have three functions:
    1 - add new plain quote
    2 - add new quote from Opportunity (this is the code that I posted)
    3 - select an existing quote
    In each instance the subform will contain the items pertaining to that quote ID and by linking the subform to the main form it will automatically display the correct records.

    Now
    1) figure out if this handles all the scenarios that you wish to accomplish with this portion of the database (here your imagination needs to be used!)
    2) design the tables on paper, with links and joins - and again does this cover all scenarios
    3) design the forms on paper

    Once all of this is straight in your head you can come into Access and start doing the grunt work.

  4. #19
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Jul 2017
    Posts
    148
    Thanks indeed. I owe you big time for your help. I understood the whole process you explained and will tackle this way before jumbing on to Access. but I still have some confusion on table relationships. I am not able to understand if I need the last table (ItemT) shown in my image and if I do, whats its significance and what info it will store. Could you please look at the below relationship diagram and advise what's wrong. Also, I'm not completely certain about the relationship types. Once I figure this out, i'll goto the next step from your above comment. Thanks very much again for your time.

    Click image for larger version. 

Name:	Capture111.JPG 
Views:	36 
Size:	64.6 KB 
ID:	29807

    Quote Originally Posted by aytee111 View Post
    To make your structure totally normalized, you would have an Opportunities header table containing the OppID and the name/description of the opportunity, and an OppDetail table which would contain the structure as shown in post #4. Then on your order/quote header table you would have the OppID and on each item on the items table you would have the OppDetailID. One rule of normalization is to never repeat anything except for keys, and having only one Opp table the name would be repeated on each record - breaking that rule.

    Also, do not store calculated fields on tables, one example is Total Cost.

    Otherwise you are heading in the right direction. Doing it with pen and paper (post # 10) is the way to go, getting it all straight in your mind prior to attempt forms and queries.

    For instance, your main form would have three functions:
    1 - add new plain quote
    2 - add new quote from Opportunity (this is the code that I posted)
    3 - select an existing quote
    In each instance the subform will contain the items pertaining to that quote ID and by linking the subform to the main form it will automatically display the correct records.

    Now
    1) figure out if this handles all the scenarios that you wish to accomplish with this portion of the database (here your imagination needs to be used!)
    2) design the tables on paper, with links and joins - and again does this cover all scenarios
    3) design the forms on paper

    Once all of this is straight in your head you can come into Access and start doing the grunt work.

  5. #20
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You're welcome.

    The items table comes into play in two different scenarios:
    1 - a quote that is added using an opportunity. User will select an OppID from OppHeaderT, then click Add New, and all the records on OppDetailT with this OppID will be copied to ItemsT to create the line items for the quote (as in post # 4) - code already posted to do this
    2 - a quote that is not based on an opportunity - user will add a new quote (QuoteHeaderT) and will begin entering line items for this quote (ItemsT).

    The same form will allow the user to select a quote from an existing list (combobox) and the form will display that quote information from the header along with a subform that will contain all the items for that quote ID. Your confusion comes in because QuoteID is missing from ItemsT.

    OppHeader to OppDetail - one to many (many items belong to one Opp, as in post # 4)
    OppHeader to QuoteHeader - one to many (the same OppID wlll be used over and over for multiple quotes)
    QuoteHeader to Items - one to many (one quote will contain many line items)
    OppDetail to Items - one to many (one OppDetail record will appear multiple times on different quotes, same as Oppheader to QuoteHEader)

    The two Opp tables are considered to be static tables, i.e. they never change (except for maintenance that is seldom done). The two quote tables are transaction tables, records are continually being added and/or changed. So picture this, when you create a quote from an Opp you need all of those OppDetail lines to show up - and then you want to make changes, such as quantity, price, etc. You definitely do not want to make those changes to the OppDetail table, that must never change, it must be kept pristine for the next quote to use it. So you need somewhere to store the data, which is your ItemsT.

  6. #21
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Jul 2017
    Posts
    148
    Crystal clear. I have put this down on paper again. One question, should the fields in both OppDetailT and ItemsT tables be the same? I have ItemID in the ItemsT Table, should that be replaced with OppDetailsID? Just wondering because ItemsT will store values for the same fields that's in OppDetailT. If the tables and relationships look fine in my below diagram, then I will put into paper the forms designs. Could you kindly have a look. Thank you very much again.

    Click image for larger version. 

Name:	Capture999.JPG 
Views:	38 
Size:	82.3 KB 
ID:	29811



    Quote Originally Posted by aytee111 View Post
    You're welcome.

    The items table comes into play in two different scenarios:
    1 - a quote that is added using an opportunity. User will select an OppID from OppHeaderT, then click Add New, and all the records on OppDetailT with this OppID will be copied to ItemsT to create the line items for the quote (as in post # 4) - code already posted to do this
    2 - a quote that is not based on an opportunity - user will add a new quote (QuoteHeaderT) and will begin entering line items for this quote (ItemsT).

    The same form will allow the user to select a quote from an existing list (combobox) and the form will display that quote information from the header along with a subform that will contain all the items for that quote ID. Your confusion comes in because QuoteID is missing from ItemsT.

    OppHeader to OppDetail - one to many (many items belong to one Opp, as in post # 4)
    OppHeader to QuoteHeader - one to many (the same OppID wlll be used over and over for multiple quotes)
    QuoteHeader to Items - one to many (one quote will contain many line items)
    OppDetail to Items - one to many (one OppDetail record will appear multiple times on different quotes, same as Oppheader to QuoteHEader)

    The two Opp tables are considered to be static tables, i.e. they never change (except for maintenance that is seldom done). The two quote tables are transaction tables, records are continually being added and/or changed. So picture this, when you create a quote from an Opp you need all of those OppDetail lines to show up - and then you want to make changes, such as quantity, price, etc. You definitely do not want to make those changes to the OppDetail table, that must never change, it must be kept pristine for the next quote to use it. So you need somewhere to store the data, which is your ItemsT.

  7. #22
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    1 - should the fields in both OppDetailT and ItemsT tables be the same? Not necessarily. They have different purposes, design them for their own purpose. ItemsT is not always created from OppDetail, sometimes it is manually entered - does this make a difference? Are there any additional fields which pertain only to the quote item, such as delivery method, due date, etc.? In your thinking, treat them as two totally separate entities.
    2 - I have ItemID in the ItemsT Table, should that be replaced with OppDetailsID? ItemID is an autonumber field and is the primary key to the table. OppDetailID is a reference for when/if the item was originally copied over from OppDetails table. Answer your own question.

    I am now going to throw a wrench in the works! Now that you have this straight, there is a very important table that is missing, and is going to change the others. The table will be a Parts Table and will be a master list of all the parts you carry. Add this to your diagram and post it back.

  8. #23
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Jul 2017
    Posts
    148
    Thanks very much for the clarification on those two tables. Clear to me now. On the parts table, can I ask a question before getting started with PartsT table? I was thinking that the OppDetailID will be my PartsID; there are unique numbers assigned to different parts in our company which are created by our engineers. Therefore, I am thinking OppDetailID to act as PartNo for my database, and not to make it Autonumber.

    We have a separate MRP system in our company, which is used to do other transactional processes including purchase orders, sales order, invoice etc.

    For my database, I wanted the purpose to be purely for generating new quotes and accessing saved quotes. for these quotes, items are usually identified as Unique Drawing Numbers or PartNo.

    Considering the above background, could you kindly advise if I still need a Parts Table?

    Thanks very much again for your time and help again.



    Quote Originally Posted by aytee111 View Post
    1 - should the fields in both OppDetailT and ItemsT tables be the same? Not necessarily. They have different purposes, design them for their own purpose. ItemsT is not always created from OppDetail, sometimes it is manually entered - does this make a difference? Are there any additional fields which pertain only to the quote item, such as delivery method, due date, etc.? In your thinking, treat them as two totally separate entities.
    2 - I have ItemID in the ItemsT Table, should that be replaced with OppDetailsID? ItemID is an autonumber field and is the primary key to the table. OppDetailID is a reference for when/if the item was originally copied over from OppDetails table. Answer your own question.

    I am now going to throw a wrench in the works! Now that you have this straight, there is a very important table that is missing, and is going to change the others. The table will be a Parts Table and will be a master list of all the parts you carry. Add this to your diagram and post it back.

  9. #24
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I would suggest that yes, a table should be created. Is it possible to link to the table in your MRP system? That would be first prize! Or maybe you can have a daily dump from that system to this database.

    Post # 4 has a part number and a part description, your drawing in #21 has only a description, on both OppDetails and on Items. This is not good, you never want to repeat data, especially when a user is typing stuff - you will get 100's of variations for the same part. So somehow you have to prevent that.

    1 - quote created from Opp - copy the description
    2 - quote created manually - description must come from the same list as (1), user cannot be allowed to enter free-form text
    3 - additional items? if a quote is copied from an Opp and the user also wants to add additional items, this is a mix-and-match of (1) and (2).

    So there needs to be a master list.

    The problem with OppDetailID being the PartID is that one part will be mentioned on multiple OppID's, which would result in the same part having two different ID's, not good. Or one OppDetailID having multiple OppID's - ain't gonna happen!

    Bottom line, yes I think there should be a parts table. There can be an autonumber PartID to carry thru to the rest of your tables, but the part number and description must match your company's system, totally, with no discrepancies. People in companies get used to seeing the same things, it would be horrible if this database looked different to the others.

  10. #25
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Jul 2017
    Posts
    148
    Thanks very much again. I have understood the objective behind it but there's still few things which I think i'll understand once I make more progress. Our existing MRP system is a badly designed database by an outsourced company and has many redundant part numbers etc. which is often complained by employees; so I totally understand your message of good database design from the beginning. Thanks, this needs to be my practice.

    I have created a PartsT Table and created relationships with OppDetailT and ItemsT; would you mind kindly advising what's still missing or wrong? Thank you so much again. Much appreciated!

    Click image for larger version. 

Name:	Capture1234.JPG 
Views:	35 
Size:	102.2 KB 
ID:	29813

  11. #26
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Brilliant! Remove description from OppDetails table, it will come from Parts table. Same for Items table - unless non-part/miscellaneous line items will be entered? If so, what could they be? BTW, what is the description on the quote header table? Hope it isn't a copy of what's on OppHeader!

    It's very important to keep this table in sync with your existing MRP system, no matter how messed up it is! How do you see this happening?

  12. #27
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Jul 2017
    Posts
    148
    Hi, sorry just got the chance to wrap my head around after yesterday. Could you please review my updated table with below changes:-

    -Deleted description from OppDetailT and ItemsT Tables, because its in PartsT now
    -I have added two more fields in QuoteHeaderT (Customer Name, OppDescription). Aside from a unique QuoteID, i thought i'll have to uniquely identify quotes by CustomerName as well, which will also help users to filter quotes by submission to different CUSTOMERS. But i guess i'll have to add a customer table now?
    -Description in OppHeaderT Table and in QuoteHeaderT table are actually the same, i am calling them OppDescription in both of those tables now. BUT, will that be a problem or added work?
    -No non-part line items will be added to itemsT table, so thats a relief
    -Syncing with current MRP system will involve possibly some spending, other staffs time, and I'll be worried about possibility of rolling out my current project. But will be discussing the possibility with my manager next week, when i am planning to present my current project.

    Could you please review my updated table, really excited to plan out the next steps from your previous list Thanks so much for your time and help again.
    Click image for larger version. 

Name:	123.jpg 
Views:	33 
Size:	32.3 KB 
ID:	29820

    Quote Originally Posted by aytee111 View Post
    Brilliant! Remove description from OppDetails table, it will come from Parts table. Same for Items table - unless non-part/miscellaneous line items will be entered? If so, what could they be? BTW, what is the description on the quote header table? Hope it isn't a copy of what's on OppHeader!

    It's very important to keep this table in sync with your existing MRP system, no matter how messed up it is! How do you see this happening?

  13. #28
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Jul 2017
    Posts
    148
    Few other items that i wanted to highlight:-

    1. In my Quote Form, i also want the users to enter "MarkUp%", that will be in QuoteFooter. Quote Footer will include - TotalCost (formula), MarkUp% (user entered), MarkUp$ (formula), Total Price (formula). I want "Markup%" to be stored in database as well, because we might want to see % MarkUp used on a particular quote for a particular customer. I am thinking that i'll have to create a MarkupT Table (fields - MarkUp%, QuoteID, OppID). Please correct me for mistakes/direction. I believe the other items (TotalCost, MarkUp$, TotalPrice) that contain formulae can be taken care of by TextBoxes in my QuoteForm so i wont have to create table or fields for them?

    2. This is an important piece - In my database, few opportunity costs will be calculated from few SubOpportunity costs. For instance, Quote Form called 'OppDescription = Complete House Build - Type1' will take component costs from SubOpportunites such as Roof, Door, Floor, etc. My DB will have few such opportunities. I have OppID in my OppDetailT Table, should i add TotalCost and TotalPrice fields in my OppDetailT Table where i will call the totalcost/totalprice from sub opportunities? I have created a diagram to explain this. Sorry if i confused you, i will try explaining better if i did. Thanks so much again.

    Click image for larger version. 

Name:	1234.jpg 
Views:	33 
Size:	41.4 KB 
ID:	29821

  14. #29
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    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:
    For instance, Quote Form called 'OppDescription = Complete House Build - Type1' will take component costs from SubOpportunites such as Roof, Door, Floor, etc. My DB will have few such opportunities.
    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?

  15. #30
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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 ....

Page 2 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