What is the best way to set-up my Database?

[dgodfrey]

I want to revisit a few things posted in another thread I started a while
back. Any help from anyone is appreciated.

I have a main form "Suspects" and a subform "sfrmBolo". The "Suspects" form
is bound to the Suspects table and the subform is bound to the Bolo table.

The Suspect table contains details about a person involved in an incident,
while the Bolo subform contains incident details. The Bolo subform is on a
tab on the main form. The top of the main form contains person details. I
also have tabs for vehicle info, a physical description, and contact history.

One of the pices of information on the Bolo subform is the name of the
Petitioner (details below) which is my main reason for posting.

I also would like to know the best way to do the "Contact History" tab. It
would need to be a listing of all records associated with that person that we
could click and get details of that event (Bolo?)

Allen Browne Wrote:

"There are multiple ways to interface SuspectID and PetitionerID without
having to show the AutoNumber values. One way is to use a combo box, where
the RowSource is something like this:
SELECT PetitionerID, Surname & ", " + Firstname AS PetitionerName
FROM tblPetitioner
ORDER BY Surname, FirstName, PetitionerID;
Then set the first column to zero-width, so it stores the hidden
PetitionerID value, but displays the name:
Column Count 2
Column Widths 0
Bound Column 1 "

I now understand about the combo boxes you mentioned for PetitionerID. My
first question is this: Do I need to make a separate table tblPetitioner? If
so, I assume it would need to have PetitionerID (PK Autonumber), FirstName,
LastName, PhoneNumber, Department ?

Assuming that table structure, Next question is: Do I need a separate form
to enter the Petitioner info? Would a sub-subform or subform be better?

Now, assuming it is set up this way, is it then correct to place a combo box
cboPetitioner bound to the PetitionerID PK field that does the query you gave
me earlier to combine the First and Last Name on the sfrmBolo subform? The
question is then, do I put the query "code" in the rowsource space on the
combo options, and how do I make this combo a drop-down list? Let me
clarify...if the petitioner does not exist in the list in the combo, I want
the add petitioner form to pop-up, so a new petitioner can be added. I do not
want the user to be able to just type a garbage name in the combo, since
first and last names are in separate fields. I want to prevent user error and
confusion as much as possible.

Last thing is I assume after setting it up this way, I would merely need to
do a dlookup to pull the petitioner phone and department into the sfrmBolo
subform?

Am I on the right track?

Thanks for your help,

Derek

 

[Klatuu]

See answers in line below:
--
Dave Hargis, Microsoft Access MVP

I want to revisit a few things posted in another thread I started a while
back. Any help from anyone is appreciated.

I have a main form "Suspects" and a subform "sfrmBolo". The "Suspects" form
is bound to the Suspects table and the subform is bound to the Bolo table.

The Suspect table contains details about a person involved in an incident,
while the Bolo subform contains incident details. The Bolo subform is on a
tab on the main form. The top of the main form contains person details. I
also have tabs for vehicle info, a physical description, and contact history.

One of the pices of information on the Bolo subform is the name of the
Petitioner (details below) which is my main reason for posting.

I also would like to know the best way to do the "Contact History" tab. It
would need to be a listing of all records associated with that person that we
could click and get details of that event (Bolo?)

Allen Browne Wrote:

"There are multiple ways to interface SuspectID and PetitionerID without
having to show the AutoNumber values. One way is to use a combo box, where
the RowSource is something like this:
SELECT PetitionerID, Surname & ", " + Firstname AS PetitionerName
FROM tblPetitioner
ORDER BY Surname, FirstName, PetitionerID;
Then set the first column to zero-width, so it stores the hidden
PetitionerID value, but displays the name:
Column Count 2
Column Widths 0
Bound Column 1 "

I now understand about the combo boxes you mentioned for PetitionerID. My
first question is this: Do I need to make a separate table tblPetitioner? If
so, I assume it would need to have PetitionerID (PK Autonumber), FirstName,
LastName, PhoneNumber, Department ?

Yes, you do need this table.

Assuming that table structure, Next question is: Do I need a separate form
to enter the Petitioner info? Would a sub-subform or subform be better?

A form would be best. If you are entering the petitioner in the bolo
subform, you can use a combo box as described by Allen Browne above. Use the
combo's Not In List event when an unknown petitioner is entered to open the
petitioner form and enter his information at that time.

Now, assuming it is set up this way, is it then correct to place a combo box
cboPetitioner bound to the PetitionerID PK field that does the query you gave
me earlier to combine the First and Last Name on the sfrmBolo subform? The
question is then, do I put the query "code" in the rowsource space on the
combo options, and how do I make this combo a drop-down list? Let me
clarify...if the petitioner does not exist in the list in the combo, I want
the add petitioner form to pop-up, so a new petitioner can be added. I do not
want the user to be able to just type a garbage name in the combo, since
first and last names are in separate fields. I want to prevent user error and
confusion as much as possible.

The query is the row source for the combo. All combos are drop downs.
Without the drop down, they become a text box.
As to controlling the garbage, you would do that with validation in the
petitioner form.

Last thing is I assume after setting it up this way, I would merely need to
do a dlookup to pull the petitioner phone and department into the sfrmBolo
subform?

Actually, you wouldn't need to do that. If you include the phone and
department in the combo's row source, you can use the After Update event of
the combo box to populate those controls.