Problem with table relationships

D

Dan

Our agency deploys a large object with a silent alarm at a public location.
If the object is moved, the agency is alerted and radios a unit to the
location to investigate and record the type of action taken, which would be
on a lookup table of 5 stop scenarios. For instance, if suspect is there an
arrest is made or there could be a pursuit, etc. My tables are set up as
follows:

tblAgency tblObject tblDeploy tblStop
tblSuspect
Agencyid Objid Deployid Stopid
Suspectid
AgencyName ObjectDesc. Depdate StopDesc(5) LName
Contact DepLocation
FName
Phone
Offense

I have tried a number of table relationships but without success. (Foreign
keys trys left off). Perhaps someone could give me some direction.
Thanks,
Dan
 
K

Ken Snell \(MVP\)

Post the table structures vertically:

Table1
Field1
Field2

Table2
Field1
Field2

etc.

That will let us see more easily what the structures are.

Second, expand on your description of what you want to be able to do with
the tables' data. Are you using a form for data entry? How are the tables
related "on paper" -- meaning, how do you see the data relationships in your
mind's design eye? What problems are you having with the table relationships
that you've tried?
 
D

Dan

tblagency
agencyid
agencyname
agencycontact
agencyphone
objectid

tblobject
objectid
object

tbldeploy
deployid
deploydate
deploylocation
agentid
stopid

tblstop
stopid
stopdescription

tblsuspect
suspectid
lastname
firstname
offense
casenumber
stopid

Above info comes to me as a paper form and the data will be entered into one
Access form. Since my first post I worked on it some more and I created a
form that seems to work with the table setups above. I'm still working on it.
I had started over again and seem to be making some progress. Do my
relationships make sense? Some of the info will be entered on the form based
on combo boxes based on tables; tblobject will have four objects, tblstop
will have five stops (stop means suspect at object, suspect pursued, suspect
out of jurisdiction, no suspect at site, false alert)

Please let me know if you need any more info.

Dan
 
K

Ken Snell \(MVP\)

If I'm understanding the data structure correctly, I would not put objectid
field in tblagency table. Instead, what would be better is to create another
table -- call it tblagencyobject -- for matching an agency with an object:

tblagencyobject
agencyobjectid (primary key)
agencyid (foreign key)
objectid (foreign key)



Then I'd have a table to record deployments of a specific object:

tbldeployments
agencyobjectid (foreign key)
deployid (primay key)
deploydate
deploylocation
removaldate



Then I'd have a table to store the types of events that can occur (is this
your stops info?):

tblevents
eventid
eventname
eventdescription



Then I'd have a table to record "events" related to that deployment:

tbldeployevents
deployid
eventdatetime
eventid
suspectid
results



and so on. Does this help?
--

Ken Snell
<MS ACCESS MVP>
 
D

Dan

Yes, I'll get on it tomorrow. some of what I tried earlier didn't work.
Thanks for your time and I'll keep you posted.
 
D

Dan

After considerable effort to getting your suggestions to work, it was decided
to redesign the project. We're working on it now. From what I've seen so far,
I'll be able to incorporate your suggestions in the redesign. Thanks again
for your input.
 
D

Dan

Ken (If you're watching)... We looked over the info we want and here we go
again:

A law enforcement agency deploys a vehicle out on the street for about 24
hours to be stolen. It is equipped with GPS equipment. It may or may not be
stolen. If stolen, an alarm goes out and we respond and stop the vehicle and
arrest suspect. If the alarm not set off we bring back the vehicle and deploy
to another location a few days later.

tblagency
agencyid
agencyname
date_to_agency

tbldeploy
deployid
deploydate
deploylocation
alarmactivated
agencyid

tblsuspect
suspectid
suspectname
charge
deployid

I first created separate forms for agency, deploy and suspect. Then made
suspect form a sub-form in frmdeploy. Then made frmdeploy a subform in
frmagency.

When I run dummy data in the frmagency form it runs fine if I have entries
in the frmsuspect.
If there are no suspects (no alert during that deploy) my query does not
pick up any information I entered. The only way it will work is if I go into
the subform for suspects and create a dummy record.

Can you help? Do I need a m-m table and how would I set up?

Thanks,

Dan
 
K

Ken Snell \(MVP\)

You'll need to post the SQL statements of the the suspect subform's
RecordSource; I'm guessing that the query is not updatable because of how
it's structured. Also, post the LinkChildFields and LinkMasterFields
properties of that subform's parent control (the subform control on the
deploy subform).

I am assuming from what you've posted that the deployid field in tblsuspect
table is a foreign key from the tbldeploy table, correct?

--

Ken Snell
<MS ACCESS MVP>
 
Top