Query/Expression Help Required!

Hello, i would appreciate any help in regards to the problem i am
having.

I will try and explain it to the best of my ability (im a newbie to
Access 2000)

Here is a snipt of the information i have extracted via a query. I
have been able to work out Duration via a calculation.


"Agent Pin" "Start Time" "AUX Name"        "End Time" "Duration"
"7015"      "13:45:21"   "After Call Work" "13:54:18" "537"
"7015"	    "14:30:25"   "After Call Work" "14:31:03" "38"
"7015"	    "15:28:50"   "Tea Break"       "15:31:09" "139"
"7015"      "15:31:31"   "People Support"  "15:33:13" "102"
"7015"      "15:33:13"   "Tea Break"       "15:50:20" "1027"
"7015"      "16:11:25"   "After Call Work" "16:19:55" "510"
"7018"      "08:00:26"   "Start Up"        "08:10:26" "600"
"7018"      "08:58:15"   "People Support"  "09:03:58" "343"
"7018"      "09:15:11"   "Tea Break"       "09:30:51" "940"
"7018"      "10:28:36"   "People Support"  "10:29:22" "46"
"7018"	    "10:29:57"   "People Support"  "10:35:00" "303"
"7018"      "12:33:38"   "Meal Break"      "13:03:38" "1800"
"7018"      "14:00:11"   "Coaching"        "15:03:50" "3819"
"7018"      "15:03:50"   "Tea Break"       "15:20:19" "989"
"7018"      "15:20:19"   "QOL"             "17:10:05" "6586"
"7018"      "17:10:05"   "Wind Down"       "17:13:10" "185"
"7115"      "07:49:56"   "Start Up"        "08:00:10" "614"
"7115"      "09:06:45"   "People Support"  "09:10:27" "222"
"7115"      "10:01:29"   "Tea Break"       "10:15:24" "835"
"7115"      "11:36:29"   "After Call Work" "11:40:24" "235"
"7115"      "12:01:04"   "Meal Break"      "12:55:59" "3295"
"7115"      "14:06:28"   "After Call Work" "14:08:25" "117"
"7115"      "14:26:22"   "Tea Break"       "14:26:46" "24"
"7115"      "15:56:36"   "Wind Down"       "16:00:07" "211"

My questions are
1) I need to work out "Total Time at Work": I can make a query that
searches "AUX Name" for Start Up & Wind Down as follows

							"Time at Work"
"7018"  "08:00:26"   "Start Up"    "08:10:26" "600"
"7018"  "17:10:05"   "Wind Down"   "17:13:10" "185"	??????????????
"7115"  "07:49:56"   "Start Up"    "08:00:10" "614"
"7115"  "15:56:36"   "Wind Down"   "16:00:07" "211"	??????????????
etc	etc	     etc	   etc        etc.

I just cant work out how to actually get "Total Time at work" for each
and every "Agent Pin" (basically i dont know how to get Wind Down(End
Time) minus Start Up(Start time))

2) I also need to be able to get a total time of any specific "Aux
Name" I am able to produce the query to search to specific code, but
again just have no idea how to get the total (i struggle because of
the two(or more) "Agent Pin" fields.
eg
	      					        "Total Breaks"
"7015"	 "15:28:50"   "Tea Break"   "15:31:09" "139"
"7015"   "15:33:13"   "Tea Break"   "15:50:20" "1027"	??????????????
"7018"   "09:15:11"   "Tea Break"   "09:30:51" "940"
"7018"   "12:33:38"   "Meal Break"  "13:03:38" "1800"	??????????????
"7115"   "10:01:29"   "Tea Break"   "10:15:24" "835"
"7115"   "12:01:04"   "Meal Break"  "12:55:59" "3295"
"7115"   "14:26:22"   "Tea Break"   "14:26:46" "24"	??????????????


Any help would be much appreciated

ps: i hope my crazy explanations made sense :)

0
sund00bie
5/21/2007 1:27:40 PM
access 16762 articles. 3 followers. Follow

2 Replies
763 Views

Similar Articles

[PageSpeed] 47

To get the total time at work use a subquery to sum all durations for the 
Agent Pin by correlating the subquery with the outer query on this column.  
To get the total breaks do the same but also restrict the subquery on the AUX 
Name column:

SELECT [Agent Pin], [Start Time], [AUX Name], [End Time],
DATEDIFF("s",[Start Time],[End Time]) AS Duration,
   (SELECT SUM(DATEDIFF("s",[Start Time],[End Time]))
    FROM [YourTable] AS T2
    WHERE T2.[Agent Pin] = T1.[AgentPin])
AS [Total time at work],
   (SELECT SUM(DATEDIFF("s",[Start Time],[End Time]))
    FROM YourTable AS T3
    WHERE T3.[Agent Pin] = T1.[AgentPin]
    AND [AUX Name] LIKE "*break")
AS [Total breaks]
FROM [YourTable] AS T1;

If, as I'd imagine to be the case, the table includes data for more than one 
day then you'd also need to restrict the outer query and both subqueries to 
the relevant day for which you are computing the times.
 
If you want to show a value in seconds in the format hh:nn:ss divide the 
value by the number of seconds in a day and call the Format function like so:

FORMAT([Value in Seconds]/86400,"hh:nn:ss")

Ken Sheridan
Stafford, England

"sund00bie@gmail.com" wrote:

> Hello, i would appreciate any help in regards to the problem i am
> having.
> 
> I will try and explain it to the best of my ability (im a newbie to
> Access 2000)
> 
> Here is a snipt of the information i have extracted via a query. I
> have been able to work out Duration via a calculation.
> 
> 
> "Agent Pin" "Start Time" "AUX Name"        "End Time" "Duration"
> "7015"      "13:45:21"   "After Call Work" "13:54:18" "537"
> "7015"	    "14:30:25"   "After Call Work" "14:31:03" "38"
> "7015"	    "15:28:50"   "Tea Break"       "15:31:09" "139"
> "7015"      "15:31:31"   "People Support"  "15:33:13" "102"
> "7015"      "15:33:13"   "Tea Break"       "15:50:20" "1027"
> "7015"      "16:11:25"   "After Call Work" "16:19:55" "510"
> "7018"      "08:00:26"   "Start Up"        "08:10:26" "600"
> "7018"      "08:58:15"   "People Support"  "09:03:58" "343"
> "7018"      "09:15:11"   "Tea Break"       "09:30:51" "940"
> "7018"      "10:28:36"   "People Support"  "10:29:22" "46"
> "7018"	    "10:29:57"   "People Support"  "10:35:00" "303"
> "7018"      "12:33:38"   "Meal Break"      "13:03:38" "1800"
> "7018"      "14:00:11"   "Coaching"        "15:03:50" "3819"
> "7018"      "15:03:50"   "Tea Break"       "15:20:19" "989"
> "7018"      "15:20:19"   "QOL"             "17:10:05" "6586"
> "7018"      "17:10:05"   "Wind Down"       "17:13:10" "185"
> "7115"      "07:49:56"   "Start Up"        "08:00:10" "614"
> "7115"      "09:06:45"   "People Support"  "09:10:27" "222"
> "7115"      "10:01:29"   "Tea Break"       "10:15:24" "835"
> "7115"      "11:36:29"   "After Call Work" "11:40:24" "235"
> "7115"      "12:01:04"   "Meal Break"      "12:55:59" "3295"
> "7115"      "14:06:28"   "After Call Work" "14:08:25" "117"
> "7115"      "14:26:22"   "Tea Break"       "14:26:46" "24"
> "7115"      "15:56:36"   "Wind Down"       "16:00:07" "211"
> 
> My questions are
> 1) I need to work out "Total Time at Work": I can make a query that
> searches "AUX Name" for Start Up & Wind Down as follows
> 
> 							"Time at Work"
> "7018"  "08:00:26"   "Start Up"    "08:10:26" "600"
> "7018"  "17:10:05"   "Wind Down"   "17:13:10" "185"	??????????????
> "7115"  "07:49:56"   "Start Up"    "08:00:10" "614"
> "7115"  "15:56:36"   "Wind Down"   "16:00:07" "211"	??????????????
> etc	etc	     etc	   etc        etc.
> 
> I just cant work out how to actually get "Total Time at work" for each
> and every "Agent Pin" (basically i dont know how to get Wind Down(End
> Time) minus Start Up(Start time))
> 
> 2) I also need to be able to get a total time of any specific "Aux
> Name" I am able to produce the query to search to specific code, but
> again just have no idea how to get the total (i struggle because of
> the two(or more) "Agent Pin" fields.
> eg
> 	      					        "Total Breaks"
> "7015"	 "15:28:50"   "Tea Break"   "15:31:09" "139"
> "7015"   "15:33:13"   "Tea Break"   "15:50:20" "1027"	??????????????
> "7018"   "09:15:11"   "Tea Break"   "09:30:51" "940"
> "7018"   "12:33:38"   "Meal Break"  "13:03:38" "1800"	??????????????
> "7115"   "10:01:29"   "Tea Break"   "10:15:24" "835"
> "7115"   "12:01:04"   "Meal Break"  "12:55:59" "3295"
> "7115"   "14:26:22"   "Tea Break"   "14:26:46" "24"	??????????????
> 
> 
> Any help would be much appreciated
> 
> ps: i hope my crazy explanations made sense :)
> 
> 

0
Utf
5/21/2007 5:16:00 PM
Thanks so much for your detailed response!

i will take you advise and see what i can come up with.  Your
explanation makes prefect sense!

In regards to the dates. I am provided with a new file everyday in the
same format. So once i have been able to produce the required result
for 1 days data. My next object would be to try and make it as stream
line as possible to upload a new file daily and keep a cumulative
tally so i will be able to get monthly data, but thats for another
day.

Again thanks very much for your help, i will keep you posted as to how
i go.

Cheers

0
sund00bie
5/22/2007 8:14:06 AM
Reply:

Similar Artilces:

repairing Outlook Express
Its taking ages to load up all of a sudden, so I assume that there must be a corrupted file somewhere. I am using XP and ie8. How can I repair it without going back to ie6? Advice would be much appreciated. -- Richard Woollacott "Richwoo" <rwoollacott(nospam)@bigfoot.com> wrote in message news:4bb1e626$1@extreme.x-privat.org... > Its taking ages to load up all of a sudden, so I assume that there must be > a corrupted file somewhere. I am using XP and ie8. How can I repair it > without going back to ie6? Advice would be much appreciated. ...

Help: Money Transfer Transaction Dates
I was using Money 2001 and recently upgraded to Money 2004. One the issues i've had with both Money and prior versions of Quicken is the inability to accurately record the dates for money transfers from one account to another (especially from a Checking account to a Credit Card) where the posting dates are different. For example... If i transfer x dollars from my checking account to pay off my MasterCard... my bank statement shows the transaction occuring on 01 OCT 2003 whereas my credit card statement shows the transaction occuring on 30 SEP 2003. When entering the transfer / payment...

Help... Custom entity may have damaged the db
I created a custom entity for a client, then, upon creating a relationship Lookup to Opportunities, the screen froze. When I shut it down, I went back into the customization menu, and was told that the server was unavailable. I think that stopping the Relationship process may have hurt the db. Is there a way to roll back? I would check the event logs on both the CRM web and sql server. ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "MD" <MD@discussions.microsoft.com> wrote in message news:3759DD21-0E71-4AEC-8425-358C17E706...

DateAdd Working Day Query
Hi, I wonder if someone could help me please. I'm trying to write an expression (see code below) within a query where I calculate 15 working days from a a given date, in this case the 'Date a report was sent'. DateAdd(“ww”,15,[Date Report Sent]-DateAdd([Date Report Sent],1)*2-IIf (Weekday([Date Report Sent],1)=7,IIf(Weekday([Date Report Sent],1)=7,0,1),IIf (Weekday([Date Report Sent],1)=7,-1,0))) But when I add it to my query I keep getting this message: 'The expression you entered has a function containing the wrong number of arguments.' I'm assuming, maybe wrongl...

Outlook Express #311
Can someone please direct me to a Microsoft Outlook Express newsgroup? Thanks microsoft.public.outlookexpress.general.=20 --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, KEN asked: | Can someone please direct me to a Microsoft Outlook Express newsgroup? | Thanks ...

Update Query and record lock violations
I have a local table constructed from two remote sqltables. I add records successfully via an append query in Access 2003. I am attempting to modify date records that have changed in the remote table to the local table (they change occasionally in the sqltable) via update query. I have the query set to pull date and time from the sqltable (there is a common key to both tables that acts as the master key in the local table) and update the date and time in the local table where the date and time are not equal in both tables. It seems to work - it returns exactly the number of records ...

backup help
Greetings, XP Pro, I have tried to backup to an ext hd thru the wizard and with run...ntbackup.exe..etc.. and both ways, it freezes when I start backup. What do you suggest? Thanks, JS haven't done an ntbackup in a while because I use disk imaging. however, what you might try to do is to create the backup file on the hard drive. then copy the archive over to the external disk. -- db���`�...�><)))�> DatabaseBen, Retired Professional - Systems Analyst - Database Developer - Accountancy - Veteran of the Armed Forces - Microsoft Partner - ...

Using an Object from a Current form for Criteria in a Query
I am using Access 2000. I have a Command Button on a form that is designed to preview or print that customer's invoice. I use a Pop-Up Dialog Box. The end user will supply 2 pieces of data in this Dialog Box. This data, together with the Customer ID from the current form. will provide the 3 necessary pieces of info to pass on to the query, and eventually the report, that creates the invoice. I suspect that the "Me." or "Me!" "command" or "action" must be used, but am not clear about how to use it. Perhaps someone can give me so...

Help - Money 2005
Help, Everything was working fine, then all of a sudden none of my online services were working. it gets stuck in the "update in progress' status, and always says there is an error. I've tried reinstalling, money, i've tried restoring from backup, i've also tried disabling online services and reenabling them. The consistent message that I keep getting is "To set up your accounts, you must be online. Please close Money, and then sign in to your file. When you sign in, make sure the Work offline box is cleared. " However, I'm not offline, the box isn&...

Help with counting user entered text values
Hi, Version: Excel 2000 Situation: In Column G, I will have testers entering in their initals. I would like to display the tester's initals in column H of another worksheet with the number of times that set of intals appeared in column G on column I of the other worksheet. The catch is that I do not have a list of the testers initals ahead of time, so I'll need to pull them from column H. Thanks for any and all help in advance, David Hi David, H10: DAL i10: =COUNTIF(G:G,H10) or if you don't want to see 0 if H10 is empty I10: =IF(H10="","",COU...

Outlook express 6.0 export/import
I am trying to import messages from OE 6.0 Win 98 machine) which are in *.dbx format into another OE 6.0 on another machine(XP).. It gives me MAPI error.. What seeems to be the problem ? >-----Original Message----- >I am trying to import messages from OE 6.0 Win 98 machine) >which are in *.dbx format into another OE 6.0 on another >machine(XP).. It gives me MAPI error.. >What seeems to be the problem ? > >. > Backup and Restore OE http://insideoe.tomsterdam.com/backup/index.htm This newsgroup is for support of Outlook 97/98/2000/2002/2003 from the Office suite...

Outlook Express Address Book won't export
I've inherited an Outlook Express address book containing only one "contact": a list with about 300 email addresses in it, nothing else. I need to export it to a .CSV file, and the export dialogue says the export has been completed, but the file is nowhere to be found. I've tried changing the file to a .txt file before exporting; I've chosen different directories: *no file to be found*. This is driving me nuts. What on earth am I doing wrong or can I try? Thanks, Tom This newsgroup is for support of Outlook 97, 98, 2000, 2002 & 2003 from the Office family for In...

help
Hello, I imported some transactions and later deleted them. Now when I try and import from my bank, M2004 does not import them. I have to manually enter them. Is there a way to tell money to import again? Money acts like it is out of sync with my bank transactions. Thanks for the help. Frank In microsoft.public.money, Frank wrote: > >I imported some transactions and later deleted them. >Now when I try and import from my bank, M2004 does not import them. I have >to manually enter them. > >Is there a way to tell money to import again? Not short of disabling online acce...

Opportunity needs to display Account and Contact
We are using opportunities to track our forecasted sales. There are multiple contacts with an account, so Opportunities are linked to the contact person. However, when viewed from a list of all opportunities, it just diplays the Contact Name - not the underlying acount the contact is associated with. We need to have the company name on the account also displayed. Any ideas how to do that? I thought about mapping accountid on the contact records to the opportunity and then displaying that field...but it is a system field with a data type that cannot be added (Lookup). Any other ideas, abs...

Freezing of Windows
Ok, I will try to give a brief summation of the issue: Brand new computer I built (Biostary Mobo), AMD 4600, 2 gig RAM, 250 SATA hd, KVM switch, Lab tech mouse and generic keyboard. The computer has XP SP1, SP2 and SP3 (over the history) on it. Freezes periodically, the mouse will no longer click but moves around. It boots fine, but this freeze occurs when running firefox, ie, office etc. You cannot type or do anything else with the computer - alt cnt del does not work. Needs to be shut off to do anything. Occasional message - HD failure - but boots if left alone. Reinstalled W...

Lookup help #2
I am using vlookup match to pull data from a .CSV file and I need to list in my new sheet any data that is held under a certain name, if that name is repeted the first entery of that name my lookup picks up continuously. How would I be able to use a lookup that would pick up all the enteries under that name going through a long list of data? Thanks -- Barky ------------------------------------------------------------------------ Barky's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14914 View this thread: http://www.excelforum.com/showthread.php?threadid=265...

using Query Based distribution groups between domains
I am trying to use query based DG's for passing world wide notificatons across domains. I have a global QBDG containing QBDG from our 3 child domains. When I am sendin an email to this DG the email only reach the recipients in the domain where the global dg is placed, none of the the recipients in the other child domains gets the email. When I do a preview on the global DG the child domain qbdg is visible so it looks as if it would work. Any ideas appreciated. Sounds like an issue with the expansion server. Have you considered setting the expansion server to try and work through t...

Self Destructing Queries #2
Doesn't anyone else have this problem?? -- baracuda ----------------------------------------------------------------------- baracuda9's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1626 View this thread: http://www.excelforum.com/showthread.php?threadid=27675 ...

Outlook works fine after using Outlook Express once...????
Here's an interesting problem : I have set up Outlook and Outlook Express to the same settings for my POP3 account. When I try to send an email from Outlook I get the error : 553 sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1) Even if I try to "Read" before "Sending", which is a common remedy for this problem, Outlook still wont work.. Emails are downloaded fine, but still I cannot send an email. However, if I use Outlook Express to send/receive an email first, Outlook works fine afterwards.... Any ideas ? I thought that Outlook and O.Expre...

Preventing Errors on calculations where query returns no results
Hi everyone I have a query which depending on the users selection may or may-not return any results. The expression below works fine when results are returned but when there are no records it returns '#Error': =IIf([expirydays]<1,"expired " & Abs([expirydays]) & " days ago") Is there a way to modify it to gracefully fall over (ie be blank) when there are no records? I have tried 'IIf([expirydays] is null,null' and 'IIf(len([expirydays])<1,null' but neither worked. Thanks in advance, John Apologies, the below refers to a c...

Help with Quotes
Ok here it is and I'm stuck big time... I'm developing somewhat of a "Fat" quote system that requires a specialized way of doing things. It's the backwards way of doing things but I have to be able to place multiple quantities on each product. Well I want to be able to have products in my catalog to have these multiple Quantities but when I put custom fields in the Product I have no way of transfering this information to the Quotes Product. Looking for some much need sage like enlightment on how to do this... Thanks ...

Need help to sync Outlook with laptop
I am new to Office (2003) Outlook and would like to find a wizard or step by step instructions on setting up my Vista Laptop to sync with my XP Desktop on the same network. I need to keep both my Outlook calendar and e-mail in sync on both computers. I have no idea how to get started. I'd appreciate it if someone would point me in the right direction. -Bill A good start is here: http://www.slipstick.com/outlook/sync.htm -- Bill R MVP "William" <wyum@msn.com> wrote in message news:eoRrgpy4HHA.5796@TK2MSFTNGP05.phx.gbl... >I am new to Office (2003) Outlook and wo...

Help inserting SYMBOLs in VBA
Using Insert -> Symbol, I am able to insert symbols into a worksheet. How can I do this programiatically from VBA? I've tried recording a macro to see but running that same macro inserts a question mark ("?") instead of the symbol I inserted when recording the macro. Thanks -Ed Take some notes when you do it manually--keep track of the codes you're using. This worked ok for me: With ActiveSheet.Range("e1") .Value = Chr(38) .Font.Name = "Wingdings" End With Ed Landau wrote: > > Using Insert -> Symbol, I am ab...

Problem sending emails from Outlook Express Ver6
I am trying to use Outlook Express Ver6 instead of Eudora. I can't seem to send e-mails from it. When I send a test to myself I get the error: The message could not be sent because the server rejected the sender's e-mail address. The sender's e-mail address was (MY ADDRESS) .. Subject 'TEST', Account: 'incoming.verizon.net', Server: 'outgoing.verizon.net', Protocol: SMTP, Server Response: '550 5.7.1 Authentication Required', Port: 25, Secure(SSL): No, Server Error: 550, Error Number: 0x800CCC78 Can anyone suggest what is wrong? Must be something...

Reusable Database connection for Combo Box, list box and other query
I have an app that is database driven and there are many times where I need to make queries out to the database to populate Combo Boxes and List boxes. In my current code, I have to explicitly set the code to populate the combo box based on the result set. Is there a way where I can get a handle to a result set, pass that to a function that parses the resultset and populates the combo box? I know in Java and other OO languages you have an abstraction to the database where you can pass around objects for manipulation but in Excel VB I'm not sure how one would do this. Any idea? On Jul 22...