Help with setting up a table(s)

  • Thread starter Thread starter wolfgang
  • Start date Start date
W

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.
 
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
 
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
Click to expand...

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.
Click to expand...

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
 
Back
Top