 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008Count Query
I'm a little lost and hoping someone can show me the light at the end of the tunnel. I have 2 table 1- General Info 2- Details They have a common thread which is the ProjNo I need to make a simple count based on the dtDelivery field from the Details table for those ProjNo have Type="A" in the General Info Table. I need a break-down count #early #on time #late #total always based on the current date vs. the dtDelivery. Thank you QB SELECT Count(Details.dtDelivery) AS TheTotals, DCount("[dtDelivery]","[Details]","dtDelivery =" & Date()) AS OnTime, DCount("[dtDelivery]","[Details]","dtDelivery <" & Date()) AS Late, DCount("[dtDelivery]","[Details]","dtDelivery >" & Date()) AS Early, [Details].Service_Month FROM [Details], [General Info] HAVING [General Info].TYPE = "A" AND [General Info].ProjNo = [Details].ProjNo; Hopefully I got the above statement right. You may have to mess with it some to get what you want. Also the results will be wrong if dtDelivery includes times in it.  SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > I'm a little lost and hoping someone can show me the light at the end of the > tunnel. [quoted text clipped - 18 lines] > > Thank you QB Interesting. I was trying, with no success, using Count(). Could you enlighten me why my method is wrong and DCount is the way to go (I actually would like to understand and learn something today). No worries, the dt.... field only has a date, no time. Thank you so very much for setting me straight!!!!!!!!!!!! I would never have come up with that SQL synthax if my life had depened on it. QB > SELECT Count(Details.dtDelivery) AS TheTotals, > DCount("[dtDelivery]","[Details]","dtDelivery =" & Date()) AS OnTime, [quoted text clipped - 31 lines] > > > > Thank you QB Count would work on any one of the requirements; however, you would need individual queries for the others as they all have different criteria. I don't think that an OR clause would work. Actually as I'm rethinking it, the SQL statement might not work correctly as is because the criteria doesn't apply to the DCount statements. You may need to first create a query that gathers the "A" records then base something like below on this initial query. Or you might be able to add the criteria to all the DCount statements but I don't see how to do that cleanly. SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > Interesting. I was trying, with no success, using Count(). Could you > enlighten me why my method is wrong and DCount is the way to go (I actually [quoted text clipped - 42 lines] > > > > > > Thank you QB 2 little issues/questions, What is the [Details].Service_Month for? The TheTotals is not giving a total, it always =0? Thank you, QB > SELECT Count(Details.dtDelivery) AS TheTotals, > DCount("[dtDelivery]","[Details]","dtDelivery =" & Date()) AS OnTime, [quoted text clipped - 31 lines] > > > > Thank you QB My bad. I create the query based on one of my tables then changed the field and table names to yours. I must have missed one. You could get rid of that line. Make sure to remove the comma behind AS Early. SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > 2 little issues/questions, > [quoted text clipped - 40 lines] > > > > > > Thank you QB I made a mistake with my initial requirement, should I say it was modified by my boss. Instead of comparing the dtDelivery to the current date I need to compare it to the maximum date for the record in another table. I tried using the Dmax() but I keep getting an error that is chinesse to me. Trying to run an aggregate query that is not part of...? for instance I tried DCount("[dtDelivery]","[Details]","dtDelivery =" & DMax("[DateApproP]","[tbl_dtDeliveryP]","[PlanIngId]=" & [PlanIngId] & " AND [Approved]=True")) compare the approve (Approved=true) planned date against the actual date and build the summary table based on the results. Could you point out the problem. Thank you QB > SELECT Count(Details.dtDelivery) AS TheTotals, > DCount("[dtDelivery]","[Details]","dtDelivery =" & Date()) AS OnTime, [quoted text clipped - 31 lines] > > > > Thank you QB I was wondering about that. If you used Date() all records would eventually show Early. I really can't get my head around the problem now without some actual data and tables. It's a little too abstract. I'm thinking that you will need subqueries to pull the data out now, but can't visualize it. Sorry. SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > I made a mistake with my initial requirement, should I say it was modified by > my boss. [quoted text clipped - 53 lines] > > > > > > Thank you QB I would use soemthing like the following. SELECT Count(*) , Abs(Sum(DtDelivery-Date()>0)) as Early , Abs(Sum(DtDelivery-Date() = 0) as OnTime , Abs(Sum(DtDelivery-Date() < 0) as Late FROM [General Info] INNER JOIN Details On [General Info].ProjNo= Details.ProjNo WHERE [General Info].Type = "A" Now I must say that this makes little sense to me. Since if something was on time today then tomorrow it will be late based on the algorithm you are using. I would think that more sensible would be to calculate dtActuallyDelivered vs dtDeliveryScheduled and if the actual date delivered is blank then you might want to replace it with the current date for purposes of the calculation John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > I'm a little lost and hoping someone can show me the light at the end of the > tunnel. [quoted text clipped - 18 lines] > > Thank you QB |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.