Problem with Grouping

[anchorp]

I am having a problem that I hope has a very simple solution that I am somehow overlooking.

I have simplified my query for the purposes of this question. I have a query that only get the date and amount of a transaction. I want to group the information by date and have the transaction amount summed. For some reason it will not group by date.

Here is my query displayed in SQL:
SELECT Transactions.Date, Sum(Transactions.TransTotal) AS SumOfTransTotal
FROM Transactions
GROUP BY Transactions.Date
ORDER BY Transactions.Date;

And here is a sample of the data returned:
Date SumOfTransTotal
12/13/2005 5.12
12/13/2005 5.12
12/13/2005 5.12
12/13/2005 5.12
12/15/2005 1.15
12/15/2005 1
12/15/2005 0.12
12/15/2005 6.56
12/16/2005 19.14
12/16/2005 12
12/16/2005 8.16
12/17/2005 11.11

Why will it not group all the 12/13 or 12/15's together? I have done other queries like this and not had this problem. What am I overlooking?

Any help is very much appreciated. Thanks,

 

[pbaldy]

Does your date field include the time? If so each record on the same day is probably slightly different.

 

[anchorp]

I cannot see a time when I open the table. However, I also wondered if this was the problem. Is it possible to use a cdate command in the query to make sure it is a short date before it groups? I have heard of this but I don't know exactly how to do it or if it will work.

 

[pbaldy]

I don't think CDate would change anything. Try:

Format(Datefield, "Short date")

in both the select and group by clauses.

 

[anchorp]

I attempted to translate your suggestion into my database with the following code:

SELECT Transactions.Date, Sum(Transactions.TransTotal) AS SumOfTransTotal
FROM Transactions
GROUP BY Transactions.Date, Format([Transactions.Date],'yyyymmdd')
ORDER BY Transactions.Date, Format([Transactions.Date],'yyyymmdd');

And the output is:

Date SumOfTransTotal
12/13/2005 5.12
12/13/2005 5.12
12/13/2005 5.12
12/13/2005 5.12
12/15/2005 1.15
12/15/2005 1
12/15/2005 0.12
12/15/2005 6.56
12/16/2005 19.14
12/16/2005 12
12/16/2005 8.16
12/17/2005 11.11
12/19/2005 9.68

It still has not grouped all the 12/13s. Are there other possible reasons for this besides a timestamp that is unseen in the date field?

 

[pbaldy]

Try this:

SELECT Format([Transactions.Date],'yyyymmdd') AS DateOnly, Sum(Transactions.TransTotal) AS SumOfTransTotal
FROM Transactions
GROUP BY Format([Transactions.Date],'yyyymmdd')
ORDER BY Format([Transactions.Date],'yyyymmdd');

 

[anchorp]

That yielded:

DateOnly SumOfTransTotal
20051213 5.12
20051213 5.12
20051213 5.12
20051213 5.12
20051215 1.15
20051215 1
20051215 0.12
20051215 6.56
20051216 19.14
20051216 12
20051216 8.16
20051217 11.11
20051219 9.68

I have never scene a dataset behave like this. What could cause this? I appreciate the help very much. Any other suggestions?

 

[pbaldy]

Offhand I can't think of anything. Can you post a sample db we can play with?

 

[anchorp]

That would be great but I don't know how to do that. Do you mean attach a file to my post? If so I could pare down the data. This query problem is only dealing with one table.

 

[pbaldy]

Yes, just attach it to a post; it has to be zipped and under 393k.

 

[anchorp]

This is very strange. In order to make a smaller version of my database to post here as you suggested I copied a portion of the data from the table I was working with before to an excel file. I then copied the structure of the table from the orignial database and copied the data portion into it.

Now the query works fine as you will see if you try running it. Something about the copying process seems to have eliminated whatever formating is causing the problem.

How do I deal with this however. My problem is that the data is coming from a tracking system that eventually I want to run append queries and constantly bring in the new data. So I do not have the luxurying of one time copying the data into excel like this and then going forward with a good data set indefinately. Does this make sense. Somehow I need to figure out how to work through this instead of around it. I think? Maybe you know a reason why this assumption is not the case. I appreciate any thoughts.

 

[anchorp]

I made another version here by just dealeting things our carefully rather than copying to excel. This one continues to have the problem that I am facing. This way you can see what I am seeing.

 

[pbaldy]

Downloaded the second, and this correctly returns 7 records, 1 per date:

SELECT Format([Date],"Short Date") AS DateOnly, Sum(Transactions.TransTotal) AS SumOfTransTotal
FROM Transactions
GROUP BY Format([Date],"Short Date")
ORDER BY Format([Date],"Short Date");

 

[anchorp]

Wonderful! Thank you.