Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 41
  1. #16
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Based on this information, would you change the structure we laid out before?
    There would be no need to change the structure I proposed.

    2. Another complexity in this matter is that some of the full project numbers that come in through my weekly data pull are inaccurate. While they are supposed to conform to the structure I presented to you earlier, there are often setup incorrectly. For this reason, there exists a need to have an overrides table.



    The override table would contain the false project number and a corrected number. I'm not sure how I'd include this and connect it to the rest of the data. Does this make sense?
    You could just correct the info before you import it into the tables or if you do not want to do that you could set up a table that holds the alternate (but incorrect) project numbers and tie those to the main project table. Each incorrect project number would be a record in this table

    tblAltProjectNumber
    -pkAltProjNoID primary key, autonumber
    -fkCustDivProProjID foreign key to tblCustomerDivisionProgramProjects
    -txtAltProjNo

    3. Also, at the project level I need to assign additional information such activities, applicants, etc. This value would be something I'd find in an Applicants/Activities table that has a finite number of entries. Those tables would be used for looking up values only.
    If you have more than 1 applicant or activity, you would have a one-to-many relationship which would be in a separate but related table

    tblCustomerDivisionProgramProjectsActivities
    -pkCustDivProgProjActID primary key, autonumber
    -fkCustDivProProjID foreign key to tblCustomerDivisionProgramProjects
    -fkActivityID foreign key to a table that holds a list of activities
    other fields related to the activity for the particular project

  2. #17
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21
    Quote Originally Posted by jzwp11 View Post
    If you have more than 1 applicant or activity, you would have a one-to-many relationship which would be in a separate but related table

    tblCustomerDivisionProgramProjectsActivities
    -pkCustDivProgProjActID primary key, autonumber
    -fkCustDivProProjID foreign key to tblCustomerDivisionProgramProjects
    -fkActivityID foreign key to a table that holds a list of activities
    other fields related to the activity for the particular project
    Can I not just have another field in tblCustomerDivisionProgramProjects for activity id that's a foreign key to the activities table? Why do I require the table above? The activity is defined at the same level the project is.


    tblFinancialDataTypes
    -pkFinDataTypeID primary key, autonumber
    -txtFinDataTypeName

    tblProjectFinancialData
    -pkProjFinDataID primary key, autonumber
    -fkkCustDivProProjID foreign key to tblCustomerDivisionProgramProjects
    -fkFinDataTypeID foreign key to tblFinancialDataTypes
    -currAmt
    -dteFinData (a date field is probably in order)
    Also, could you explain to me again the benefit of having a data types table? We have a very limited number of data types and they won't change. What are the disadvantages of having those data types be fields in the financial data table (planned_cst_amt, actual_cst_amt) etc.? Having a data types table requires me to duplicate the timestamp field doesn't it? Instead of having a single record for each project number on a given date, I'd have 6 records (one for each data type) and 6 timestamps instead of 1.

    Thanks again!

  3. #18
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Can I not just have another field in tblCustomerDivisionProgramProjects for activity id that's a foreign key to the activities table? Why do I require the table above? The activity is defined at the same level the project is.
    In your earlier post you said this:
    3. Also, at the project level I need to assign additional information such activities, applicants, etc. This value would be something I'd find in an Applicants/Activities table that has a finite number of entries. Those tables would be used for looking up values only.
    This implied that there are multiple activities. If you have multiple activities related to a project that describes a one(project)-to-many(activities) relationship which requires the separate table. If you ONLY HAVE ONE AND ONLY ONE activity then you can have the 1 field in the project table.

    Also, could you explain to me again the benefit of having a data types table? We have a very limited number of data types and they won't change.
    You reduce spelling errors since the type is only located in 1 place. If you leave it to your users to input the type each time you will have spelling errors. If you try to search for specific text in that field you will only get those records that match and you might exclude those records where the type was misspelled.

  4. #19
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21
    Quote Originally Posted by jzwp11 View Post
    In your earlier post you said this:


    This implied that there are multiple activities. If you have multiple activities related to a project that describes a one(project)-to-many(activities) relationship which requires the separate table. If you ONLY HAVE ONE AND ONLY ONE activity then you can have the 1 field in the project table.
    Ahh, i see. I misspoke. For each project this is only one activity.


    You reduce spelling errors since the type is only located in 1 place. If you leave it to your users to input the type each time you will have spelling errors. If you try to search for specific text in that field you will only get those records that match and you might exclude those records where the type was misspelled.
    This makes sense to me as well. The only thing is that a user will never have to input the data types. However, I can see that they may need to add additional data types in the future so your format looks like a good idea.

  5. #20
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21
    Quote Originally Posted by jzwp11 View Post
    tblCustomerDivisionPrograms
    -pkCustDivProgramID primary key, autonumber
    -fkCustDivID foreign key to tblCustomerDivisions
    -txtProgramName
    -txtProgramElement (your yyyyy)

    A program can have many projects

    tblCustomerDivisionProgramProjects
    -pkCustDivProProjID primary key, autonumber
    -fkCustDivProgramID foreign key to tblCustomerDivisionPrograms
    -txtProjectName
    -txtProjectElement (your zzzzz)


    The above is just a rough structure and I do not know to which entity the financial data is associated, so you will have to provide more info on that
    Note that I have simplified the structure to make it easier for this forum. Say, for example, that a project record could contain many programs and a program could contain many projects (a many-many relationship). Would I have to create a third linking table (I read about this). How would that look?

  6. #21
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    If a program has many projects and a project can be related to many programs, then you would indeed have a many-to-many relationship. In general, the table structure would look like this:

    tblProgram
    -pkProgramID primary key, autonumber
    -txtProgramName

    tblProject
    -pkProjectID primary key, autonumber
    -txtProjectName


    tblProgramsProjects
    -pkProgProjID primary key, autonumber
    -fkProgramID foreign key to tblProgram
    -fkProjectID foreign key to tblProject

  7. #22
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21
    Quote Originally Posted by jzwp11 View Post
    If a program has many projects and a project can be related to many programs, then you would indeed have a many-to-many relationship. In general, the table structure would look like this:

    tblProgram
    -pkProgramID primary key, autonumber
    -txtProgramName

    tblProject
    -pkProjectID primary key, autonumber
    -txtProjectName


    tblProgramsProjects
    -pkProgProjID primary key, autonumber
    -fkProgramID foreign key to tblProgram
    -fkProjectID foreign key to tblProject
    Thanks again, makes perfect sense.

    The only other thing I'm going to do is add values tables. The tables we described above, programs for example, only effectively represent the programs assigned to a wbs element. I do need another table with a list of programs.

    So I might change the name of the original programs table (containing the wbs element) we described and have another programs table that looks like...

    tblPrograms
    -ProgramID, pk, autonumber
    -ProgramName
    -DivisionID, fk to a separate divisions table with a list of all the divisions below

    tblDivisions
    -DivisionID, pk, autonumber
    -DivisionName

    I'd be doing that for customers and other subjects just as I did with the activities table.

  8. #23
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    ...only effectively represent the programs assigned to a wbs element. I do need another table with a list of programs.
    Are you saying that you intend to create 2 program tables? If so, that is not the right approach. Like data should be in 1 table. You can use a field within that table that distinguishes programs related to wbs elements from ones that are not.

  9. #24
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21
    Quote Originally Posted by jzwp11 View Post
    Are you saying that you intend to create 2 program tables? If so, that is not the right approach. Like data should be in 1 table. You can use a field within that table that distinguishes programs related to wbs elements from ones that are not.
    Note really. I think what I was saying is that the table that we originally referred to as a programs table is actually a "3rd level WBS element" table. That level has a program associated with it. Does that make sense?
    That level would look something like

    tblWBS_3RD_LVL
    -WBS3rdLevelID, pk, autonumber
    -txt3rdLevelElementNo
    -WBS2ndLevelID, fk to 2nd lvl table
    -txtProgramID, fk to programs table containing a list of all program names, program codes, etc.

    is this not the right way to go about this?

  10. #25
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Perhaps I did not understand what you were saying. It looks like the table you showed is the junction table. If so, then it is OK. The foreign key should be a number field (not text "txt"). Relational database are more efficient with numbers rather than text fields.

    tblWBS_3RD_LVL
    -WBS3rdLevelID, pk, autonumber
    -txt3rdLevelElementNo
    -WBS2ndLevelID, fk to 2nd lvl table
    -txtProgramID, fk to programs table containing a list of all program names, program codes, etc.

  11. #26
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21
    Quote Originally Posted by jzwp11 View Post
    Perhaps I did not understand what you were saying. It looks like the table you showed is the junction table. If so, then it is OK. The foreign key should be a number field (not text "txt"). Relational database are more efficient with numbers rather than text fields.

    tblWBS_3RD_LVL
    -WBS3rdLevelID, pk, autonumber
    -txt3rdLevelElementNo
    -WBS2ndLevelID, fk to 2nd lvl table
    -txtProgramID, fk to programs table containing a list of all program names, program codes, etc.
    including "txt" was a mistake. it would be the foreign key to a table with a primary key set to autonumber.

    The main idea is that whereas before I called the first level table a CustomersTable, I'm going to call it a 1st level table where you enter the first level element number and select the customerid associated with it. There would be a customers table containing a list of all customers. Calling it a customer's table doesn't seem accurate to me. Right now we only associate that level with Customer's, but conceivably we could associate that number with more things (like we do the project-level, where we have activities and other things).

    This would make it so that the user did not have to enter the customer's name manually, leading to spelling errors as you mentioned before. It would also allow me to change the customer's name and have that reflected in all records accurately.

    Am I understanding this correctly?

    It feels like I have a better grasp of this now and I can go ahead and diagram this whole thing out.

  12. #27
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Sounds OK to me from what I know of your application.

  13. #28
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21
    I've attached a jpeg of the table structure I came up with. Producing the diagram made me realize I have a couple more questions.

    My questions relate to the tblFinancialData table (at the bottom). If you recall, this is the table that will be populated by importing a dataset from an excel spreadsheet. Each import will consist of a few thousand rows.

    Here are my questions.

    1. I originally figured that I would have a wbs_element_id field as the primary key for the tblWBS_Overrides tbl, set to autonumber. I was not sure however, how I would add a field to my excel data to be the foreign key for that autonumber field. Many of the wbs elements won't require an override at all. As such, I used the wbs_element_no as the primary key instead of autonumber because my dataset already containst he full wbs number. Is there any problem with this or is there a way to use an autonumber field in this case?

    2. I'm not sure how I relate tblFinancialData to tblWBS_6TH_LVL. tblWBS_6TH_LVL only has the 6th_lvl_no, a section of the full wbs_element_no. tblFinancialData contains the full wbs_element_no. Some of these element numbers will be incorrect though, which is why I have the overrides table.

    How do I establish a relationship between tblWBS_6TH_LVL and tblFinancialData at all in this case? Or do I just do that through a query?

    3. As I mentioned before, the wbs structure is slightly different and more complexed than the example I provided in this thread. There are 6 levels, each level containing additional information.

    Do you see any issues with the structure I've presented besides my questions above?

    Thank you.

  14. #29
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    1. I originally figured that I would have a wbs_element_id field as the primary key for the tblWBS_Overrides tbl, set to autonumber. I was not sure however, how I would add a field to my excel data to be the foreign key for that autonumber field. Many of the wbs elements won't require an override at all. As such, I used the wbs_element_no as the primary key instead of autonumber because my dataset already containst he full wbs number. Is there any problem with this or is there a way to use an autonumber field in this case?
    There is a way to use the autonumber field but there must be another field in that table that can be matched exactly to a field in the incoming data. You would make a join via those fields in your append query.

    What is an override?

    How do I establish a relationship between tblWBS_6TH_LVL and tblFinancialData at all in this case?
    To what entity does the financial data relate? a program, a project, a customer? That should tell you how to make the join--it is dependent on your data

  15. #30
    mribnik is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    21
    Quote Originally Posted by jzwp11 View Post
    There is a way to use the autonumber field but there must be another field in that table that can be matched exactly to a field in the incoming data. You would make a join via those fields in your append query.

    What is an override?



    To what entity does the financial data relate? a program, a project, a customer? That should tell you how to make the join--it is dependent on your data
    The financial data itself contains the full WBS element number, wwww.xxxx.yyyy.zzzz in our original example as well as financial data (types and amounts as we discussed earlier). My structure in the picture is more like wwww.xxxx.yyyy.zzzz.aaaa.bbbb. Sometimes, however, this data is entered incorrectly (into the customer's other database) and when I pull the financial data (my excel import), the incorrect wbs element numbers will show up. I need to have an override table to link the incorrect wbs element numbers to an alternate corrected number.

    The financial data itself is associated at the lowest possible level (bbbb or project level in our previous example).

    One of the main goals of this database is to be able to create a view or query where I can see each full WBS element number (wwww.xxxx.yyyy.zzzz.aaaa.bbbb), all the associated data we added to each individual level and the financial data.

    Do the rest of the table relationships look good to you?

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

Similar Threads

  1. Need to hire out relational database design
    By janakybrent in forum Access
    Replies: 5
    Last Post: 02-19-2016, 05:11 AM
  2. Learning Access - Design Questions
    By learning_access in forum Database Design
    Replies: 2
    Last Post: 02-15-2011, 09:13 AM
  3. Relational database for mice breeding
    By shiphtfour in forum Database Design
    Replies: 19
    Last Post: 12-16-2010, 10:01 PM
  4. Relational Database/query help
    By Champin4 in forum Queries
    Replies: 7
    Last Post: 07-14-2010, 08:49 PM
  5. Best Design for Multiple Y/N Questions on each record
    By DanielHochman in forum Database Design
    Replies: 0
    Last Post: 07-20-2009, 02:51 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