Tables & Relationships

[cricket7]

I think I figured out my tables and relationships.

Tables:

Table 1
tblTowedVehicles
fldTowID
fldReportDate
fldReportTime
fldlReportNumber
fldTowRequestDate
fldTowRequestTime
fldTowLocation
fldTowCompanyID(1)
fldTowArrivalDate
fldTowArrivalTime
fldTowTypeID (8)
fldTowReasonID (8)
fldNotes

Table 2
tblTowCompanies
fldTowCompanyID(8)
fldCompanyName
fldAddress
fldCity
fldZipCode
fldBusPhone
fldFaxNumber
fldContactID(8)
fldBoundaries
fldNotes

Table 3
tblTowCompanyContacts
fldContactID(1)
fldTowCompanyID
fldLastName
fldFirstName
fldNickname
fldBusPhone
fldCellPhone
fldFaxNumber
fldOtherPhone
fldNotes

Table 4
tblTowTypes
fldTowTypeID (1)
fldTowType

Table 5
tblTowReasons
fldTowReasonID (1)
fldTowReason


My relationships are in (1), (8) being many.

Could someone check these out to make sure they are ok. Now I am
stuck on how to make the entry form for the user. Do I use the fields
from the Table 1 (tblTowedVehicles). Or do I make a query with all
tables and take only the main fields from the tblTowedVehicles table
but where the field is called "tblTowTypeID", "tblTowReasonID",
"tblTowCompanyID", I bring down from those other tables the
tblTowType, tblTowReason, and tblCompanyName. Then run the query and
make a form based on that? I guess I am confused as to where to go
from here.

The more I look at my tables and relationships I am not sure those are
correct.

Thanks for any help you can give me.

 

[Jeff Boyce]

From your description, it seems like you've already determined the
"entities" and "relationships". But we aren't there, and (many of us) may
not understand the aspects of your business that you take for granted.

If you were describing what you are working with to an 89 year old
grandmother, what would you say? Remember, you wouldn't be able to mention
"tables" or "relationships" or (probably even) "computers".

Here's my (admittedly uninformed) (mis-)interpretation:

* you have vehicles
* you have towing companies
* you have "towings"
* you have towing reasons
* you have towing types (but I'm not real clear on how this is different
than 'reasons')

(and based on your data structure, you can have multiple contacts at any
given towing company)

If these are accurate statements, it suggests that you have 6 tables.

But wait, there's more!

You'd also need to explain how the various "entities" are related. For
instance:

** any vehicle can be towed many times
** any towing company can tow many vehicles
** only one towing company can tow a vehicle at a specific point in time
** towing companies can have multiple "contacts"
** "towings" can have one and only one reason
** "towings" can have one and only one type

Now, your turn! Remember, an 89 year old grandmother...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pat Hughes]

Thanks and sorry Jeff,

I work for a police dept. and there is already a program that has all the
other info for any tows that are first requested by an officer to dispatch.
But then the dispatch calls one of the towing companies to have a vehicle
towed. The Dep. Chief asked me to do a database or excel worksheet for info
that isn't in the currently used database. His request is to "make me a
spreadsheet/data base with the following fields. Date, Time, Report #, Type
of tow, Location of tow, Which tow company was used, Time (Tow company was)
called, and Time (Tow Company) arrived. This info is on a new data sheet
made by the D/C for dispatch to fill out for each tow. There is also a data
sheet that the officer must fill out for each tow. This is why there is a
disparity in the type and reason for the tow. I am trying to figure out how
to make a database to connect the 2 data sheets. The "Type" of tow on the
one sheet are Traffic, Boot (applying the boot to a vehicle so it can't be
moved), Evidence, City Maintenance, Criminal, Customer Request and Other.
The Reason or "Nature of Tow" on the other data sheet are Arrest, Accident,
Hazard, DUI, Recovered/Stolen, Abandoned, Seizure, Evidence, and Other.
When I asked the D/C for what he was looking to get out of the database he
said, 1) How many tows by date/time and by which towing company, 2) What was
the time frame by each company to arrive to tow the vehicle from the time
they were called, 3) What time frame do most of the tows occur, 4) If
possible, what areas do most tows occur.
I hope this explains more.
Thanks

 

[Jeff Boyce]

Thanks for the clarification.

Have you taken a look at the statements I intuited? How accurate are they?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

I just found your duplcate post/string in another newsgroup. Since the
folks responding here are volunteering their time, it is rarely necessary to
post to more than one 'group. Doing so means folks who respond may not
realize that your question has already been answered.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pat Hughes]

I first posted on the other newsgroup but I didn't understand much of what
they were saying. I felt lost so I thought I should start here in the
getting started newsgroup. I have had some classes in the past in Access but
since the majority of my job hasn't been in Access I forget a lot by the time
I need it. I also have always had a problem in figuring out the tables and
relationships. I have started many different databases in the past number of
years but haven't had anyone to talk to get past the tables and relationship
phase.

As to your other statements:

1) Yes, we have vehicles that are towed but that isn't in what the D/C wants
in his database. The info on the vehicles that are towed doesn't really have
any bearing on anything he needs.
2) We have 3 towing companies
3) We have numerous towings
4) As I explained above there are 2 data sheets that are filled out with one
being filled out by officers at the scene and the other being filled out by
dispatch which they (dispatch) in turn call the towing company to make the
request for a tow. I know this confuses things since the "Type of Tow" on
one sheet is different than the "Reason or Nature of Tow" on the other sheet.
I thought about possibly clarifying these types and reasons more succinctly
like
1) Traffic:
a) No License,
b) No Insurance,
c) Boot
1) Parking Ticket Payment
2) Other
d) Other
2) Evidence
3) City Maintenance
4) Criminal
5) Customer Request
6) Other

These would probably be updated at sometime in the future.

Now as I write all this to explain how detailed it sounds, I am thinking
that maybe I am making this into a harder situation than what is needed at
the moment for my Dep. Chief. My thinking has been to put the 2 data info
sheets together in one database. But hopefully my IT guy can do something in
the main database they are using in a program called AFFIX. I have asked him
for help because it makes much more sense to just add the few new fields in
AFFIX (like time dispatch requests tow from tow company & time tow company
arrives to tow vehicle) since much of the other info is already entered in
AFFIX. The IT guy could easily get the queries out of the very sophisticated
AFFIX database. But he tells me has has no time to do this. The D/C just
basically wants a little more info that is not being entered in AFFIX at the
moment and is only on the one data info sheet dispatch is filling out. All
the info the D/C wants basically is the time dispatch calls the towing
company, and the time the towing company arrives and towes the vehicle. Thus
I am trying just to get the info the D/C wants. So I guess I want to make
this as simple as possible even though it doesn't sound like it. This why I
went with the original fields the D/C requested which are
Time of Report
Report #,
Type of tow,
Location of tow,
Which tow company was used,
Time the tow company was called
Time the tow company arrived <<

So maybe I should remove the contacts table

Table 1
tblTowedVehicles
fldTowID
fldReportDate
fldReportTime
fldlReportNumber
fldTowRequestDate
fldTowRequestTime
fldTowLocation
fldTowCompanyID(1)
fldTowArrivalDate
fldTowArrivalTime
fldTowTypeID (8)
fldNotes


Table 2
tblTowCompanies
fldTowCompanyID(8)
fldCompanyName
fldNotes


Table 3
tblTowTypes
fldTowTypeID (1)
fldTowType

I think that I was afraid that the D/C might ask for more info later and I
was trying to include what that might be and making the tables too big. So
would these 3 tables give me the info he wants. Are the relationships right?
Right now I want to make this as simple as possible.

I appreciate your help very much. I'm sorry if I am frustrating you with my
lack of communication skills.

Thanks,
Pat

 

[Spurious Response]

Now as I write all this to explain how detailed it sounds, I am thinking
that maybe I am making this into a harder situation than what is needed at
the moment for my Dep. Chief. My thinking has been to put the 2 data info
sheets together in one database. But hopefully my IT guy can do something in
the main database they are using in a program called AFFIX. I have asked him
for help because it makes much more sense to just add the few new fields in
AFFIX (like time dispatch requests tow from tow company & time tow company
arrives to tow vehicle) since much of the other info is already entered in
AFFIX. The IT guy could easily get the queries out of the very sophisticated
AFFIX database. But he tells me has has no time to do this. The D/C just
basically wants a little more info that is not being entered in AFFIX at the
moment and is only on the one data info sheet dispatch is filling out. All
the info the D/C wants basically is the time dispatch calls the towing
company, and the time the towing company arrives and towes the vehicle. Thus
I am trying just to get the info the D/C wants. So I guess I want to make
this as simple as possible even though it doesn't sound like it. This why I
went with the original fields the D/C requested which are

The immediate solution would be to merely add the missing fields to the
AFFIX database, and worry about extracting the data afterwards.

...Or you could keep that database, with the added fields, as the main
data entry front end, and export segments of the data to a front end
report provider database you can develop, which would use the AFFIX
database as a back end at that point.

Then, at some point, you could design an Access database that does the
entire operation in one package, which appears would be a more in depth
development, so doing it on the side would help to insure that it gets
done more thoroughly.

 

[Pat Hughes]

Thanks but your suggestions is what I first thought of and explained to our
IT person. I thought it would be rather easy for him. But he said he
doesn't have time to do anything right now. And I know that means that it
probably will be a very long time before he will get to it if ever. But I
need to have this report for the Dep. Chief. I don't have any administrative
rights to be able to do anything in AFFIX except enter data, do searches or
print reports.

This afternoon I did finally do an excel worksheet with the info the Dep.
Chief needs and turned that in. Tomorrow I think I will try and import that
worksheet into access to write a simple database. Or if the D/C likes the
worksheet I did I might just leave it at that.