Need help setting up a complex project database


E

eco-designer

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!
 
Ad

Advertisements

S

Steve

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
(e-mail address removed)
 
G

Gina Whipp

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!
 
J

John Marshall, MVP

Nice try Steve. but these newsgroups are not your personal ground for
soliciting. They are for Free Peer to Peer support.

John... Visio MVP
 
T

Tony Toews [MVP]

Gina Whipp said:
I just finished working on a conract that does FF&E Specification sheets

What does FF & E mean?
I can provide you with table layouts for FREE!

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/
 
Ad

Advertisements

K

Keith Wilby

Steve said:
PC Datasheet
A Resource For Help With Excel

Yeh, right. Except that you don't know the first thing about Excel as
demonstrated by your questions in the Excel NG.
 
K

Keith Wilby

Tony Toews said:
Wow, much better price than Steve. And a much more trustworthy
source.

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.
 
E

eco-designer

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 (e-mail address removed).
Thanks-
 
G

Gina Whipp

Tanya,

I sent them to you in a word document. The title of the eMail is FFE table
set-up.
 
G

Gina Whipp

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.
 
Ad

Advertisements

E

eco-designer

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,
 
G

Gina Whipp

Tanya,
Then, how do I link the projects to the correct client?

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
And the correct project detail tables to each 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.
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.

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.
I would like to have a table for finish schedule, that links several
sub-categories of floors, walls, ceilings, etc.

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.
 
E

eco-designer

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?
 
G

Gina Whipp

So do you just
end up with a lot of fields that may be blank for a specific set of specs?

Yes, but not as many as you think.
i.e., electrical plate covers are only brand/color and location

I also hold Vendor/Supplier, Estimated Ship Date, Quantity, etc...
countertops include material, color, backsplash, supplier, grout color,
etc.
and plumbing includes qty, location, manufacturer, item, color, model,
etc.

Color and Style are actually stored in another table linked to
tblFFESpecification
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?

In my set-up the answer is yes and with minimal effort.
 
E

eco-designer

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.
 
Ad

Advertisements

G

Gina Whipp

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!!!
 
E

eco-designer

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,
 
G

Gina Whipp

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.
 
Ad

Advertisements

W

WadeLovell

Gina Whipp said:
Tanya,

I will send you an eMail directly about the database I already have.

Gina, I am looking to solve a similar problem. If you see this then please
contact success AT SYMBOL ceo at home DOT com.
 

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. After that, you can post your question and our members will help you out.

Ask a Question

Top