P
pareez
I maintain a database that stores exceptions to daily work schedules for help
desk agents. There are 3 tables in the database: 1) a table that stores a
daily calculated adherence for each agent, 2) a table that stores daily
logins/logouts for each agent and 3) a table that stores daily exceptions
that each agent inputs for their daily work schedule. All three tables share
2 common fields: an agent PIN #, eg. 1026, and a date field. I'm trying to
produce a report that will show data from all three tables based on a date
range specified by the database user. I've created the report based on 3
separate queries for each table. However, when the database user selects a
date range of more than one day, instead of showing the information for each
agent from each query one day at a time, it's showing all the information
from the first query, then all the information from second query and then all
the information from the third query. I want the report though to show
information from each query for each agent one day at a time.
For example, if I wanted to see data for an agent who has a PIN # of 1026
for the month of February, I want to see the information from the first query
on Feb. 1 for that agent, then information from the query on Feb. 1 for that
agent, then information from the third query for that agent on Feb. 1, and
then proceed with the next day in February in the same manner and so forth.
What the report is doing right now, is if I want to see all information for
agent 1026 for the month of February, the report is showing all information
from the first query for the month for that agent, then all information from
the second query for that agent for the month and then all information from
the third query for the month for this agent, it's not separating the days
like I want it to. If I choose to view all agents for the month of February,
then it shows all data in the first query for all agents for the month, then
all data from the second query for all agents for the month and then all data
from the third query for all agents for the month. I want the report to show
each agent, one day at a a time, with the appropriate information from each
query.
Right now the way the report is set up, is that I group by Agent and have an
Agent header and footer. In the Agent header I have just the column headings
for the table that stores daily logins/logouts for each agent. In the Detail
section I then have the table that stores daily logins/logouts for the
agents. Then I made 2 subreports for the other 2 tables and have those in
the Agent footer on the report.
Any help appreciated. Thanks.
desk agents. There are 3 tables in the database: 1) a table that stores a
daily calculated adherence for each agent, 2) a table that stores daily
logins/logouts for each agent and 3) a table that stores daily exceptions
that each agent inputs for their daily work schedule. All three tables share
2 common fields: an agent PIN #, eg. 1026, and a date field. I'm trying to
produce a report that will show data from all three tables based on a date
range specified by the database user. I've created the report based on 3
separate queries for each table. However, when the database user selects a
date range of more than one day, instead of showing the information for each
agent from each query one day at a time, it's showing all the information
from the first query, then all the information from second query and then all
the information from the third query. I want the report though to show
information from each query for each agent one day at a time.
For example, if I wanted to see data for an agent who has a PIN # of 1026
for the month of February, I want to see the information from the first query
on Feb. 1 for that agent, then information from the query on Feb. 1 for that
agent, then information from the third query for that agent on Feb. 1, and
then proceed with the next day in February in the same manner and so forth.
What the report is doing right now, is if I want to see all information for
agent 1026 for the month of February, the report is showing all information
from the first query for the month for that agent, then all information from
the second query for that agent for the month and then all information from
the third query for the month for this agent, it's not separating the days
like I want it to. If I choose to view all agents for the month of February,
then it shows all data in the first query for all agents for the month, then
all data from the second query for all agents for the month and then all data
from the third query for all agents for the month. I want the report to show
each agent, one day at a a time, with the appropriate information from each
query.
Right now the way the report is set up, is that I group by Agent and have an
Agent header and footer. In the Agent header I have just the column headings
for the table that stores daily logins/logouts for each agent. In the Detail
section I then have the table that stores daily logins/logouts for the
agents. Then I made 2 subreports for the other 2 tables and have those in
the Agent footer on the report.
Any help appreciated. Thanks.