Daily County of date records

[sonofroy]

I have never tried counts for dates so any instruction would be greatly
appreciated. I have a query [qry1] with [Vehicle Number]'s and a field on
which each vehicle is checked out [CheckOut] using Date and Time. I am trying
to figure out how I can have a query that will total how many times a day
each Vehicle has been Checked out so I can create a chart out of it.

Example I have one of the 12 vehicles that has been checked out 3 times in
one day.

Much thanks for any help

 

[vanderghast]

Make a total query (Click on the button with the Summation symbol to have a
new line, Total, in the grid). Bring the vehicles field in the grid, keep
the proposed GROUP BY. Next, in the first line, type
DATE(dateTimeFieldNameHere), and again, keep the proposed GROUP BY. In a
third column, type COUNT(*) and change the GROUP BY to EXPRESSION.


Vanderghast, Access MVP

 

[John W. Vinson]

Make a total query (Click on the button with the Summation symbol to have a
new line, Total, in the grid). Bring the vehicles field in the grid, keep
the proposed GROUP BY. Next, in the first line, type
DATE(dateTimeFieldNameHere), and again, keep the proposed GROUP BY. In a
third column, type COUNT(*) and change the GROUP BY to EXPRESSION.

Vanderghast, I think that should be DateValue not Date, shouldn't it?

 

[vanderghast]

Definitively, thanks to point it out.

Vanderghast, Access MVP

 

[sonofroy]

Thanks guys but I just found that I also need to show the summary of all the
vehicles checked out each day too. What would I need to change on the query
to do that?

Thanks again!

Definitively, thanks to point it out.

Vanderghast, Access MVP

 

[sonofroy]

I would also like to be able to put in a here a between statement so the end
use can type a beginning date and an ending date depending on how many days
they need focus on for # of vehicles checked out. I tried as crter of the
DATEVALUE Expression but that lead to an error

Thanks guys but I just found that I also need to show the summary of all the
vehicles checked out each day too. What would I need to change on the query
to do that?

Thanks again!

 

[vanderghast]

SELECT vehicleID, DATEVALUE(dateTimeField), COUNT(*)
FROM tableNameHere
WHERE dateTimeField BETWEEN [Enter Starting date: ] AND [Enter Final
date: ]
GROUP BY vehicleID, DATEVALUE(dateTimeField)


(in SQL view) should do. Replace the table name (one place) and the fields
vehicleID and dateTimeField (two place, each) with your table name and field
names.


Vanderghast, Access MVP

 

[sonofroy]

I get a Syntax error on this line SELECT vehicleID, DATEVALUE(dateTimeField),
COUNT(*)

SELECT vehicleID, DATEVALUE(dateTimeField), COUNT(*)
FROM tableNameHere
WHERE dateTimeField BETWEEN [Enter Starting date: ] AND [Enter Final
date: ]
GROUP BY vehicleID, DATEVALUE(dateTimeField)


(in SQL view) should do. Replace the table name (one place) and the fields
vehicleID and dateTimeField (two place, each) with your table name and field
names.


Vanderghast, Access MVP

I would also like to be able to put in a here a between statement so the
end
use can type a beginning date and an ending date depending on how many
days
they need focus on for # of vehicles checked out. I tried as crter of the
DATEVALUE Expression but that lead to an error

 

[sonofroy]

I still can not figure out how to show the summary of all the
vehicles checked out each day too. What would I need to change on the query
to do that?

Thanks guys but I just found that I also need to show the summary of all the
vehicles checked out each day too. What would I need to change on the query
to do that?

Thanks again!

 

[vanderghast]

In SQL view:


SELECT DISTINCT vehicleID
FROM tableName
WHERE dateTimeField BETWEEN [enter starting date: ] AND [enter ending
date: ]


will produce a list of all vehicles having the value of the supplied
dateTimeField between the two given dates (supplied as parameter).


Vanderghast, Access MVP

 

[vanderghast]

Are you in SQL view or in design view in the Access Query Designer? You
have to be in SQL view, with nothing else than
-----------------------------
SELECT vehicleID, DATEVALUE(dateTimeField), COUNT(*)
FROM tableNameHere
WHERE dateTimeField BETWEEN [Enter Starting date: ] AND [Enter Final
date: ]
GROUP BY vehicleID, DATEVALUE(dateTimeField)
----------------------------
in it (and having changed the fields and table names). ALL CAP WORDS are to
be typed as they are.


Vanderghast, Access MVP

I get a Syntax error on this line SELECT vehicleID,
DATEVALUE(dateTimeField),
COUNT(*)

SELECT vehicleID, DATEVALUE(dateTimeField), COUNT(*)
FROM tableNameHere
WHERE dateTimeField BETWEEN [Enter Starting date: ] AND [Enter Final
date: ]
GROUP BY vehicleID, DATEVALUE(dateTimeField)


(in SQL view) should do. Replace the table name (one place) and the
fields
vehicleID and dateTimeField (two place, each) with your table name and
field
names.


Vanderghast, Access MVP

I would also like to be able to put in a here a between statement so the
end
use can type a beginning date and an ending date depending on how many
days
they need focus on for # of vehicles checked out. I tried as crter of
the
DATEVALUE Expression but that lead to an error

:

Thanks guys but I just found that I also need to show the summary of
all
the
vehicles checked out each day too. What would I need to change on the
query
to do that?

Thanks again!

:

Definitively, thanks to point it out.

Vanderghast, Access MVP


message
On Wed, 19 Aug 2009 15:12:35 -0400, "vanderghast"
<vanderghast@com>
wrote:

Make a total query (Click on the button with the Summation symbol
to
have
a
new line, Total, in the grid). Bring the vehicles field in the
grid,
keep
the proposed GROUP BY. Next, in the first line, type
DATE(dateTimeFieldNameHere), and again, keep the proposed GROUP
BY.
In a
third column, type COUNT(*) and change the GROUP BY to
EXPRESSION.

Vanderghast, I think that should be DateValue not Date, shouldn't
it?

 

[sonofroy]

Thanks that works great but I just found out I am missing some information I
need for it. Some vehicles are checked out for multiple days at a time and I
am using a [CheckOut] date field plus I have an estimated [EstRtn] Date
Field. And I have to show not only the vehicle the day its checked otu but I
need to show a count for each day that vehicle is out. So if it goes out on a
monday until wednesday I need to show a cout of 1 for each of the 3 days. Is
it possible to add this to query?

Thanks and sorry for being a pest!

In SQL view:


SELECT DISTINCT vehicleID
FROM tableName
WHERE dateTimeField BETWEEN [enter starting date: ] AND [enter ending
date: ]


will produce a list of all vehicles having the value of the supplied
dateTimeField between the two given dates (supplied as parameter).


Vanderghast, Access MVP

I still can not figure out how to show the summary of all the
vehicles checked out each day too. What would I need to change on the
query
to do that?