Microsoft Access Forum

Access 2007-2016
Access 2000-2003
Access Seminars

TechHelp Support
Tips & Tricks
Access Forum

Course Index
Topic Glossary
Insider Circle

Home News Tips Glossary Templates Forums Help Logon Order Contact Us

Forums

Microsoft Access Forum
By Richard Rost

This forum is for the discussion of Microsoft Access.

Access is my personal specialty. Have questions? Comments? Want to discuss how to do something? Post it here. Also, I get a LOT of questions sent to me in Email from people around the world. I'll post the interesting ones in here. Feel free to comment on them.

Click Here To Subscribe to this forum and receive an email update whenever new posts are added, just scroll down to the bottom of this page and enter your email address in the comment form.

Permanent Link
Keywords: microsoft access forum

Post New Topic

Read Reduce height of header by Hugh C @ 11/9/2018

Read Is there a Restricted number of linked DB by Dottie A @ 11/8/2018

Read Booking calendar by Vesna D @ 11/8/2018

Read Pasting from Excel by Jeremiah T @ 11/7/2018

Read conditionally Format continuous subform by Gina D @ 11/5/2018

Read multi select list boxes by RT @ 11/5/2018

Read Imaging Seminar by HUGO M @ 11/4/2018

Read divide two querries then group by department by jannette j @ 11/2/2018

I am looking for the rate change between new accounts and all accounts, I have both queries listed below. I need to divide Emergeny Work Orders by AllWorkorders, take that percentage and group by department in the same query. Thanks
Expand|Select|Wrap|Line Numbers
The first query:


SELECT maximoreport.lfc_assetdept, 
       Sum(LEFT([actuallaborhours], Instr(1, [actuallaborhours], ':') - 1) + ( 
           RIGHT( 
           [actuallaborhours], Instr(1, [actuallaborhours], ":") - 1) ) / 60) AS 
       LaborHours 
FROM   maximoreport 
WHERE  ( ( (( [actualstartdate] - [reporteddate] )) < 3.5 ) 
         AND ( ( maximoreport.[estimated labor hours] ) = "00:00" ) 
         AND ( ( maximoreport.worktype ) = "cm" 
                OR ( maximoreport.worktype ) = "em" 
                OR ( maximoreport.worktype ) = "mmnro" 
                OR ( maximoreport.worktype ) = "mmroi" 
                OR ( maximoreport.worktype ) = "pmcm" 
                OR ( maximoreport.worktype ) = "pmins" 
                OR ( maximoreport.worktype ) = "pmor" 
                OR ( maximoreport.worktype ) = "pmpdm" 
                OR ( maximoreport.worktype ) = "pmreg" 
                OR ( maximoreport.worktype ) = "pmrt" ) 
         AND ( ( maximoreport.actualstartdate ) >= 
               Dateadd("h", -1, [enter the start date]) 
               AND ( maximoreport.actualstartdate ) < 
                   Dateadd("h", 23, [enter the end date]) ) ) 
        OR ( ( ( maximoreport.worktype ) = "em" ) 
             AND ( ( maximoreport.actualstartdate ) >= 
                   Dateadd("h", -1, [enter the start date]) 
                   AND ( maximoreport.actualstartdate ) < 
                       Dateadd("h", 23, [enter the end date]) ) ) 
GROUP  BY maximoreport.lfc_assetdept 
ORDER  BY maximoreport.lfc_assetdept;

Query 2:
Expand|Select|Wrap|Line Numbers


SELECT maximoreport.lfc_assetdept, 
       Sum(LEFT([actuallaborhours], Instr(1, [actuallaborhours], ':') - 1) + ( 
           RIGHT( 
           [actuallaborhours], Instr(1, [actuallaborhours], ":") - 1) ) / 60) AS 
       LaborHours 
FROM   maximoreport 
WHERE  ( ( ( maximoreport.worktype ) = "cm" 
            OR ( maximoreport.worktype ) = "em" 
            OR ( maximoreport.worktype ) = "mmnro" 
            OR ( maximoreport.worktype ) = "mmroi" 
            OR ( maximoreport.worktype ) = "pmcm" 
            OR ( maximoreport.worktype ) = "pmins" 
            OR ( maximoreport.worktype ) = "pmor" 
            OR ( maximoreport.worktype ) = "pmpdm" 
            OR ( maximoreport.worktype ) = "pmreg" 
            OR ( maximoreport.worktype ) = "pmrt" ) 
         AND ( ( maximoreport.actualstartdate ) >= 
               Dateadd("h", -1, [enter the start date]) 
               AND ( maximoreport.actualstartdate ) < 
                   Dateadd("h", 23, [enter the end date]) ) ) 
GROUP  BY maximoreport.lfc_assetdept 
ORDER  BY maximoreport.lfc_assetdept;

Show Just This Thread Post Reply

Read time sum by jannette j @ 10/30/2018

I have a query where i am trying to calculate the total hours worked on an order.But when i run it i get an error This expression is typed incorectly or is too complexed to be evualated does anyone know how i can do a query that adds hours.


SELECT ( Sum(Datepart("h", [actuallaborhours]) * 3600 + 
                      Datepart("n", [actuallaborhours]) * 60) )   AS 
       TotalSeconds, 
       Round([totalseconds] / 3600, 0) & ':' & RIGHT("00" & 
                                               Round(( ( totalseconds / 
                                                         3600 ) - Round( 
                                                       totalseconds / 3600 
                                                       , 0) ) * 
                                                     60, 0 
                                               ), 2) AS TotalTime, 
       [maximo report].worktype, 
       [maximo report].actualstartdatefrom 
       [Maximo Report] 
WHERE  ( ( ( [maximo report].worktype ) = "cm" 
            OR ( [maximo report].worktype ) = "em" 
            OR ( [maximo report].worktype ) = "mmnro" 
            OR ( [maximo report].worktype ) = "mmroi" 
            OR ( [maximo report].worktype ) = "pmcm" 
            OR ( [maximo report].worktype ) = "pmins" 
            OR ( [maximo report].worktype ) = "pmor" 
            OR ( [maximo report].worktype ) = "pmpdm" 
            OR ( [maximo report].worktype ) = "pmreg" 
            OR ( [maximo report].worktype ) = "pmrt" ) 
         AND ( ( [maximo report].actualstartdate ) >= 
               Dateadd("h", -1, [enter the start date]) 
               AND ( [maximo report].actualstartdate ) < 
                   Dateadd("h", 23, [enter the end date]) ) );

Reply from Alex Hedley:

Try splitting the query into simpler ones, sometimes access needs the calculation done first before it can then use it as a value somewhere else.

Show Just This Thread Post Reply

Read Decimels by Gerald S @ 10/30/2018

Read sum short time field by jannette j @ 10/30/2018

Read insert values from multiple queries by jannette j @ 10/29/2018

Read Search Box by Sara P @ 10/29/2018

Read Edit Fields by lauro c @ 10/28/2018

Read multi select list boxes by Richard R @ 10/27/2018

Read uDialog Developer 11 by James G @ 10/27/2018

Read Navigation form by Piet n @ 10/24/2018

Read Video Embed by Richard L @ 10/18/2018

Read New Class by Damien N @ 10/16/2018

Read Imaging Seminar by Frits d @ 10/15/2018

Show Older Posts Expand All Topics

Post Your Comments or Subscribe
If you would like to be notified of new posts on this forum,
just enter your email address below. It will be kept private.

If you just want to subscribe to get email updates when this forum is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
Your Name:	Required
Your Email:	NOT Public
Subject:
Comments:
	Sorry about this step. It's just to keep the spam bots away:
Verify:	What is 9+7:

	Notify me when the this forum is updated
	Remember Me for my next comments


Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead. I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be. As always, I promise to never give away your personal information to anyone else, ever. NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard

You may want to read these articles from the 599CD News:

11/20/2018	Microsoft Access Developer 12
8/25/2018	NEW: Access Dev 9, 10, 11
8/25/2018	Microsoft Access Developer 11
8/25/2018	Microsoft Access Developer 10
8/25/2018	Microsoft Access Developer 9
8/23/2018	Access Dev 11 is ONLINE
8/17/2018	Access Dev 10 is ONLINE
8/15/2018	Access Tip: Search Form
8/15/2018	Access Tip: Locked v. Enabled
8/15/2018	Access Dev 9 is ONLINE

Learn

Access	index
Excel	index
Word	index
Windows	index
PowerPoint	index
Photoshop	index
Visual Basic	index
ASP	index
Seminars
More...

Customers

Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email

Info

Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus

Help

Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services

About Us

Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List

Order

Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs

Contact

Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey