Hello!
I have a `job` database, where users add a job (or record) with a start date and end date.
I need to run a query that will show me the number of uncompleted jobs by user - which I'm taking to be a date greater than todays current date. I'm going to use this as a basis for a chart on a form.
I have a staff name field, autonumberfield, start date and end date field, along with an expression that works out the DateDiff between the two, in days.
I can run a query to show me the total number of jobs per user, by using a Group By on the Staff Name and a Count on the Autonumber field, but what expression or query do I need to show me the total number of uncompleted jobs?
Cheers!
Neil
6  2498 
If I'm following you correctly, in the grid's Criteria box for the end date field
> Date()
Linq ;0)>
If I'm following you correctly, in the grid's Criteria box for the end date field
> Date()
Linq ;0)>
Hi Linq,
No, I can't get that to work.
What i really what is a count of the total number of records by user, but showing only uncompleted jobs - I`m not sure exactly how I define an `uncompleted job`. perhaps I should just be assuming that it is one with no end date rather than an assumed future date?
Do you see what I mean?
Cheers
Neil
Hi Linq,
What am I taking about - of course it works!
Sorry about that - I have just realised that I can't run a query on a future date greater than today when there are none in my database.
Brain a bit slow on Thursdays. Mondays. Tuesdays. Wednesdays. Friday not that hot.
Cheers!
Neil
Ah! But...but...
This now no longer groups my results by user into one record per user, as its counting the number of records per future date:
Staff Name CountofID EndDate
Jo Bloggs 1 14.02.2008
Jo Bloggs 1 21.02.2008
John Smith 1 06.05.2008
John Smith 2 07.05.2008
When what I need for my chart is this:
Staff Name Total Number of Records where >Date()
Jo Bloggs 2
John Smith 3
So they only have one column in my chart, and its not the length of the Queen Mary.
Cheers
Neil
No I've done it!
I just ran another query on the results of the one above, then Sum-med the CountOfID Field.
Perhaps my brain is working today...albeit slowly.
Thanks!
Neil
Glad you got it figured out!
Linq ;0)> Sign in to post your reply or Sign up for a free account.
Similar topics
by: Spark |
last post by:
Hi,
Situation:
Need a query to return number of ticket records by month of open in a
log table where the ticket open record is older than 24 hours then the
ticket pending or ticket closed...
|
by: Good Man |
last post by:
Hi all
Well, I didn't want to have to bring this question to someone else to
answer, but I am truly flummoxed and could use some help. It all boils
down to trying to find the number of jobs...
|
by: Lauren Quantrell |
last post by:
Can anyone post any code that will count the number of elapsed days in
a year? I want code that tells me 1/1/2004 is day 1, 2/1/2004 is day
32, etc.
I plan to use this in a calendar that caculates...
|
by: wz |
last post by:
Hi,
I'm new to access sql, and have a simple question.
I have a date field, named log_dt, and I'm trying to count how many
hours each record is from starting time of it's own month. But the 5
in...
|
by: Kevin |
last post by:
Group By - Client
Sum - Past:IIF(DateDiff("d",,Date())>30,,0)
Sum - Current:IIF(DateDiff("d",,Date())<31,,0)
Works for positive numbers, but if InvoiceNetTotal = ($4.00) and
InvoiceNetTotal =...
|
by: Sjef ten Koppel |
last post by:
Hi,
I've a small problem. I have a table in which one column is date. I want to
count the records for statiscs in a temptable grouped by months lets say 12
months back.
e.g.
month 1 counts 164...
|
by: BUmed |
last post by:
I want to set up a Query to show the diffence between 2 dates and the output to be in months and weeks. So the child is 15months and 2weeks (15/2). I have been try to modify DateDiff Age:...
|
by: StevoNZ |
last post by:
Hi,
I am a little stuck with a query I am trying to build. Attemting to calculate the delivery time for a material.
I have managed to utilise the DateDiff function, but have some additional...
|
by: Career Point |
last post by:
HCL, Aricent, Infosys and many more companies hiring fresh &
experienced engineers ,
FIND UR DREAM JOBS AT GIVEN BELOW LINKS: - http://careerguide.50webs.com
IT JOBS:- ...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| | |
