472,985 Members | 2,609 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,985 software developers and data experts.

Somewhat novice Access user, tripped up in a query problem

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
4 1901
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: Phil Powell | last post by:
<cfquery name="getAll" datasource="#request.dsn#"> SELECT U.userID, U.fname, U.lname, U.phone, U.lastLoggedIn, U.choiceId, U.experience, T.label AS teamLabel, R.label AS roleLabel FROM User U...
1
by: gretchen.ogrady | last post by:
I admit - I'm a simple user but looking to improve skills. Instructions aren't helping and have searched this group but am getting bogged down by some of the programming-speak. I have a query...
24
by: Bob Alston | last post by:
Anyone know a way to make all access to a linked table, in another Access MDB, read only? I really don't want all the hassle of implementing full access security. I can't do this at the server...
4
by: James | last post by:
I have a VB windows forms application that accesses a Microsoft Access database that has been secured using user-level security. The application is being deployed using No-Touch deployment. The...
39
by: Marcin Zmyslowski | last post by:
Hello all! I have the following problem with MS Access 2003 permissions. I have two users. One is admin and the second one is user who has full permissions to enter modify and read data. I...
9
by: Kelii | last post by:
I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped....
8
by: mainframetech | last post by:
We are having a problem with Access 2002 as opposed to Access 2000. A multi-file query was set up under 2000 and tested good. Access is connected through a link to an Oracle 9.1 database. The...
4
by: iheartvba | last post by:
Hi guys, Thanks to a lot of luck and help from you great people, I am on the verge of completing my first Database/Application. but at the moment the Jet Engine back end is not able to handle...
2
by: DayzedandConfused | last post by:
Here I go.. I haven't worked with Access for almost a decade and due to recent abrupt job change need to write a simple data base. I am creating a data base to reference a larger one. It needs...
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.