I need to calculate a total

[mondoray]

Guys,

I have a select query that is used to provide the number of items with a "1C" designation for a date period. The query gives me a count for each date. I need to get a total ( as below)of the count during a set period ( date from to date to).


Date Number CountofNumber Total

29.11.04 1C 1 2
30.11.04 1C 1

Any help would be appreciated.

mondoray

 

[GingerR]

What is your current query? What have you tried so far that isn't working?
How do you want to do it? Prompt the user for two dates, or do you have a form you'll be taking the dates from? Have you tried putting criteria in your query?

 

[mondoray]

GingerR,

The data is required to build a MS graph chart. The user will specifiy the date to and data from via combo's on a form that has the graphs embeded.

I feel that I have to create more than one query to obtain the data. I did create another query to do the calculation. It worked fine until I put the date component in it.

mondoray

 

[GingerR]

hey there--please paste your query here.
thanks--g

 

[mondoray]

GingerR,

This is the base query:

SELECT DISTINCTROW rptDfrls.Date, rptDfrls.Number, Count(rptDfrls.Number) AS CountOfNumber, rptDfrls.Categories
FROM rptDfrls
GROUP BY rptDfrls.Date, rptDfrls.Number, rptDfrls.Categories;

The second query I created was based on the above and did a count of the CountOfNumber.

SELECT Count(qryChart.Number) AS CountOfNumber1
FROM qryChartNo1, qryChart;

But the date part is the problem.

mondoray

 

[faxpay]

mondoray,

try this:

select sum(CountofNumber)
from (insert table or query here)
where Date between '29.11.04' and '30.11.04'

query has to be in the database. Not sure if the dates will work the way you have them. Give this a try. At least maybe this is a starting point."between) is the operator you need.

faxpay, Tom

 

[Golom]

You should note that, according to the Access documentation, DISTINCTROW has no effect if you are selecting from only one table OR if you have multiple tables joined but are selecting fields from all of them.

You should probably eliminate that because GROUP BY creates a distinct record for each combination of the fields in the group.

Another possibility is that the "Date" field has datetime values (i.e. things like "05/27/2004 15:23:17".) In that case you are grouping records only if they are identical to the second. You may want to truncate the Date field to a whole day rather than grouping by the raw Date field value.

As I look at your code, it appears that the following is what you are doing (with everything in-line.)

SELECT Count(qryChart.Number) AS CountOfNumber1

FROM 

   (SELECT DISTINCTROW 
       rptDfrls.Date, rptDfrls.Number, 
       Count(rptDfrls.Number) AS CountOfNumber, 
       rptDfrls.Categories
    FROM rptDfrls
    GROUP BY 
       rptDfrls.Date, 
       rptDfrls.Number, 
       rptDfrls.Categories) As qryChartNo1, 

   (SELECT DISTINCTROW 
       rptDfrls.Date, rptDfrls.Number, 
       Count(rptDfrls.Number) AS CountOfNumber, 
       rptDfrls.Categories
    FROM rptDfrls
    GROUP BY 
       rptDfrls.Date, 
       rptDfrls.Number, 
       rptDfrls.Categories) As qryChart;

My question is:

Why are you doing a cross join of the query to itself? That will give you the square of the number of records in a single occurence of the query ... and the square of the count of the number of records in a single copy of the query.

 

[mondoray]

Fapay,

Thks I will give it a try.

mondoray

 

[mondoray]

Golom,

Sorry for the delayed response as I have been fiddling with the program.

I have created a crosstab qry which give me the totals I require (as below). My problem now is that I now cannot specify a date (from two combo boxes) to run the qry for a selected date.

TRANSFORM Count(rptDfrls.Number) AS [Value]
SELECT rptDfrls.Categories, Count(rptDfrls.Number) AS Total, rptDfrls.Date AS [Date]
FROM rptDfrls
GROUP BY rptDfrls.Categories
ORDER BY rptDfrls.Date
PIVOT rptDfrls.Date;

Any help appreciated.

mondoray

 

[PHV]

PARAMETERS [Forms]![form name]![combo date from] DateTime,
[Forms]![form name]![combo date to] DateTime;
TRANSFORM Count(rptDfrls.Number) AS [Value]
SELECT rptDfrls.Categories, Count(rptDfrls.Number) AS Total, rptDfrls.Date AS [Date]
FROM rptDfrls
WHERE rptDfrls.Date BETWEEN [Forms]![form name]![combo date from] AND [Forms]![form name]![combo date to]
GROUP BY rptDfrls.Categories
ORDER BY rptDfrls.Date
PIVOT rptDfrls.Date;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mondoray]

PHV,

Many thanks I will give it a try as soon as I can.

mondoray