Misc Time - Continued

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following summary query for a production database:

Date Processor Sum Of MiscTime

The SQL window shows this-
SELECT [Date], Processor, Sum(MiscTime) As MiscTimeSum
FROM ProdTable1
GROUP BY [Date], Processor

The query is still producing this result:

Ex: Current output
Date Processor Sum Of MiscTime
10/18/2004 Sally 2.5
10/18/2004 Sally 3.0
10/18/2004 Sally 0.5
10/19/2004 Ruth 3.5
10/19/2004 Ruth 2.5

What I need:
Date Processor Sum Of Misc Time
10/18/2004 Sally 6
10/19/2004 Ruth 5

Why will the query not collapse/condense/combine the numbers for the same
date?

Thanks
dlemarr
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

My guess: The column "Processor" values have spaces in them, or, the
column "Date" includes the Time value - the Time values are different
and, therefore, separate "Date" values are grouped together, but only
the date is displayed.

Possible solutions (using JET):

Get just the Date:

SELECT CDate(Format([Date],"m/d/yyyy")) As theDate
...
GROUP BY CDate(Format([Date],"m/d/yyyy"))

Trim the "Processor" values:

SELECT Trim([Processor]) As theProcessor, ...
...
GROUP BY Trim([Processor])

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYajxoechKqOuFEgEQJtLgCbBd4vsEePhjhuF5uc3QXl63tYR2QAoMED
XH1g1c6dft9nTjdWuZszgdC3
=Tr87
-----END PGP SIGNATURE-----
 
Dear dlemarr:

From what you say, there must be some specific reason it is not able
to group by [Date] and Processor as you have told it to do. A
possibility is that Sally is not always spelled the same way. I'll
show you what I mean. Using B to mean a blank (space) then you might
have values of: "Sally", "SallyB", and "SallyBB". Something similar
could be happening with the Date column, especially if it is text.
That might be that your dates contain a Time component which you are
not reporting.

You could run a query on this data like:

SELECT DISTINCT Processor FROM ProdTable1 ORDER BY Processor

to see if "Sally" is always entered the same way. To ensure
consistent results like this in future, be sure you have a related
table of permissible Processor values and enforce the use of values
only from that table.

A similar query that may be instructive:

SELECT DISTINCT [Date] FROM ProdTable1 ORDER BY [Date]

will show whether there are multiple different values for [Date] that
just appear to be the same, but are not.

Come back with some results to these proposals and perhaps your
problem can be explained and solved.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Dear Tom,

Here are the query results for the SELECT queries that you suggested I run.
It looks like the processor name is fine, but the date must be capturing the
time as you suggested. The format of the Date field is Short Date. Any
suggestions on how I can eliminate the duplicate dates? Your help is greatly
appreciated.

Processor
ADMIN

Date
10/18/2004
10/18/2004
10/18/2004
10/18/2004
10/28/2004

Suggestions?

Tom Ellison said:
Dear dlemarr:

From what you say, there must be some specific reason it is not able
to group by [Date] and Processor as you have told it to do. A
possibility is that Sally is not always spelled the same way. I'll
show you what I mean. Using B to mean a blank (space) then you might
have values of: "Sally", "SallyB", and "SallyBB". Something similar
could be happening with the Date column, especially if it is text.
That might be that your dates contain a Time component which you are
not reporting.

You could run a query on this data like:

SELECT DISTINCT Processor FROM ProdTable1 ORDER BY Processor

to see if "Sally" is always entered the same way. To ensure
consistent results like this in future, be sure you have a related
table of permissible Processor values and enforce the use of values
only from that table.

A similar query that may be instructive:

SELECT DISTINCT [Date] FROM ProdTable1 ORDER BY [Date]

will show whether there are multiple different values for [Date] that
just appear to be the same, but are not.

Come back with some results to these proposals and perhaps your
problem can be explained and solved.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have the following summary query for a production database:

Date Processor Sum Of MiscTime

The SQL window shows this-
SELECT [Date], Processor, Sum(MiscTime) As MiscTimeSum
FROM ProdTable1
GROUP BY [Date], Processor

The query is still producing this result:

Ex: Current output
Date Processor Sum Of MiscTime
10/18/2004 Sally 2.5
10/18/2004 Sally 3.0
10/18/2004 Sally 0.5
10/19/2004 Ruth 3.5
10/19/2004 Ruth 2.5

What I need:
Date Processor Sum Of Misc Time
10/18/2004 Sally 6
10/19/2004 Ruth 5

Why will the query not collapse/condense/combine the numbers for the same
date?

Thanks
dlemarr
Click to expand...
Click to expand...
 
Back
Top