Daily County of date records

  • Thread starter Thread starter sonofroy
  • Start date Start date
S

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
 
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
 
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.
Click to expand...

Vanderghast, I think that should be DateValue not Date, shouldn't it?
 
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 said:
Definitively, thanks to point it out.

Vanderghast, Access MVP
Click to expand...
 
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 said:
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!
Click to expand...
 
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 get a Syntax error on this line SELECT vehicleID, DATEVALUE(dateTimeField),
COUNT(*)

vanderghast said:
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 said:
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
Click to expand...
Click to expand...
 
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?


sonofroy said:
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!
Click to expand...
 
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
 
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



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

vanderghast said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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!

vanderghast said:
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


sonofroy said:
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?
Click to expand...
Click to expand...
 
Back
Top