Tables & relationships

[Pat Hughes]

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 (), (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.

 

[Amy Blankenship]

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

There actually isn't any information about vehicles in this table, so its
name doesn't make sense. Additionally, ReportNumber would seem to point to
a Report table, but that doesn't seem to exist. Regardless, I would
structure it this way:

tblTowRequest
RequestID
TowLocation
TowTypeID
TowReasonID
Notes
You probably also need information for who requested the tow.

tblTowEvent
EventID
RequestID
dtmTimeOccurred (records both date and time)
EventTypeID

tblTowEventTypes
EventTypeID
EventTypeDesc
EventTypeOrder

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

You have a lot of repeated fields between Table 2 and 3. Try this:

tblTowCompany
CompanyID
CompanyName
Boundaries
Notes

tblContacts
ContactID
LastName
FirstName
NickName
BusPhone
CellPhone
FaxPhone
OtherPhone
Notes

Note I have left the CompanyID off. This will enable you to use this table
to define clients (people being towed) as well, if needed in the same table

tblCompanyContacts
CompanyID
ContactID
IsPrimaryContact

Table 4
tblTowTypes
fldTowTypeID (1)
fldTowType

Table 5
tblTowReasons
fldTowReasonID (1)
fldTowReason


My relationships are in (), (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).

You would use a main form for tblTowRequest, and you'd use a subform in that
pointing to tblTowEvents to record each event as it occurs in the life of
the request. You could also point this to a Left or Right join between
tblTowEvents and tblTowEventTypes to prepopulate the recordset with "empty"
events before they happen. To fill in the values for the "ID" fields, use a
Combobox that is bound to the ID, but shows the Description when the user
clicks on it.

HTH;

Amy