Query using check box data

[CaptDD]

Hello,

I have a database and a query. Little background. The database is
Emergency Run information for my Fire Department. What I am attempting to
do, with little success, is create a query that will generate data based on
time spent times number of responders for each run.

In the form / database there is a check box for each of the employees which
is checked when that person responds to the run. I also have time boxes for
the response times of each run.

What I am trying to do is create a query field that takes the elapsed time
and multiplies it times the number of check boxes checked in that record.
The data that shows in the table or query for the check box (when checked) is
-1.

I can't seem to get a count of the number of check boxes.

Tried a field --> Men:Sum([name1]+[name2]+[name3]) but it brings no result.
I can set each name field to a count, but then I can't use the count to get a
sum.

Hope this wasn't too confusing.

 

[Allen Browne]

You can get the number of men by typing an expression like this into a fresh
column in the Field row in query design:
Men: - ( [name1] + [name2] + [name3] )

But that's not the right way to design a relational database. You need to
have at least these 3 tables:
Employee table (one record for each person), with fields:
EmployeeID AutoNumber primary key
Surname Text
Firstname Text
...

Incident table (one record for each call out), with fields such as:
IncidentID AutoNumber primary key
IncidentDateTime Date/Time when the call out occurred.
...

IncidentEmployee table
IncidentID Number Relates to Incident.IncidentID
EmployeeID Number Relates to Employee.EmployeeID

So, if 3 people respond to incident 7, you have 3 rows for that incident in
this table. You interface this with a main form bound to the Incident table,
and a subform bound to the IncidentEmployee table. The subform is in
continuous view, and you add as many rows as you need for the people who
responded. The subform has a combo box for choosing the employee. (The
combo's RowSource is the Employee table.)

A major advantage of doing it the relational way is that when a new person
joins, you don't have to redesign all your tables, queries, forms and
reports. You add them as a record in the Employee table, and they
automatically show up in the subform's combo.

Technically, you have a many-to-many relation between employees and
incidents (i.e. one employee responds to multiple incident, and one incident
can have many people respond to it.) The 3rd table resolves that into a pair
of one-to-many relations. This junction table is the standard solution for a
many-to-many. For another example, see:
http://allenbrowne.com/casu-06.html

If you want to read further on that, the keyword is normalization. Here's a
starting point:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I have a database and a query. Little background. The database is
Emergency Run information for my Fire Department. What I am attempting to
do, with little success, is create a query that will generate data based
on
time spent times number of responders for each run.

In the form / database there is a check box for each of the employees
which
is checked when that person responds to the run. I also have time boxes
for
the response times of each run.

What I am trying to do is create a query field that takes the elapsed time
and multiplies it times the number of check boxes checked in that record.
The data that shows in the table or query for the check box (when checked)
is
-1.

I can't seem to get a count of the number of check boxes.

Tried a field --> Men:Sum([name1]+[name2]+[name3]) but it brings no
result.
I can set each name field to a count, but then I can't use the count to
get a
sum.

Hope this wasn't too confusing.

 

[CaptDD]

You can get the number of men by typing an expression like this into a fresh
column in the Field row in query design:
Men: - ( [name1] + [name2] + [name3] )

But that's not the right way to design a relational database. You need to
have at least these 3 tables:
Employee table (one record for each person), with fields:
EmployeeID AutoNumber primary key
Surname Text
Firstname Text
...

Incident table (one record for each call out), with fields such as:
IncidentID AutoNumber primary key
IncidentDateTime Date/Time when the call out occurred.
...

IncidentEmployee table
IncidentID Number Relates to Incident.IncidentID
EmployeeID Number Relates to Employee.EmployeeID

So, if 3 people respond to incident 7, you have 3 rows for that incident in
this table. You interface this with a main form bound to the Incident table,
and a subform bound to the IncidentEmployee table. The subform is in
continuous view, and you add as many rows as you need for the people who
responded. The subform has a combo box for choosing the employee. (The
combo's RowSource is the Employee table.)

A major advantage of doing it the relational way is that when a new person
joins, you don't have to redesign all your tables, queries, forms and
reports. You add them as a record in the Employee table, and they
automatically show up in the subform's combo.

Technically, you have a many-to-many relation between employees and
incidents (i.e. one employee responds to multiple incident, and one incident
can have many people respond to it.) The 3rd table resolves that into a pair
of one-to-many relations. This junction table is the standard solution for a
many-to-many. For another example, see:
http://allenbrowne.com/casu-06.html

If you want to read further on that, the keyword is normalization. Here's a
starting point:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

Ok, now I'm totally lost again....

I've created two additional tables so I now have three.

First table is Rep07 which is the data collected for each incident report.
Second table is Employees which is self explanatory.
Third table is Incident Employees.

I've created relationships between the three tables
[Rep07] relates RunNumber to [IncidentEployees] IncidentID
[Employees] relates IDNumber to [IncidentEmployees] EmployeeID
[Employees] relates Name to [IncidentEmployees] EmployeeName

I have a form Rescue which is based on the Rep07 table. I want to create
the subform for IncidentEmployees to reflect the employees that responded to
the incident for which the form is being completed.

The problem I'm having is how to create the subform to show a table in which
to all the list of employees that responded along with a combo box listing
all employees from which to choose to be entered into the subforms table?

I can get a subform table to show the EmployeeName field but can't figure
out how to use a combo box to retrieve the data.

 

[Allen Browne]

So you have the 3 tables:
- Rep07 = the incidents, with primary key field RunNumber.
- Employees = the list of staff, with primary key field IDNumber.
- IncidentEployees = the junction table.

IncidentEployees table has these fields:
- a primary key (perhaps IncidentEployeesID.)
- IncidentID, with a relationship to Rep07.RunNumber.
- EmployeeID, with a relationship to Employees.EmployeeID.
- Name, with a relationship to Employees.Name.

The last field is wrong: you do not store the employee name in the
IncidentEployees table. It exists in the Employees table only, and you just
use the EmployeeID to look up the employee's name. That way you cannot get
an inconsistency (where the IncidentEployees table has one EmployeeID and a
different Name.)

Can I also suggest avoiding naming a field Name. Nearly everything (such as
forms) has a Name property, and Access can get confused between the name of
the form and the contents of the Name field. In general, you want to put
FirstName and Surname in different fields anyway - makes it easier to
search.

So, if you Employees table has fields for Surname and Firstname, in your
subform (bound to the IncidentEployees table), you would have a combo bound
to the EmployeeID field, and you set these properties for the combo:
Column Count: 2
Column Widths: 0
Bound Column: 1
Row Source: SELECT IDNumber, Surname & ", " & FirstName AS FullName
FROM Employees ORDER BY Surname, FirstName,
IDNumber;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

You can get the number of men by typing an expression like this into a
fresh
column in the Field row in query design:
Men: - ( [name1] + [name2] + [name3] )

But that's not the right way to design a relational database. You need to
have at least these 3 tables:
Employee table (one record for each person), with fields:
EmployeeID AutoNumber primary key
Surname Text
Firstname Text
...

Incident table (one record for each call out), with fields such as:
IncidentID AutoNumber primary key
IncidentDateTime Date/Time when the call out occurred.
...

IncidentEmployee table
IncidentID Number Relates to Incident.IncidentID
EmployeeID Number Relates to Employee.EmployeeID

So, if 3 people respond to incident 7, you have 3 rows for that incident
in
this table. You interface this with a main form bound to the Incident
table,
and a subform bound to the IncidentEmployee table. The subform is in
continuous view, and you add as many rows as you need for the people who
responded. The subform has a combo box for choosing the employee. (The
combo's RowSource is the Employee table.)

A major advantage of doing it the relational way is that when a new
person
joins, you don't have to redesign all your tables, queries, forms and
reports. You add them as a record in the Employee table, and they
automatically show up in the subform's combo.

Technically, you have a many-to-many relation between employees and
incidents (i.e. one employee responds to multiple incident, and one
incident
can have many people respond to it.) The 3rd table resolves that into a
pair
of one-to-many relations. This junction table is the standard solution
for a
many-to-many. For another example, see:
http://allenbrowne.com/casu-06.html

If you want to read further on that, the keyword is normalization. Here's
a
starting point:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

Ok, now I'm totally lost again....

I've created two additional tables so I now have three.

First table is Rep07 which is the data collected for each incident report.
Second table is Employees which is self explanatory.
Third table is Incident Employees.

I've created relationships between the three tables
[Rep07] relates RunNumber to [IncidentEployees] IncidentID
[Employees] relates IDNumber to [IncidentEmployees] EmployeeID
[Employees] relates Name to [IncidentEmployees] EmployeeName

I have a form Rescue which is based on the Rep07 table. I want to create
the subform for IncidentEmployees to reflect the employees that responded
to
the incident for which the form is being completed.

The problem I'm having is how to create the subform to show a table in
which
to all the list of employees that responded along with a combo box listing
all employees from which to choose to be entered into the subforms table?

I can get a subform table to show the EmployeeName field but can't figure
out how to use a combo box to retrieve the data.

 

[CaptDD]

So you have the 3 tables:
- Rep07 = the incidents, with primary key field RunNumber.
- Employees = the list of staff, with primary key field IDNumber.
- IncidentEployees = the junction table.

IncidentEployees table has these fields:
- a primary key (perhaps IncidentEployeesID.)
- IncidentID, with a relationship to Rep07.RunNumber.
- EmployeeID, with a relationship to Employees.EmployeeID.
- Name, with a relationship to Employees.Name.

The last field is wrong: you do not store the employee name in the
IncidentEployees table. It exists in the Employees table only, and you just
use the EmployeeID to look up the employee's name. That way you cannot get
an inconsistency (where the IncidentEployees table has one EmployeeID and a
different Name.)

Can I also suggest avoiding naming a field Name. Nearly everything (such as
forms) has a Name property, and Access can get confused between the name of
the form and the contents of the Name field. In general, you want to put
FirstName and Surname in different fields anyway - makes it easier to
search.

So, if you Employees table has fields for Surname and Firstname, in your
subform (bound to the IncidentEployees table), you would have a combo bound
to the EmployeeID field, and you set these properties for the combo:
Column Count: 2
Column Widths: 0
Bound Column: 1
Row Source: SELECT IDNumber, Surname & ", " & FirstName AS FullName
FROM Employees ORDER BY Surname, FirstName,
IDNumber;

Thanks again Allen!!

I'll be giving this a go in a couple days when I return to work and the
headache has subsided... LOL

 

[CaptDD]

Thanks again Allen!!!

I'll be giving this a go in a couple days when I again am at work and the
headache has had time to subside... LOL

 

[CaptDD]

Still not working correctly. I have a combobox which presents the Surnames
for selection. When I choose one, it puts the EmployeeID number in the
EmployeeName box in the subform. If I select a second name I get a warning
as the IncidentNumber is not a unique entry and the primary field can not
have duplicate entries.

Guess this program is just too complex for the average user!!!

Thanks for trying to help, I give UP!!!!

 

[CaptDD]

Still not working correctly. I have a combobox which presents the Surnames
for selection. When I choose one, it puts the EmployeeID number in the
EmployeeName box in the subform. If I select a second name I get a warning
as the IncidentNumber is not a unique entry and the primary field can not
have duplicate entries.

Guess this program is just too complex for the average user!!!

Thanks for trying to help, I give UP!!!!

Ok, so I lied!!! I couldn't just give up on this thing (Chief wouldn't let
me.. LOL). I found my error, the IncidentEmployees table could NOT have a
Primary Key set to the Incident ID. I simply cleared the data from the table
and removed the Primary Key designation. Now the form shows the names as
desired and I can add as many or as few as I desire!!! WOOHOO!!! All of the
relationships are as Allen stated above. That means that when I switch
between the records in the main form, the subform changes to reflect the
entries for that particular incident.

Now for the BIG headaches, creating the queries to calculate totals from
incidents on a daily, monthly and annual basis!!! Don't think I won't be
posting again!!! LOL

Thanks so much Allen for your assistance!!!