Need help setting up a complex project database

Discussion in 'Access Table Design' started by eco-designer, Sep 14, 2007.

  1. eco-designer

    eco-designer Guest

    I am setting up a database to track and report the finish/specification
    schedule for projects that will allow the info to be sorted in a couple of
    different ways.

    The basics are: I have builders, sub-contractors, and suppliers who I use
    repeatedly on many idfferent projects in various combinations. I have these
    already stored in my Outlook Contacts. I think it would be esiest to import
    these or link directly to these contacts from there, without creating a new
    table.

    Second, I have a client, who has one or more projects. Each project has a
    specific set of rooms, which I am thinking I will put in its own lookup
    table. Then each project also has a specific set of information, which I am
    thinking should be in seperate tables according to type, i.e. cabinets,
    countertops, plumbing, appliances, etc. (Each of those categories has its
    own set of info - room, material, color, finish, etc).

    Then there is the "finish schedule" which has sub-sets of floors, walls,
    ceilings, etc - each of those has specific info - room, material, color,
    finish, etc., again. I want the entire finish schedule to be grouped
    together, but maybe only in the report, unless there is a good way/reason for
    linking it together.

    So, to sum up-
    Many builders - who are linked to one or many projects
    Ditto sub-contractors
    Ditto Suppliers

    Many Clients - one or many projects
    Projects - one or many rooms
    Detailed categories, specific to only one project

    Eventually I want to be able to print reports that show each project,
    detailing each product by category and then also detailing each room and
    showing all info pertaining to that room from every category.

    Am I on the right track?
    How do I set up these tables/relationships?

    Thanks!
     
    eco-designer, Sep 14, 2007
    #1
    1. Advertisements

  2. eco-designer

    Steve Guest

    If you have an immediate need to get your project database done quickly, I
    can work with you to design the
    table structure of your project database. I have done this for numerous
    customers. My fee is very reasonable. I provide a map of the tables that
    shows all the tables in the database, all the fields in each table, all the
    relationships between the tables and the type of relationship for each
    relationship. The tables are arranged on the map generally as the flow of
    information in the database. I create a map of the tables for every database
    I do. The map visually shows what forms and subforms are needed for data
    entry, shows what special forms and subforms can be created for dispaying
    data in the database and shows
    what reports and subreports can be created from the data in the database.

    PC Datasheet
    Providing Customers A Resource For Help With Access, Excel And Word
    Applications
     
    Steve, Sep 15, 2007
    #2
    1. Advertisements

  3. eco-designer

    Gina Whipp Guest

    Eco-Designer,

    I just finished working on a conract that does FF&E Specification sheets for
    Interior Design which includes purchasing, client/contact management,
    project managment, vendor management, as well as, reporting, etc... If
    you think this is close to what you need, I can provide you with table
    layouts for FREE!
     
    Gina Whipp, Sep 15, 2007
    #3
  4. Nice try Steve. but these newsgroups are not your personal ground for
    soliciting. They are for Free Peer to Peer support.

    John... Visio MVP
     
    John Marshall, MVP, Sep 15, 2007
    #4
  5. What does FF & E mean?
    Wow, much better price than Steve. And a much more trustworthy
    source.

    Tony

    --
    Tony Toews, Microsoft Access MVP
    Please respond only in the newsgroups so that others can
    read the entire thread of messages.
    Microsoft Access Links, Hints, Tips & Accounting Systems at
    http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
     
    Tony Toews [MVP], Sep 16, 2007
    #5
  6. eco-designer

    Gina Whipp Guest

    Furniture, Fixture & Equipment...

    and Thank you!!
     
    Gina Whipp, Sep 16, 2007
    #6
  7. eco-designer

    Keith Wilby Guest

    Yeh, right. Except that you don't know the first thing about Excel as
    demonstrated by your questions in the Excel NG.
     
    Keith Wilby, Sep 17, 2007
    #7
  8. eco-designer

    Keith Wilby Guest

    I love jumping in with free offers when snake-oil boy touts for business. I
    recently sent two people a copy of a survey db of mine for free and they're
    both now satisfied "customers".

    Keith.
     
    Keith Wilby, Sep 17, 2007
    #8
  9. eco-designer

    eco-designer Guest

    I would love to see what you have - I'm mainly looking for the FF&E
    specifications, but you never know where something might pop up.
    Please send to .
    Thanks-
     
    eco-designer, Sep 17, 2007
    #9
  10. eco-designer

    Gina Whipp Guest

    Tanya,

    I sent them to you in a word document. The title of the eMail is FFE table
    set-up.
     
    Gina Whipp, Sep 17, 2007
    #10
  11. eco-designer

    Gina Whipp Guest

    And just a side note... when placing your eMail in these newsgroups...
    tanyaashively AT earthlink DOT net prevents it from being collected by spam
    tools.
     
    Gina Whipp, Sep 17, 2007
    #11
  12. eco-designer

    eco-designer Guest

    Thanks for the anti-spam tip - I HATE that stuff!!
    I got your file - thx.
    I guess one of mymain questions is really how to link the tables together so
    that the info is connected correctly (see my inital post regarding set-up).
    I have the tables with the basic info I need, but don't know the best way to
    link these and create the relationships.
    For instance, can I use lookup columns to pull contact info from Outlook for
    builders and sub-contractors, etc.
    Then, how do I link the projects to the correct client?
    And the correct project detail tables to each project?
    I have set up tables for cabinets, countertops, tub & showers, appliances,
    plumbing, windows, fireplaces, etc and they each have a long list of info
    columns.
    I would like to have a table for finish schedule, that links several
    sub-categories of floors, walls, ceilings, etc.
    Does that make sense?
    Any ideas?
    Thanks,
     
    eco-designer, Sep 17, 2007
    #12
  13. eco-designer

    Gina Whipp Guest

    Tanya,
    1. Each Client can have multiple Projects

    tblProjects FK pClientID <> tblClientProfile PK cpClientID

    When entering a new Project you have to select the client that goes to that
    project
    2. Each Project can have umpteen FF&E Sheets

    tblProjects PK pID <> tblFFESpecification FK ffesProjectID

    I have set up my database to link to the Project when entering a new FF&E.
    This is done when entering a new FF&E Sheet you must select a Project.
    These are actually part of the FF&E Sheet, you have to select
    ffesItemDescription which lets you know what the item is, setting up
    seperate tables makes reports that much more difficult. Also on the FF&E
    Sheet menu you can select, room, floor, style, finish, location and more.
    You don't need seperate tables to be able to print things like Finish
    Schedules, You just need the a way to get a query and produce the report
    with the information you want. The sub-catergories you refer to are part of
    my Specification Index, which is done by a range of numbers, so any
    specification sheet between 100-199 means floors, walls, etc. Hope I am
    explaining this so it makes sense.
     
    Gina Whipp, Sep 17, 2007
    #13
  14. eco-designer

    eco-designer Guest

    Ok - I understand the project / client link, and also the project FFE spec
    link.
    What I understand you're saying is that all of your specs, regardless of
    type, are on one table. In my situation, each type has it's own set of info
    types, sometimes they are similar, sometimes they are not. So do you just
    end up with a lot of fields that may be blank for a specific set of specs?
    i.e., electrical plate covers are only brand/color and location, where
    countertops include material, color, backsplash, supplier, grout color, etc.
    and plumbing includes qty, location, manufacturer, item, color, model, etc.

    Then can I set up queries to get all of the countertops, all of the
    cabinets, all of the appliances, etc plus a query to get all of the info for
    each room?
     
    eco-designer, Sep 17, 2007
    #14
  15. eco-designer

    Gina Whipp Guest

    So do you just
    Yes, but not as many as you think.
    I also hold Vendor/Supplier, Estimated Ship Date, Quantity, etc...
    Color and Style are actually stored in another table linked to
    tblFFESpecification
    In my set-up the answer is yes and with minimal effort.
     
    Gina Whipp, Sep 17, 2007
    #15
  16. eco-designer

    eco-designer Guest

    I appreciate your patient help- I know I dove into a very complex project for
    my first foray into Access!
    I guess part of my difficulty is that each of my projects are very unique -
    there really isn't repeatable info once you get past the suppliers and
    builders. The FFE Specifications are individual only to that particular
    project. Should I make my database a template that I use to create a new
    file for each project so that they are not intermingled? Won't the database
    become huge if all projects are kept in one file?
    (I also don't care about tracking orders or any of that - I basically give
    this info to the builder and they take over from there, which simplifies in
    that I don't need po's or invoices or any sort of shipping info.)
    When I'm reading your tables I don't see the fields I thought I
    wanted/needed for product info. Do you just enter all the color, material,
    style, etc as one long description?

    Frankly, I am very confused, and would be willing to have someone else
    create this for me if the price was right. I only started this on my own
    because I couldn't find anyone else to do it.
     
    eco-designer, Sep 17, 2007
    #16
  17. eco-designer

    Gina Whipp Guest

    Tanya,

    You definitely went to the most challenging for a first time project!

    The way I designed this database was that the FF&E Specification Sheets have
    a specific layout, the client and I thought it looked more professional that
    way. What's on them is what makes the unique to the project. Projects are
    in one table, FF&E Sheets another table with plenty of tables linked to it.
    My client received this database and has since entered about 20 projects,
    each project has about 500 FF&E sheets and it's not 10mg yet. As long as
    the database tables are set-up properly then 'huge' won't come for many
    years.

    Color, style, etc.. would be in tblFFESpecificationStyle and
    tblFFESpecificationSize, as well as, tblFFESpecificationImages (if you want
    pictures), tblFFESpecificationLocation. These are one sheet to many Styles,
    Sizes, Pictures, etc by the way of subforms.

    Awkward position... I would hate to be accused of trolling for business.
    If you would like to have a conversation about me doing a scaled down
    version of what I have done, then let me know. You might want references
    and the like first!!!
     
    Gina Whipp, Sep 18, 2007
    #17
  18. eco-designer

    eco-designer Guest

    ok- next question, and this may be exactly what you have been trying to tell
    me since the first-
    do I need a table to link all the categories, i.e. tblProjectSpecs
    that has fields AppliancesID, PlumbingID, CabinetsID, TubShowerID, etc?
    Each of those refer to one item entered in their respective tables. (I
    think this may be similar to your tblFFESpecification) or maybe it just has a
    field for ItemDescriptionID, that refers to each of those other tables, and
    thus creates a more uniform appearance?

    If we were to talk about doing a scaled down version of your existing
    database I would certainly not be thinking you had been trolling - I am aware
    of the rules for this group, but I think I bit off more than I can handle,
    and you seem to already know what I'm talking about.
    Please e-mail me directly to discuss.
    Thanks,
     
    eco-designer, Sep 18, 2007
    #18
  19. eco-designer

    Gina Whipp Guest

    Tanya,

    No you do not need an additional table, that would be handled by let's say
    the Specification Index which is different then the tblFFESpecification.

    I will send you an eMail directly about the database I already have.
     
    Gina Whipp, Sep 18, 2007
    #19
  20. eco-designer

    WadeLovell Guest

    Gina, I am looking to solve a similar problem. If you see this then please
    contact success AT SYMBOL ceo at home DOT com.
     
    WadeLovell, Oct 31, 2007
    #20
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.