473,699 Members | 3,282 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 1940
Ben
On Sep 20, 9:50 am, pokerboy...@gma il.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...@gma il.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...@gma il.com wrote:
On Sep 20, 11:53 am, Ben <bmarl...@gmail .comwrote:


On Sep 20, 9:50 am, pokerboy...@gma il.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...@fl oron.leidenuniv .nl>
wrote:
On 24 sep, 02:52, pokerboy...@gma il.com wrote:
On Sep 20, 11:53 am, Ben <bmarl...@gmail .comwrote:
On Sep 20, 9:50 am, pokerboy...@gma il.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
18667
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 LEFT JOIN UserTeamAssoc UTA ON UTA.userID = U.userID, Role R, UserRoleAssoc URA, Team T WHERE U.userID = URA.userID AND URA.roleID = R.roleID AND U.userId > 1
1
1762
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 called PAL_PA Summary and in that query I calculate several fields, all %s: Fed, ITS, SALT which are 3 of the 6 columns for example. Basically its a query by person that shows the % of their time spent in these diff fields (they add up to 100%). ...
24
41587
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 file system because in some front ends the user needs update access. I want to give users access to the data for reporting with their own queries and reports. I just don't want to take a chance on them
4
3527
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 objective in utilizing this new deployment method is to reduce the maintenance overhead as well as making it easier for my users to setup and run the application initially. I have VS 2002, Windows XP, Access XP(2000 format). He is my problem....
39
3081
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 have one table and one queries which is based on this table. This query is good working for these two users.
9
3055
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. Below you will find the code I've written and the error that results. I'm hoping that someone can give me some direction as to what syntax or parameter is missing from the code that is expected by VBA. Overview: I'm trying to copy calculated...
8
2017
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 database (mdb) was installed in remote client machines working with Access2000 and it worked fine. When the query was put on an Access2002 machine, it came up with "ORA-01017 invalid logon/password", and fails. The failing machine passes other...
4
1787
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 the load here are some specs re my database: Back End: Access -> Jet Engine Front Ends: Access -> Jet Engine usually not more than 2 users accessing the database and the back end is stored on one of the users computers which is quite a new...
2
1206
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 to be simple and easy. I have built a table with the following Field Names: Screen names, Query Number, and Query Field Name. I need to create a Form that allows the user to have a Combo Box to choose the Screen name that then displays the...
0
8704
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8623
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9053
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8894
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6540
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4390
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4636
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3071
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2360
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.