Need Help Counting

[Boz]

We are scheduling appointments for trucking companies to arrive with freight.
I have a table that we enter the scheduled date in one field and the
scheduled time in another. If the carrier is bringing more than one vendor
on each truck, we use the same appointment time. Unfortunately, when we run
a query to see how many appointments we have each day, it counts each record.
THis is how it displays:


Appointment Date Appointment Time Appointment Number
Vendor
04/27/07 10:15 am 123
ABC
04/2707 12:15 am 124
DEF
04/27/07 12:15 am 125
GHI
04/28/07 0:00 am 126
JKL
I need it to show the information like this:

Appointment Date Appointments
04/27/07 2
04/28/07 1

I can the the query to just show me totals, that isn't the problem.
Unfortunately it will show me having 3 appointments on 04/27/07 and 1 on
4/28/07. I guess what I'm trying to say is that I need the query to count
unique times.

 

[Jason Lepack]

Enter this SQL using SQL View in the query designer. Anything in [ ]
needs to be changed to the actual field and table names that they
represent.

SELECT
[Appointment Date],
Count([Appointment Number]) AS Appointments
FROM
[Your Table Name Here]
GROUP BY
[Appointment Date],
[Appointment Time]

Cheers,
Jason Lepack

 

[Jason Lepack]

I knew something was nagging at me the entire time I was posting that!

Microsoft Access stores dates and times in the same type of data
field. It's called a DateTime.

Your design would be much better if you stored your appointment time
and appointment date in the same field!

You can create this joined data field using:
SELECT
[Appointment Date] + [Appointment Time] AS AppointmentDateTime,
[Appointment Number]
FROM
[Your Table Name Here]

Cheers,
Jason Lepack

Enter this SQL using SQL View in the query designer. Anything in [ ]
needs to be changed to the actual field and table names that they
represent.

SELECT
[Appointment Date],
Count([Appointment Number]) AS Appointments
FROM
[Your Table Name Here]
GROUP BY
[Appointment Date],
[Appointment Time]

Cheers,
Jason Lepack

We are scheduling appointments for trucking companies to arrive with freight.
I have a table that we enter the scheduled date in one field and the
scheduled time in another. If the carrier is bringing more than one vendor
on each truck, we use the same appointment time. Unfortunately, when we run
a query to see how many appointments we have each day, it counts each record.
THis is how it displays:

Appointment Date Appointment Time Appointment Number
Vendor
04/27/07 10:15 am 123
ABC
04/2707 12:15 am 124
DEF
04/27/07 12:15 am 125
GHI
04/28/07 0:00 am 126
JKL
I need it to show the information like this:

Appointment Date Appointments
04/27/07 2
04/28/07 1

I can the the query to just show me totals, that isn't the problem.
Unfortunately it will show me having 3 appointments on 04/27/07 and 1 on
4/28/07. I guess what I'm trying to say is that I need the query to count
unique times.

 

[Boz]

I know the date and time fields should be combined, but this is a really old
database (running in Access 97) and it's reallllllllly big. I am in the
process of building a new one, but it's slow going. Anyway, one question
about your answer. I already have a query built. Should I just create a new
query using your suggestion or do I insert it in the old one. I'm not real
up on SQL. Here is the current SQL....

SELECT Tbl_Schedule.[Awarded Appointment Date], Tbl_Schedule.[Awarded
Appointment Time], Tbl_Schedule.[Probill #], Tbl_VendorListing.[Vendor Name],
Tbl_Schedule.[TF Unloading Method] INTO [LTL Query]
FROM Tbl_VendorListing INNER JOIN Tbl_Schedule ON Tbl_VendorListing.[Vendor
#] = Tbl_Schedule.[Vendor #]
WHERE (((Tbl_Schedule.[Awarded Appointment Date])=Date()) AND
((Tbl_Schedule.[Probill #]) Is Not Null) AND ((Tbl_Schedule.[TF Unloading
Method])="TF LTL"));

I knew something was nagging at me the entire time I was posting that!

Microsoft Access stores dates and times in the same type of data
field. It's called a DateTime.

Your design would be much better if you stored your appointment time
and appointment date in the same field!

You can create this joined data field using:
SELECT
[Appointment Date] + [Appointment Time] AS AppointmentDateTime,
[Appointment Number]
FROM
[Your Table Name Here]

Cheers,
Jason Lepack

Enter this SQL using SQL View in the query designer. Anything in [ ]
needs to be changed to the actual field and table names that they
represent.

SELECT
[Appointment Date],
Count([Appointment Number]) AS Appointments
FROM
[Your Table Name Here]
GROUP BY
[Appointment Date],
[Appointment Time]

Cheers,
Jason Lepack

We are scheduling appointments for trucking companies to arrive with freight.
I have a table that we enter the scheduled date in one field and the
scheduled time in another. If the carrier is bringing more than one vendor
on each truck, we use the same appointment time. Unfortunately, when we run
a query to see how many appointments we have each day, it counts each record.
THis is how it displays:

Appointment Date Appointment Time Appointment Number
Vendor
04/27/07 10:15 am 123
ABC
04/2707 12:15 am 124
DEF
04/27/07 12:15 am 125
GHI
04/28/07 0:00 am 126
JKL
I need it to show the information like this:

Appointment Date Appointments
04/27/07 2
04/28/07 1

I can the the query to just show me totals, that isn't the problem.
Unfortunately it will show me having 3 appointments on 04/27/07 and 1 on
4/28/07. I guess what I'm trying to say is that I need the query to count
unique times.

 

[Jason Lepack]

Create a new query:

SELECT
[Awarded Appointment Date],
Count([Probill #]) AS Appointments
FROM
[The name of the query that you posted above]
GROUP BY
[Awarded Appointment Date],
[Awarded Appointment Time]

OR:

1. Create a new query in design view.
2. Add in the query that listed above
3. Add three fields [Awarded Appointmnet Date], [Awarded Appointment
Time], [Probill #]
4. Click the sigma looks kind of like an "E" in the toolbar.
5. In the "Totals" row for [Probill #] select "Count"
6. Make sure the other two say "Group By"
7. Run your query.

Let us know how it goes.

Cheers,
Jason Lepack

I know the date and time fields should be combined, but this is a really old
database (running in Access 97) and it's reallllllllly big. I am in the
process of building a new one, but it's slow going. Anyway, one question
about your answer. I already have a query built. Should I just create a new
query using your suggestion or do I insert it in the old one. I'm not real
up on SQL. Here is the current SQL....

SELECT Tbl_Schedule.[Awarded Appointment Date], Tbl_Schedule.[Awarded
Appointment Time], Tbl_Schedule.[Probill #], Tbl_VendorListing.[Vendor Name],
Tbl_Schedule.[TF Unloading Method] INTO [LTL Query]
FROM Tbl_VendorListing INNER JOIN Tbl_Schedule ON Tbl_VendorListing.[Vendor
#] = Tbl_Schedule.[Vendor #]
WHERE (((Tbl_Schedule.[Awarded Appointment Date])=Date()) AND
((Tbl_Schedule.[Probill #]) Is Not Null) AND ((Tbl_Schedule.[TF Unloading
Method])="TF LTL"));

I knew something was nagging at me the entire time I was posting that!

Microsoft Access stores dates and times in the same type of data
field. It's called a DateTime.

Your design would be much better if you stored your appointment time
and appointment date in the same field!

You can create this joined data field using:
SELECT
[Appointment Date] + [Appointment Time] AS AppointmentDateTime,
[Appointment Number]
FROM
[Your Table Name Here]

Cheers,
Jason Lepack

Enter this SQL using SQL View in the query designer. Anything in [ ]
needs to be changed to the actual field and table names that they
represent.
SELECT
[Appointment Date],
Count([Appointment Number]) AS Appointments
FROM
[Your Table Name Here]
GROUP BY
[Appointment Date],
[Appointment Time]
Cheers,
Jason Lepack
We are scheduling appointments for trucking companies to arrive with freight.
I have a table that we enter the scheduled date in one field and the
scheduled time in another. If the carrier is bringing more than one vendor
on each truck, we use the same appointment time. Unfortunately, when we run
a query to see how many appointments we have each day, it counts each record.
THis is how it displays:
Appointment Date Appointment Time Appointment Number
Vendor
04/27/07 10:15 am 123
ABC
04/2707 12:15 am 124
DEF
04/27/07 12:15 am 125
GHI
04/28/07 0:00 am 126
JKL
I need it to show the information like this:
Appointment Date Appointments
04/27/07 2
04/28/07 1
I can the the query to just show me totals, that isn't the problem.
Unfortunately it will show me having 3 appointments on 04/27/07 and 1 on
4/28/07. I guess what I'm trying to say is that I need the query to count
unique times.

 

[jgraves]

Wow! Thanks alot. This SQL Query is just what I needed for my problem.
-Jen Graves

Enter this SQL using SQL View in the query designer. Anything in [ ]
needs to be changed to the actual field and table names that they
represent.

SELECT
[Appointment Date],
Count([Appointment Number]) AS Appointments
FROM
[Your Table Name Here]
GROUP BY
[Appointment Date],
[Appointment Time]

Cheers,
Jason Lepack

We are scheduling appointments for trucking companies to arrive with freight.
I have a table that we enter the scheduled date in one field and the
scheduled time in another. If the carrier is bringing more than one vendor
on each truck, we use the same appointment time. Unfortunately, when we run
a query to see how many appointments we have each day, it counts each record.
THis is how it displays:

Appointment Date Appointment Time Appointment Number
Vendor
04/27/07 10:15 am 123
ABC
04/2707 12:15 am 124
DEF
04/27/07 12:15 am 125
GHI
04/28/07 0:00 am 126
JKL
I need it to show the information like this:

Appointment Date Appointments
04/27/07 2
04/28/07 1

I can the the query to just show me totals, that isn't the problem.
Unfortunately it will show me having 3 appointments on 04/27/07 and 1 on
4/28/07. I guess what I'm trying to say is that I need the query to count
unique times.