Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    3

    Unanswered: Access Relational Database Question

    Hello All,

    I've been asked to create a database for one of our customers. The database will house project information (WBS element numbers, program name, division, cost, etc.). I'm not sure the best way to go about this with the information I have to work with.

    For example:

    One data pull I do (i'll have to pull this on a weekly basis and add it to the access database) will contain the full WBS element number (6 levels) and a bunch of financial data columns as well. To that dataset I'd like to pull from tables in order to add the program name, division and other information specific to that WBS number.

    My research on database design says that it'd be optimal to have a separate table each for programs, names, divisions etc (as opposed to one table with a WBS element number and the division, program, etc.). The WBS structure is such that the division is specified in the 2nd level of the WBS, program name in the 3rd etc.

    The problem is that the tables are related by the WBS number, but the programs table would only go up to 2 levels, whereas the data table would contain the full WBS number. I'm assuming I can't define a relationship between the two tables such that the first 14 characters of the WBS element in the programs table match the first 14 characters of the full WBS element in the data table, correct?

    If that's true, is it also correct that in this case I must create one table with all the full WBS elements and the corresponding program names, divisions etc.

    Thanks for all your help! I'm sure you can tell I'm relatively new to both access and relational databases in general. Up to this point, all my database needs have been handled in excel and I haven't had the need to create a database with more than 15,000 records.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mribnik View Post
    The problem is that the tables are related by the WBS number, but the programs table would only go up to 2 levels, whereas the data table would contain the full WBS number. I'm assuming I can't define a relationship between the two tables such that the first 14 characters of the WBS element in the programs table match the first 14 characters of the full WBS element in the data table, correct?
    not using substrings, no

    you said the WBS has 6 levels -- each of those levels should have a name, and each should be a separate column

    then you can easily define relationships between tables that share more than one level of the WBS
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    3
    Thanks for your reply.

    So you're saying that in my weekly data pull (the one with the full WBS element number) I should take that number and separate it out into it's levels in adjacent columns? Or is a better approach to make a WBS element table that lists all the elements broken out into their levels?

    Thank you,

    Mike

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't mean to tease you, but can you think of pros and cons for both of those suggestions?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2011
    Posts
    3
    No, that's fine. I appreciate the idea.

    I'm not sure from a database upkeep standpoint.

    I wish I could query directly from my work's database instead of having to do my datapull, but it's not an option. I have to download the report from another program and run it through an excel VBA procedure to get it formatted correctly to put in the Access database. To that extent, writing code to add a few columns for the different levels of the WBS element isn't a big deal because I only need to update the code once.

    I'm not quite sure I understand the benefits of adding a wbs elements table with all the levels in it. It sounds like a clean option though.

    I'd guess one benefit of having the wbs elements table is that I could relate the other tables without needing the data pull table.

    What else am I missing?

    Thank you so much for your help.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i never suggested a wbs elements table, just so you know
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •