HELP! DB Design to later query multiple keywords

[Rhoswen]

I am an Access novice, using v 2007.

I need to design a database to store our company's project information in
for later reference for marketing, etc. Each project needs to have multiple
associated keywords from several different categories. The goal is to be able
to run a query specifying certain keywords, and a list of associated projects
will return.

Some of the categories are Sector, Project Type, Materials and Services.

Under Sector would be commerical, industrial, residential, etc.

Materials would include steel, wood, masonry...and so on.

I need a way to assign mulitiple selections from each category to all the
projects, so that later when I need to find an industrial project, that
included steel and masonry work and on which we provided services such as
project management and structural design, I can query that information easily
and it will return that projects A, F and G meet that criteria.

Any suggestions on how to lay out the tables and relationships to make this
work??

Thanks in advance for any help!

 

[Steve]

TblProject
ProjectID
ProjectName
ProjectStartDate
ProjectCompletionDate
<etc>

TblProjectCategory
ProjectCategoryID
ProjectCategory

TblProjectCategoryItem
ProjectCategoryItemID
ProjectCategoryID
Item

TblProjectItem
ProjectItemID
ProjectID
ProjectCategoryItemID

1. Record categories in TblCategory
2. Record category items in TblProjectCategoryItem; such as commerical,
industrial, residential, etc. in Sector
3. Record items in category in TblProjectCategoryItem; such as project
management and structural design in services
4. Record for a project each category and each item in each category in
TblProjectItem; such as project management and structural design of services
for projects A, F and G.

You can then use a properly structured query to do what you want.

Steve

 

[Rhoswen]

Thanks, Steve! You're my new superhero! Works great. I've just spent so many
years in Excel flat files, I'm having trouble wrapping my brain around
relational db design.

Appreciate the time you took to help.

 

[Steve]

You're welcome!

I provide help with Access and Excel applications for a modest fee. If you
need help beyond what you can get in a newsgroup or if you need quick help,
contact me at (e-mail address removed).

Steve

 

[John... Visio MVP]

You're welcome!

I provide help with Access and Excel applications for a modest fee. If you
need help beyond what you can get in a newsgroup or if you need quick
help, contact me at (e-mail address removed).

Steve

These newsgroups are provided by Microsoft for FREE peer to peer support.
Stevie is a known troll who harrasses posters for work. He provides
questionable results at immodest prices.

There was nothing special about his original answer.

John... Visio MVP

 

[Keith Wilby]

contact me

But before you do that, read this:

http://home.tiscali.nl/arracom/whoissteve.html