Help with setting up a table(s)

[wolfgang]

i have some information i want to store in a talbe, one
table if possible. i am new to access. i have several
communitues. each of these communities has a dynamic mix
of plans. each plan may have several different
elevations to it. so for example. i would have plan
name" Alexandria" with elevations A-D. however in
community #1 they build elevations A,C and D. and in
community #2 the build all of the elavations. i can't
figure out how to set up a table(s) to store plan
information. if i create a query for community #1 it
shows the plan listed 4 times in the list because there
are 4 possible elevations. i want to run a query on any
community and get a list of the plans being built there.
any advice would be appreciated TIA.

 

[rpw]

One table for communities, one table for plans. Each community can have several plans, and each plan can be assigned to several communities. This is a M:M relationship for which you need a juction table something like this:

tblCommunityPlans
CommPlanID 'autonumber PK
CommunityID 'FK linked to community table
PlansID 'FK linked to Plans table

Index the CommunityID and PlansID fields so that there are no duplicates.

hth

 

[Tim Ferguson]

i have several
communitues. each of these communities has a dynamic mix
of plans. each plan may have several different
elevations to it. so for example. i would have plan
name" Alexandria" with elevations A-D. however in
community #1 they build elevations A,C and D. and in
community #2 the build all of the elavations

How about this: (*=Primary Key)

Communities(*NameOfCommunity, etc..)

Plans(*PlanName, Community(FK), etc..)

Elevations(*EleCode(A,B,C or D), etc..)

Building(*PlanName(FK), *EleCode(FK), StartDate, LooksGood, etc..)

i want to run a query on any
community and get a list of the plans being built there.

PARAMETERS [Enter name of community] TEXT;
SELECT ALL PlanName
FROM Plans
WHERE NameOfCommunity = [Enter name of community]

If you want the elevations, you just join the Building and Elevations
tables on to the Plans table.

Hope that helps


Tim F