Need help creating report

[Mcrawford]

I have created a call log database that our tech service department uses to
log their calls. In the Call_Logs table I have several yes/no fields. These
fields indicate types of questions the user is calling for (warranty info,
new installation info, Specification info, etc.). One or more fields may be
checked.

The users would like a report that lists how many calls they received for
each type over a given period of time. They also want to be able to select
which call types are included on the report. They envision a "form" in which
they can select checkboxes to include their desired call types. They may want
to select only one call type or several types.

What is the best way to go about this? Do I create a query? Or do I create a
form that filters information? If a form, how do I get the information into a
report? I'm new at Access and I'm a bit stumped on this one.

 

[Duane Hookom]

I would change the table structure to be normalized. If this isn't possible,
consider using a union query like:
SELECT CallID, "Warranty" As CallType
FROM Call_Logs
WHERE [Warranty] = True
UNION ALL
SELECT CallID, "New Install"
FROM Call_Logs
WHERE [New Install] = True
UNION ALL
-- etc for all YN fields
SELECT CallID, "Last Field"
FROM Call_Logs
WHERE [LastField] = True;

You can then use this query to display records with one or more CallType
values.

 

[Mcrawford]

I'm new to Access so I'm not familiar with what you mean by "normalized", can
you explain?

I would change the table structure to be normalized. If this isn't possible,
consider using a union query like:
SELECT CallID, "Warranty" As CallType
FROM Call_Logs
WHERE [Warranty] = True
UNION ALL
SELECT CallID, "New Install"
FROM Call_Logs
WHERE [New Install] = True
UNION ALL
-- etc for all YN fields
SELECT CallID, "Last Field"
FROM Call_Logs
WHERE [LastField] = True;

You can then use this query to display records with one or more CallType
values.
--
Duane Hookom
Microsoft Access MVP

I have created a call log database that our tech service department uses to
log their calls. In the Call_Logs table I have several yes/no fields. These
fields indicate types of questions the user is calling for (warranty info,
new installation info, Specification info, etc.). One or more fields may be
checked.

The users would like a report that lists how many calls they received for
each type over a given period of time. They also want to be able to select
which call types are included on the report. They envision a "form" in which
they can select checkboxes to include their desired call types. They may want
to select only one call type or several types.

What is the best way to go about this? Do I create a query? Or do I create a
form that filters information? If a form, how do I get the information into a
report? I'm new at Access and I'm a bit stumped on this one.

 

[Duane Hookom]

You have kinda set up a spreadsheet type table. There are several links on
normalization at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101.

I would have created your application with a Call/QuestionType table that
would include one record per call per question type.

The result of a union query somewhat models the table structure that I would
use. If you want to add another question type, you should never have to
create a new field or modify any form or report or query.
--
Duane Hookom
Microsoft Access MVP

I'm new to Access so I'm not familiar with what you mean by "normalized", can
you explain?

I would change the table structure to be normalized. If this isn't possible,
consider using a union query like:
SELECT CallID, "Warranty" As CallType
FROM Call_Logs
WHERE [Warranty] = True
UNION ALL
SELECT CallID, "New Install"
FROM Call_Logs
WHERE [New Install] = True
UNION ALL
-- etc for all YN fields
SELECT CallID, "Last Field"
FROM Call_Logs
WHERE [LastField] = True;

You can then use this query to display records with one or more CallType
values.
--
Duane Hookom
Microsoft Access MVP

I have created a call log database that our tech service department uses to
log their calls. In the Call_Logs table I have several yes/no fields. These
fields indicate types of questions the user is calling for (warranty info,
new installation info, Specification info, etc.). One or more fields may be
checked.

The users would like a report that lists how many calls they received for
each type over a given period of time. They also want to be able to select
which call types are included on the report. They envision a "form" in which
they can select checkboxes to include their desired call types. They may want
to select only one call type or several types.

What is the best way to go about this? Do I create a query? Or do I create a
form that filters information? If a form, how do I get the information into a
report? I'm new at Access and I'm a bit stumped on this one.