Counting Rows within a query

[RobWulf]

OKay, driving myself a bit nuts with this.

I am trying to get a total count of rows from a query on my tickets, Each ticket has a date and an ID number

SELECT tbl_ticket.ticket, tbl_ticket.entrydate, Count(tbl_ticket.[entrydate]) AS [Row Count]
FROM tbl_ticket
GROUP BY tbl_ticket.ticket, tbl_ticket.entrydate
HAVING (((tbl_ticket.entrydate) Between #1/1/2011# And #1/31/2011#));

I am trying to get this query built so I can attach it to a form.

I am trying to get three bits of data from this Query

1 a total count of all the Tickets in a given month
The ticket #'s and their date of entry.

So far this gives me a great list of Tickets and the date they got put in the system, but then for the total count it gives me 1 for each line. Even if some have the same date. :banghead:

 

[plog]

I am trying to get three bits of data from this Query

That will not be possible. You want aggregate data, as well as individual data. That cannot be accomplished with one query. This query will give you individual data you want:

SELECT tbl_ticket.ticket, tbl_ticket.entrydate
FROM tbl_ticket
GROUP BY tbl_ticket.ticket, tbl_ticket.entrydate
WHERE entrydate  Between #1/1/2011# And #1/31/2011#;

And this will give you the total count:

SELECT COUNT(tbl_ticket.ticket)
FROM tbl_ticket
WHERE entrydate  Between #1/1/2011# And #1/31/2011#;

 

[RobWulf]

Okay, I am doing the count with a DCount however I have run into the problem of it counting extra days?

Me.A.Value = DCount("[ticket]", "tbl_ticket", "[entrydate] Between #1/1/2013# AND #1/31/2013#")

I should have 26 records show up, but for some reason this code is pulling the first few days of Feb to get an extra three tickets? How can I stop it from doing that? :banghead: I even tried removing the #'s from the dates, all that got me was a zero count. Putting one # in front of the dates gets an error.

 

[plog]

My guess is your data actually has 29. Look at it again, make sure you are looking at the entrydate field. Try running the SQL I posted earlier to get the total.