redundant information

[kkelley]

I need to build a database that has just a few pieces of information, however
how to design the tables without redundancy is confusing me.

I have water techs that want to be able to type in an address and have it
pull up the different valve locations for turning the water off for a
subdivision. I have three to four valves for each subdivision. Each
subdivision has multiple addresses. I am overthinking this severly and
confusing myself but I have an address table with the list of addresses and a
valve table with 4 fields valve1 valve2 etc. I thought that there was a set
for each subdivision. However after going through the locations one valve
from subdivision a can be a valve for subdivision c too.

What is the best way to layout these tables so that when the tech types in
the address all the corresponding valves show up.

Thanks!

 

[John Spencer]

Sounds like you need the following Tables:
SubDivision
Addresses
Valves
ValvesForSubdivision

--SubDivision table is basically a lookup table so you are consistently
entering data in addresses and ValvesForSubDivision tables
--Addresses is a list of addresses and other information with the
subdivisionID (Assumption is that an address only belongs to ONE
subdivision)
--Valves is a listing of all valves (and any additional information about
the valve)
--ValvesForSubDivision has two fields SubDivisionID and ValveID (one record
for each combination of valve and subdivision)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..