Query with dates calculation

[Tami]

I have a query that lists just a job# and a date pulling from a table in
which I put every day that an employee works on that job (Labor Expense
table). Some days there are more than one employee working on the same job,
and some days no one is working on that job.

I want to get a total number of days that the job was worked on. I can't
just take the last date worked minus the first day because it includes days
not worked. And I can't take the number of records because it includes more
than one employee.

How would I create the query to just give me a count of different dates?

Thanks in advance for your help.

 

[Jerry Whittle]

Select Count([DateField])
From [Labor Expense]
Group By [DateField] ;

This should work IF it's just a date with no time in it.

 

[geebee]

hi,

What a wonderful opportunity to introduce you to Domain Aggregate Functions...

You could try something like...
=DCount("[field]","table1","[table column] = [fieldname]")

Hope this helps
geebee

 

[Tami]

That tells me that two employees worked one day, three another and one on
another, but how do I now tell it to give me the total of the days?
--
Tami

Select Count([DateField])
From [Labor Expense]
Group By [DateField] ;

This should work IF it's just a date with no time in it.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query that lists just a job# and a date pulling from a table in
which I put every day that an employee works on that job (Labor Expense
table). Some days there are more than one employee working on the same job,
and some days no one is working on that job.

I want to get a total number of days that the job was worked on. I can't
just take the last date worked minus the first day because it includes days
not worked. And I can't take the number of records because it includes more
than one employee.

How would I create the query to just give me a count of different dates?

Thanks in advance for your help.

 

[Dale Fye]

Sorry geebee, that isn't going to work. What she wants is to count the
number of distinct days that at least one person was on the job, not get a
total number of mandays worked.

Tami, try a nested subquery:

SELECT Count(D.Days) as DistinctDays
FROM (SELECT Distinct [Date_Field] as Days
FROM your_Table
WHERE [JOB_No] = [Job Number]) as D

Inside the ( ) is a subquery that selects the set of distinct days (only 1
record per day) that someone is on the job you are looking for. When you
run this query, you will be asked for the Job Number.

HTH
Dale

hi,

What a wonderful opportunity to introduce you to Domain Aggregate
Functions...

You could try something like...
=DCount("[field]","table1","[table column] = [fieldname]")

Hope this helps
geebee

I have a query that lists just a job# and a date pulling from a table in
which I put every day that an employee works on that job (Labor Expense
table). Some days there are more than one employee working on the same
job,
and some days no one is working on that job.

I want to get a total number of days that the job was worked on. I can't
just take the last date worked minus the first day because it includes
days
not worked. And I can't take the number of records because it includes
more
than one employee.

How would I create the query to just give me a count of different dates?

Thanks in advance for your help.

 

[Tami]

Dale, Thanks for your reply, but I am teaching myself this program & I am
still learning. Please bear with me.

My table is called Labor Exp
My fields are called Date & Job#

I don't understand the D.Days in your SQL.

Sorry again for my ignorance. Thanks again for your help.
--
Tami

Sorry geebee, that isn't going to work. What she wants is to count the
number of distinct days that at least one person was on the job, not get a
total number of mandays worked.

Tami, try a nested subquery:

SELECT Count(D.Days) as DistinctDays
FROM (SELECT Distinct [Date_Field] as Days
FROM your_Table
WHERE [JOB_No] = [Job Number]) as D

Inside the ( ) is a subquery that selects the set of distinct days (only 1
record per day) that someone is on the job you are looking for. When you
run this query, you will be asked for the Job Number.

HTH
Dale

hi,

What a wonderful opportunity to introduce you to Domain Aggregate
Functions...

You could try something like...
=DCount("[field]","table1","[table column] = [fieldname]")

Hope this helps
geebee

I have a query that lists just a job# and a date pulling from a table in
which I put every day that an employee works on that job (Labor Expense
table). Some days there are more than one employee working on the same
job,
and some days no one is working on that job.

I want to get a total number of days that the job was worked on. I can't
just take the last date worked minus the first day because it includes
days
not worked. And I can't take the number of records because it includes
more
than one employee.

How would I create the query to just give me a count of different dates?

Thanks in advance for your help.

 

[Tami]

I entered this and got a syntax error in the FROM clause.

SELECT Count (D.Days) as DistinctDays
FROM (SELECT Distinct [Labor Exp].Date as Days
FROM [Labor Exp]
WHERE ([Labor Exp].Job# = [Job#]) as D
--
Tami

Sorry geebee, that isn't going to work. What she wants is to count the
number of distinct days that at least one person was on the job, not get a
total number of mandays worked.

Tami, try a nested subquery:

SELECT Count(D.Days) as DistinctDays
FROM (SELECT Distinct [Date_Field] as Days
FROM your_Table
WHERE [JOB_No] = [Job Number]) as D

Inside the ( ) is a subquery that selects the set of distinct days (only 1
record per day) that someone is on the job you are looking for. When you
run this query, you will be asked for the Job Number.

HTH
Dale

hi,

What a wonderful opportunity to introduce you to Domain Aggregate
Functions...

You could try something like...
=DCount("[field]","table1","[table column] = [fieldname]")

Hope this helps
geebee

I have a query that lists just a job# and a date pulling from a table in
which I put every day that an employee works on that job (Labor Expense
table). Some days there are more than one employee working on the same
job,
and some days no one is working on that job.

I want to get a total number of days that the job was worked on. I can't
just take the last date worked minus the first day because it includes
days
not worked. And I can't take the number of records because it includes
more
than one employee.

How would I create the query to just give me a count of different dates?

Thanks in advance for your help.

 

[Tami]

I realized that I entered a parenthesis in the wrong place and changed it to.

SELECT Count (D.Days) as DistinctDays
FROM (SELECT Distinct [Labor Exp].Date as Days
FROM [Labor Exp]
WHERE [Labor Exp].Job# = [Job#]) as D

Now when I try to run it, I get the msg Syntax error (missing oporator) in
query expression '[Labor Exp].Job# = [Job#]'.
--
Tami

I entered this and got a syntax error in the FROM clause.

SELECT Count (D.Days) as DistinctDays
FROM (SELECT Distinct [Labor Exp].Date as Days
FROM [Labor Exp]
WHERE ([Labor Exp].Job# = [Job#]) as D
--
Tami

Sorry geebee, that isn't going to work. What she wants is to count the
number of distinct days that at least one person was on the job, not get a
total number of mandays worked.

Tami, try a nested subquery:

SELECT Count(D.Days) as DistinctDays
FROM (SELECT Distinct [Date_Field] as Days
FROM your_Table
WHERE [JOB_No] = [Job Number]) as D

Inside the ( ) is a subquery that selects the set of distinct days (only 1
record per day) that someone is on the job you are looking for. When you
run this query, you will be asked for the Job Number.

HTH
Dale

hi,

What a wonderful opportunity to introduce you to Domain Aggregate
Functions...

You could try something like...
=DCount("[field]","table1","[table column] = [fieldname]")

Hope this helps
geebee

:

I have a query that lists just a job# and a date pulling from a table in
which I put every day that an employee works on that job (Labor Expense
table). Some days there are more than one employee working on the same
job,
and some days no one is working on that job.

I want to get a total number of days that the job was worked on. I can't
just take the last date worked minus the first day because it includes
days
not worked. And I can't take the number of records because it includes
more
than one employee.

How would I create the query to just give me a count of different dates?

Thanks in advance for your help.

 

[Tami]

I figured out a way to do it.

SELECT Distinct [Labor Exp].Date
FROM [Labor Exp]
GROUP BY [Labor Exp].[Job#], [Labor Exp].Date
HAVING ((([Labor Exp].[Job#])=[What Job #]));

This returns the total days worked on the Job. Thanks for everybody's help.