Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 22nd, 2003, 11:50 AM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The embedded subqueries... that's what I was thinking. However, I am absolutely clueless on how to do it... Parameter queries, I've done through the Design window and just entering my parameters in the Criteria fields of the design grid. But a set of embedded queries sounds like just the ticket!

Have your coffee, my friend.... I know I'm having mine!!

Scott
 
Old November 22nd, 2003, 12:17 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Never mind. Do a union query instead

SELECT "8am-11am" AS Expr1, Count(Documents.DailyID) AS 11_2
FROM Documents
WHERE (((Documents.TimeIn) Between #12/30/1899 8:0:0# And #12/30/1899 10:59:0#) AND ((Documents.DateIn) Between [Enter begin date] And [Enter end date]))
GROUP BY "8am-11am"

UNION

SELECT "11am-2pm " AS Expr1, Count(Documents.DailyID) AS 11_2
FROM Documents
WHERE (((Documents.TimeIn) Between #12/30/1899 11:0:0# And #12/30/1899 13:59:0#) AND ((Documents.DateIn) Between [Enter begin date] And [Enter end date]))
GROUP BY "11am-2pm ";



Sal
 
Old November 22nd, 2003, 12:58 PM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by sal
 Never mind. Do a union query instead

SELECT "8am-11am" AS Expr1, Count(Documents.DailyID) AS 11_2
FROM Documents
WHERE (((Documents.TimeIn) Between #12/30/1899 8:0:0# And #12/30/1899 10:59:0#) AND ((Documents.DateIn) Between [Enter begin date] And [Enter end date]))
GROUP BY "8am-11am"

UNION

SELECT "11am-2pm " AS Expr1, Count(Documents.DailyID) AS 11_2
FROM Documents
WHERE (((Documents.TimeIn) Between #12/30/1899 11:0:0# And #12/30/1899 13:59:0#) AND ((Documents.DateIn) Between [Enter begin date] And [Enter end date]))
GROUP BY "11am-2pm ";
SAL: You are a GOD!! I'll create additional SQL statements like the ones above (which work EXACTLY as intended... of course!) to get all of my required time frames for my boss... Thank god I'm not long for this job... opening my own practice (i'm a licensed massage therapist) next year! Probably will still be here for a while till I get my practice up and booming.

Since you're up and semi-awake (wine will do it every time!!), could I trouble with one, hopefully last, question?

I'm also writing the last of the "required" queries my boss wants... finally!:) This one is to find the total amount of time spent "on task" versus the total amount of "down time" an employee has during his/her working day. This is the SQL I have so far, but it's not quite right:

SELECT Documents.DateIn, [Calculate Operator Total Time spent on Job].[Total Time], [Enter Total Hours Worked] AS [Hours Worked]
FROM Employees, Documents INNER JOIN [Calculate Operator Total Time spent on Job] ON Documents.ID = [Calculate Operator Total Time spent on Job].ID
WHERE (((Documents.DateIn)=[Enter Date]) AND ((Employees.Employee)=[Employee Name?]));

I need to add up the total Time spent on all jobs for a given date for a given employee, then divide that by the number of hours the employee worked. I would like to specify as parameters: Date, Employee Name, Hours Worked (we have really bizarre working hours here.. some people work 7 hour days, others work 12 hours, etc.), and have the query display Date, Employee Name, Hours Worked, Time On Task and "down" time (total time working - hours worked for the day). ANy suggestions??

ST
 
Old November 25th, 2003, 02:19 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, We can pick thi one back next weekend. Hope you got your stuff finished (at least the first part)

Happy thanks giving



Sal
 
Old December 1st, 2003, 03:26 PM
Registered User
 
Join Date: Dec 2003
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've had similar requests. Here's another possible solution that allows you to do everything in one query. Create a custom function that puts your time information into the categories you want. Here's a simple example:

Function TimeCategory(myTime As Variant)
'Function creates categories for crosstab query

    Dim convertTime

    convertTime = Format(myTime, "hhnn")

    Select Case convertTime
        Case Is >= 2100
            TimeCategory = "[6] 9:00 p.m. -- 11:59 p.m."
        Case Is >= 1700
            TimeCategory = "[5] 5:00 p.m. -- 8:59 p.m."
        Case Is >= 1300
            TimeCategory = "[4] 1:00 p.m. -- 4:59 p.m."
        Case Is >= 900
            TimeCategory = "[3] 9:00 a.m. -- 12:59 p.m."
        Case Is >= 500
            TimeCategory = "[2] 5:00 a.m. -- 8:59 p.m."
        Case Is >= 0
            TimeCategory = "[1] 12:00 a.m. -- 4:59 a.m."
        Case Else
            TimeCategory = "Unknown"
    End Select
End Function

You can then create one crosstab query like the following to produce the results:

TRANSFORM Count(ACTIVE_AUDIT_TBL.OPER_NO_IN) AS [Total Items]
SELECT TimeCategory([CHANGED_DATE_DT]) AS TimeSlot
FROM ACTIVE_AUDIT_TBL
GROUP BY TimeCategory([CHANGED_DATE_DT])
PIVOT "Total Items";

It produces the following:

TimeSlot Total Items
[2] 5:00 a.m. -- 8:59 p.m. 307
[3] 9:00 a.m. -- 12:59 p.m. 1541
[4] 1:00 p.m. -- 4:59 p.m. 1575
[5] 5:00 p.m. -- 8:59 p.m. 6


NOTE: I modified the string labels so the time period would sort correctly. You can use any method to sort them in the proper order. This technique also assumes that all time slots would be used. If not, the CrossTab would only display appropriate categories.

Mark M
 
Old December 1st, 2003, 09:28 PM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Mark M
 I've had similar requests. Here's another possible solution that allows you to do everything in one query. Create a custom function that puts your time information into the categories you want. Here's a simple example:

Function TimeCategory(myTime As Variant)
'Function creates categories for crosstab query

    Dim convertTime

    convertTime = Format(myTime, "hhnn")

    Select Case convertTime
        Case Is >= 2100
            TimeCategory = "[6] 9:00 p.m. -- 11:59 p.m."
        Case Is >= 1700
            TimeCategory = "[5] 5:00 p.m. -- 8:59 p.m."
        Case Is >= 1300
            TimeCategory = "[4] 1:00 p.m. -- 4:59 p.m."
        Case Is >= 900
            TimeCategory = "[3] 9:00 a.m. -- 12:59 p.m."
        Case Is >= 500
            TimeCategory = "[2] 5:00 a.m. -- 8:59 p.m."
        Case Is >= 0
            TimeCategory = "[1] 12:00 a.m. -- 4:59 a.m."
        Case Else
            TimeCategory = "Unknown"
    End Select
End Function

You can then create one crosstab query like the following to produce the results:

TRANSFORM Count(ACTIVE_AUDIT_TBL.OPER_NO_IN) AS [Total Items]
SELECT TimeCategory([CHANGED_DATE_DT]) AS TimeSlot
FROM ACTIVE_AUDIT_TBL
GROUP BY TimeCategory([CHANGED_DATE_DT])
PIVOT "Total Items";

It produces the following:

TimeSlot Total Items
[2] 5:00 a.m. -- 8:59 p.m. 307
[3] 9:00 a.m. -- 12:59 p.m. 1541
[4] 1:00 p.m. -- 4:59 p.m. 1575
[5] 5:00 p.m. -- 8:59 p.m. 6


NOTE: I modified the string labels so the time period would sort correctly. You can use any method to sort them in the proper order. This technique also assumes that all time slots would be used. If not, the CrossTab would only display appropriate categories.

Mark M
Mark: Your suggestion sounds great, but as I'm nearly brain-dead when it comes to VB, etc... I was wondering if you could give a few more clues...

My table from which the information is being drawn is named DOCUMENTS. It contains the following fields relevant to this particular situation:

ID=autonum
DailyID=number
DateIn=Date()
TimeIn=Time()

If I write the function you've suggested, do I put it in the MODULES section of the DB? Also, how would I modify the SQL for the Cross-tab query to reflect the fields and table I'm drawing from. As it stands now, I'm using Sal's union query example (see prior posts...) and it works, but the data is not sorted the way I need it to (by time frames: 8a-1059a, 11a-159p, 2p-459p, 5p-759p, 8p-1159p, 12a-159a, 2a-459a, 5a-759a. Also, in the function you posted, how would I modify the CASE IS statement? I'm not sure of it's purpose, though it looks like you are specifying hours in 24-hour time. Any help is appreciated.

Thanks,
Scott
 
Old December 1st, 2003, 09:57 PM
Registered User
 
Join Date: Dec 2003
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Scott:

Yes. You will need to create a new module that contains the new function called TimeCategory. Cut-n-paste the code into the module area and save it with the name GENERAL. I am assuming you are creating this database in Access97 or higher.

The line convertTime = Format(myTime, "hhnn") converts whatever time you pass to the function to an integer value from 0 to 2359 which allows you evaluate in the case statement. 2100 equals the 24-hour clock of 9 p.m. or higher. If you wanted one hour intervals you would have 2300, 2200, 2100, 2000, etc. For example, you stated you wanted a group that equaled 8a-1059a, here are the case statements for it:

         case is >= 1100
            TimeCategory = "[3] 11:00 a.m. -- 1:59 p.m."
         case is >= 800
            TimeCategory = "[2] 8:00 a.m. -- 10:59 a.m."

The Case Is statement is merely looking to see if the condition is true. If so, it drops into the statement and returns the string value assigned to TimeCategory. You go most restrictive to least restrictive so the values will fall through to the bottom as they get smaller (or earlier in the day). You modify these levels to whatever increments you want and adjust the labels returned. In your example, it is every 3 hours. In my example, it was every 4 hours. Adding the [n] at the beginning of the label allows you to sort on it in the proper sequence. You would obviously replace the `n` with the appropriate number sequence based on the total groups.

The ELSE statement lets you trap for any values that may not fall within your case statements. This could happen if there is no time assigned within a record or it falls outside the range of the case statements you created. In my example, I covered all 24-hours but you could set it up to do only a specific period and then dump anything outside this range into your ELSE statement (along with invalid time entries).

Your crosstab query would look like the following using the fields you described:

TRANSFORM Count(documents.DailyID) AS [Total Items]
SELECT TimeCategory([TimeIn]) AS TimeSlot
FROM DOCUMENTS
WHERE Documents.[DateIn] = [ENTER YOUR DATE HERE]
GROUP BY TimeCategory([TimeIn])
PIVOT "Total Items";

The Count(documents.DailyID) statement simply counts records that fall within the GROUP and WHERE condition ranges. The [ENTER YOUR DATE HERE] is a parameter that will prompt you for a date when you run the query. It's a nice way to run the same query over-and-over and just pass a new date to it each time. You can also hard-code a date into the statment (e.g. WHERE Documents.[DateIn] = #12/1/03#).

The GROUP BY statement along with the SELECT statement take the time input and converts it according to the new function -- TimeCategory. The PIVOT statement says to build a column heading for all the values associated with the value "TOTAL ITEMS" which in this case will be every value so you will get one column with all your counts under it.

One note -- I am assuming that your TimeIn field is a true Access time value. If it's embedded in a date field and doesn't appear to be recognized by Access, you can substitute the statement TimeCategory(Timevalue([TimeIn])) which should identify only the time portion of the field.

Hope this helps! Let me know if you have any other questions.

- Mark M -
 
Old December 3rd, 2003, 03:49 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What I was thinking is to do a mod on the function that adds the dailyID so that it also adds the time frame into a field so that this could be used instead of a crosstab query. This will also improve speed on the database.

TimeFrame
8:00 - 11:00
11:00 - 2:00

Those would be the values inserted in the table. Then all you have to do is create a sum query or something similar and voila.

I will check on this tonight. I still have your db at home. I had it here at work, but my usb drive just took a dump on me :(:(:(



Sal
 
Old December 6th, 2003, 01:39 PM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Mark M
Your crosstab query would look like the following using the fields you described:

TRANSFORM Count(documents.DailyID) AS [Total Items]
SELECT TimeCategory([TimeIn]) AS TimeSlot
FROM DOCUMENTS
WHERE Documents.[DateIn] = Between [ENTER BEGINNING DATE] AND [ENTER ENDING DATE]
GROUP BY TimeCategory([TimeIn])
PIVOT "Total Items";
Mark: I've used the code you suggested, and written the CrossTab as above, yet I get an error: Does not recognize [Enter Beginning Date] as a valid Field or Expression.

I definitely need to be able to prompt the user for parameters BETWEEN[Enter Beginning Date] AND [Enter Ending Date]

Everything else looks and works great!

Any suggestions??
Scott
 
Old December 7th, 2003, 09:24 PM
Registered User
 
Join Date: Dec 2003
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes. This is a by-product of the fact of how the Jet database processes crosstab queries. I forgot it doesn't like the standard way to declare parameters. Modify the query by adding the following line to the first line of the query:

PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;

This line defines the two parameters you want to use in the query and tells the Jet engine the parameter type (DATETIME). The final query should look like the following:

PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
TRANSFORM Count(DOCUMENTS.DailyID) AS [Total Items]
SELECT TimeCat([TimeIn]) AS TimeSlot
FROM DOCUMENTS
WHERE (((DOCUMENTS.DateIn) Between [Beginning Date] And [Ending Date]))
GROUP BY TimeCat([TimeIn])
PIVOT "Total Items";

I tested this against my test database and the error you reported is no longer displayed and the query prompts the user for the appropriate information. Remember, if a user enters a date range over 24 hours, the crosstab query will combine results for the multiple days into the same group.

Good luck!

- Mark M -





Similar Threads
Thread Thread Starter Forum Replies Last Post
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
how to make a query from an existing query raport SQL Language 3 November 13th, 2006 08:59 PM
I solved insert query.now see this Update Query. amit_mande@yahoo.com VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM
Syntax error in query. Incomplete query clause. dispickle ADO.NET 3 April 16th, 2004 01:04 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.