Somewhat novice Access user, tripped up in a query problem

pokerboy801

OK, I will try to explain this as clearly and as concise as possible.
I am using Access, which has three MS Excel Linked tables, to store
call center metrics for reps. My Excel workbook has three separate
worksheets, all linked to Access. I have a query that performs some
minor calculations, not to hard, and will be ued to drive a report.
Here is my problem, I only have about 14 rows of data in each table as
we only have three reps. Originally, I had a relationship between the
three tables based on Agent Name. When I run the query I am
anticipating 14 lines of return data, however, I am receiving
approximately 2000. It appears to be returning an agents name with
their own row of data and again for every other person on my list it
gives their data too.
Is this something wrong with my query, reltionship or is it in Excel?

Sep 20 '07 #1

Ben

On Sep 20, 9:50 am, pokerboy...@gmail.com wrote:

OK, I will try to explain this as clearly and as concise as possible.
I am using Access, which has three MS Excel Linked tables, to store
call center metrics for reps. My Excel workbook has three separate
worksheets, all linked to Access. I have a query that performs some
minor calculations, not to hard, and will be ued to drive a report.
Here is my problem, I only have about 14 rows of data in each table as
we only have three reps. Originally, I had a relationship between the
three tables based on Agent Name. When I run the query I am
anticipating 14 lines of return data, however, I am receiving
approximately 2000. It appears to be returning an agents name with
their own row of data and again for every other person on my list it
gives their data too.
Is this something wrong with my query, reltionship or is it in Excel?

What does your query look like? Change the view to SQL and paste the
text here.

Sep 20 '07 #2

pokerboy801

On Sep 20, 11:53 am, Ben <bmarl...@gmail.comwrote:

On Sep 20, 9:50 am, pokerboy...@gmail.com wrote:

OK, I will try to explain this as clearly and as concise as possible.
I am using Access, which has three MS Excel Linked tables, to store
call center metrics for reps. My Excel workbook has three separate
worksheets, all linked to Access. I have a query that performs some
minor calculations, not to hard, and will be ued to drive a report.
Here is my problem, I only have about 14 rows of data in each table as
we only have three reps. Originally, I had a relationship between the
three tables based on Agent Name. When I run the query I am
anticipating 14 lines of return data, however, I am receiving
approximately 2000. It appears to be returning an agents name with
their own row of data and again for every other person on my list it
gives their data too.
Is this something wrong with my query, reltionship or is it in Excel?

What does your query look like? Change the view to SQL and paste the
text here.

Sorry, thought I'd get an email when a response came. Here is the SQL
view of my query:

SELECT [Group Attendance2].Date, [Group Attendance2].[Agent Name],
[Group Attendance2].[Scheduled Hours], [Group Attendance2].[Staffed
Time], [Group Attendance2].[ACD Time], [Group Attendance2].[Extn In
Time], [Group Attendance2].[Extn Out Time], [Group Attendance2].[ACD
Calls], [Group Attendance2]![ACD Calls]-Dialer2![ADJ Down] AS [# ACD
Calls], [Group Attendance2].[Extn In Calls], [Group Attendance2].[Extn
Out Calls], Dialer2.Calls, Dialer2.[ADJ Down], Dialer2!Calls+[Group
Attendance2]![ACD Calls]+[Group Attendance2]![Extn In Calls]+[Group
Attendance2]![Extn Out Calls]+Dialer2![ADJ Down] AS [Total Calls],
[Group AUX2].[Total Aux], [Group AUX2].[Aux 0], [Group AUX2].Lunch,
[Group AUX2].Break, [Group AUX2].Meeting, [Group AUX2].Training,
[Group AUX2].Project, [Group AUX2].Callback, [Group AUX2].Support,
[Group AUX2].Down, Dialer2!Update+[Group Attendance2]![ACW Time] AS
[Total Update], [Group Attendance2].[ACW Time], Dialer2.Login,
Dialer2.Logout, Dialer2.[Productive ], Dialer2.SIT, [Total Calls]/
[Group Attendance2]![Scheduled Hours] AS [Total CPH], [Group
Attendance2]![Staffed Time]/[Group Attendance2]![Scheduled Hours] AS
[Schedule Adherence %], ([Group Attendance2]![ACD Time]+[Group
Attendance2]![Extn In Time]+[Group Attendance2]![Extn Out Time]+[Group
Attendance2]![ACW Time]+Dialer2!Work-[Group AUX2]![Total AUX])/[Group
Attendance2]![Scheduled Hours] AS [Overall Productivity], [Group AUX2].
[Aux 0], [Group AUX2].[Aux 0]
FROM [Group Attendance2], [Group AUX2], Dialer2
WHERE ((([Group Attendance2].Date)=["Date?"]));

Sep 24 '07 #3

floronDBA

On 24 sep, 02:52, pokerboy...@gmail.com wrote:

On Sep 20, 11:53 am, Ben <bmarl...@gmail.comwrote:

On Sep 20, 9:50 am, pokerboy...@gmail.com wrote:

OK, I will try to explain this as clearly and as concise as possible.
I am using Access, which has three MS Excel Linked tables, to store
call center metrics for reps. My Excel workbook has three separate
worksheets, all linked to Access. I have a query that performs some
minor calculations, not to hard, and will be ued to drive a report.
Here is my problem, I only have about 14 rows of data in each table as
we only have three reps. Originally, I had a relationship between the
three tables based on Agent Name. When I run the query I am
anticipating 14 lines of return data, however, I am receiving
approximately 2000. It appears to be returning an agents name with
their own row of data and again for every other person on my list it
gives their data too.
Is this something wrong with my query, reltionship or is it in Excel?

What does your query look like? Change the view to SQL and paste the
text here.

Sorry, thought I'd get an email when a response came. Here is the SQL
view of my query:

SELECT [Group Attendance2].Date, [Group Attendance2].[Agent Name],

8<-------------------------------------------------------------------------------------------------------
SNIP

[Aux 0], [Group AUX2].[Aux 0]
FROM [Group Attendance2], [Group AUX2], Dialer2
WHERE ((([Group Attendance2].Date)=["Date?"]));

In other words, you select all rows from table [Group AUX2], all rows
from Dialer2, and only
those rows from [Group Attendance2] that have a specific Date.

>From your original questin:
we only have three reps. Originally, I had a relationship between the
three tables based on Agent Name. When I run the query I am

What happened to those relationships?
You may add them in your query:
WHERE ((([Group Attendance2].Date)=["Date?"]))
AND [Group Attendance2].[Agent Name]=[Group AUX2].[Agent Name]
AND [Group Attendance2].[Agent Name]=Dialer2.[Agent Name]

Sep 24 '07 #4

pokerboy801

On Sep 24, 10:03 am, floronDBA <peterbro...@floron.leidenuniv.nl>
wrote:

On 24 sep, 02:52, pokerboy...@gmail.com wrote:

On Sep 20, 11:53 am, Ben <bmarl...@gmail.comwrote:

On Sep 20, 9:50 am, pokerboy...@gmail.com wrote:

OK, I will try to explain this as clearly and as concise as possible.
I am using Access, which has three MS Excel Linked tables, to store
call center metrics for reps. My Excel workbook has three separate
worksheets, all linked to Access. I have a query that performs some
minor calculations, not to hard, and will be ued to drive a report.
Here is my problem, I only have about 14 rows of data in each tableas
we only have three reps. Originally, I had a relationship between the
three tables based on Agent Name. When I run the query I am
anticipating 14 lines of return data, however, I am receiving
approximately 2000. It appears to be returning an agents name with
their own row of data and again for every other person on my list it
gives their data too.
Is this something wrong with my query, reltionship or is it in Excel?

What does your query look like? Change the view to SQL and paste the
text here.

Sorry, thought I'd get an email when a response came. Here is the SQL
view of my query:

SELECT [Group Attendance2].Date, [Group Attendance2].[Agent Name],

8<-------------------------------------------------------------------------*------------------------------
SNIP

[Aux 0], [Group AUX2].[Aux 0]
FROM [Group Attendance2], [Group AUX2], Dialer2
WHERE ((([Group Attendance2].Date)=["Date?"]));

In other words, you select all rows from table [Group AUX2], all rows
from Dialer2, and only
those rows from [Group Attendance2] that have a specific Date.

From your original questin:
we only have three reps. Originally, I had a relationship between the
three tables based on Agent Name. When I run the query I am

What happened to those relationships?
You may add them in your query:
WHERE ((([Group Attendance2].Date)=["Date?"]))
AND [Group Attendance2].[Agent Name]=[Group AUX2].[Agent Name]
AND [Group Attendance2].[Agent Name]=Dialer2.[Agent Name]- Hide quoted text -

- Show quoted text -

I think I understand your question, let me see if this adds clarity
and I am on the right track. Each of the linked tables includes in
column1 the date - which should be the same. Am I getting this error
because I am not asking for the same date on each of the other two
linked tables (Group AUX and Dialer)?

Sep 25 '07 #5