By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Somewhat novice Access user, tripped up in a query problem

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.