Count Query

[Question Boy]

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

 

[Jerry Whittle]

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.

 

[Question Boy]

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,
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.
--
Jerry 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.

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

 

[Question Boy]

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,
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.
--
Jerry 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.

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

 

[Question Boy]

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,
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.
--
Jerry 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.

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

 

[Jerry Whittle]

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

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,
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.
--
Jerry 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.

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

 

[Jerry Whittle]

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.
--
Jerry 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
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,
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.
--
Jerry 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.

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

 

[Jerry Whittle]

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.
--
Jerry 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.

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,
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.
--
Jerry 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.

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

 

[John Spencer]

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