SQL Count by Date

[NoFrills]

I have a query that uses 2 Fields from a large table. Cashier and Date. The problem I am having is I would like the select a time frame, a Start and Stop date, and count how many times the cashier comes up for that day.

Sounds easy, I would think so but having a problem. The dates are automatically imported into the large table as format 01/01/2005 12:00pm (Can't do anything about this).

When writing the SQL and the Count function, it is using the time as well in its calculation, but I simply want it to count on the date only. So unless a cashier existed exactly in the table as 01/01/2005 12:01pm, 01/01/2005/12:01pm it only counts it as a total of 1, instead of twice for that day.

I simply want the SQL function to read the Date field as 01/01/2005, and drop off the Time. I can format it, but only changes it visually and the SQL statement still sees the time.



Any help would be appreciated.


I found this code on the boards here, almost what I want but does not group each Cashier by day and total, it does the total of all cashiers for that day. I need the Cashiers sorted for each day, and totalled.

SELECT Format([Date1],"dd mmm yyyy") AS Day1, Count([ Cashier1].[Cashier1]) AS CountofCashier
FROM CashierQ1
GROUP BY Format([Date1],"dd mmm yyyy"), Format([Date1],"dd mmm yyy")
ORDER BY Format([Date1],"dd mmm yyyy");

 

[NoFrills]

Figured it out. Thanks anyhow.

 

[NoFrills]

Stuff is goingto drive me crazy. I got the Queries just like I want them, but now when trying to grab data with a Start and End Date, it keeps returning with blank results.


Query3:

SELECT VendorQ1.Vendor1, Count(VendorQ1.Vendor1) AS CountofVendor, Format([Date1],"dd mmm yyyy") AS Day1
FROM VendorQ1
GROUP BY VendorQ1.Vendor1, Format([Date1],"dd mmm yyyy"), Format([Date1],"dd mmm yyy")
ORDER BY Format([Date1],"dd mmm yyyy");

Query4:

SELECT DISTINCTROW VendorQ3.Vendor1, VendorQ3.Day1, Sum(VendorQ3.CountofVendor) AS CountofVendor1
FROM VendorQ3
GROUP BY VendorQ3.Vendor1, VendorQ3.Day1;

FORM Calling Query Sort:

StartDate = Format([StartDate], "dd mmm yyyy")
EndDate = Format([EndDate], "dd mmm yyyy")

strlinkcriteria = "Day1 between #" & StartDate & "# and #" & EndDate & "#"
sort1 = "Select * from VendorQ4 ORDER BY " & temp1

Is is that Day1 is no longer a Date Field? If is isn't, what is it?

Any help here would be appreciated

 

[NoFrills]

Lol, sorry for having a conversation with myself. Everytime I finally give up and post my problem, I kind of find a solution.

Line should read:

strlinkcriteria = "Day1 between " & Chr(34) & StartDate & Chr(34) & " and " & Chr(34) & EndDate & Chr(34) & ""

 

[NoFrills]

Please bare with my insanity of yet another question:

OK, I got everything how I want it. If I want the last 2 weeks of sales, it will list:


Vendor1.......1 Jan 2005........25
Vendor1.......2 Jan 2005........10
Vendor1.......3 Jan 2005.........5
Vendor2.......4 Jan 2005.........1
Vendor2.......5 Jan 2005.........18
...
..

You get the idea


But I would like:

Vendor1......................40
Vendor2......................19

Just list the Vendor name, and how many sales they did within the time frame I gave?

This one I am truely stumped on.

 

[mresann]

Not sure if this is exactly what you need, but take a look at the actual query.

SELECT tblCashier.Cashier, Count(tblCashier.SaleDate) AS CountOfSaleDate
FROM tblCashier
WHERE (((tblCashier.SaleDate)>=[Start Date] And (tblCashier.SaleDate)<=[Stop Date]))
GROUP BY tblCashier.Cashier
ORDER BY tblCashier.Cashier;

 

[NoFrills]

I know I must be missing something very easy here, but I am not seeing it. Perhaps my problem is how I am handling the original data. If you guys can give your ideas on how I should go about formatting that data correctly, maybe it will smooth the way forward.

The original data looks like this.

Vendor1         Item1       Date1

Furniture        Couch      1/1/2005 10:00:00 PM
Lights           Bulb        2/1/2005  5:18:24 PM
Lights           Switch    9/4/2005 3:01:02 AM
Furniture        Chair        9/4/2005  11:00:00 AM
Furniture        Table       9/4/2005  1:00:00 AM
Furniture        Table       9/4/2005  5:00:00 AM

--------------------------------------------------------

Problem is If I wanted to query how many items sold on 9/4/2005 I am getting a list that has:

Vendor1            Item1           Quantity

Lights              Switch                1
Furniture           Chair                 1
Furniture           Table                 1
Furniture           Table                 1

But what I want is:

Vendor1            Item1           Quantity

Lights              Switch                1
Furniture           Chair                 1
Furniture           Table                 2

Since there is a time in the original Date1 field, when Sum'ing, it counts each entry as a seperate field if BOTH the time and dates do not match. if there is a Way to SUM these by using ONLY the Date portion of the Date1 field, and NOT the time, that would solve this problem.