Formuli in query

Currently I am trying to sum rows in a report to show the difference
between call volume in one chunk of time compared to another chunk in
time. This time chunk is defined by a form gleaned from the Northwind
database setting a beginning and ending date, the second time chunk is
the year prior to the chunk selected by Date"yyyy"-1 method.

Tables
TableDataEntry
ID
Unit
Street_ID (linked to streets selection sub data)
Date
RPD (boolean)
Arrests (integer)
Short_Desc (memo field)
Calltype_ID (lookup to SubDataCallType)
Log_Codes_ID (Lookup to SubDataLogCodes)

SubDataCallType
ID (index)
Type (Text Description)
CallNumber (numeric identifier)

SubDataLogCodes
ID (index)
LogCode (Text identifier)
LogDesc (Long Descriptor)

Data is entered into these via a Form directly to TableDataEntry as
multiple calls per date. While querying for reports, there are certain
calltypes such as Homocide which are not a monthly occurance (thank
goodness) but which need to be counted on a monthly basis and I would
prefer to show there are none (keep in mind when I explain report)

My present query is as such for call total comparison report
SELECT [SubDataCalltype].[Type], [TableDataentry].[ID],
[TableDataentry].[Date]
FROM SubDataCalltype INNER JOIN TableDataentry ON
[SubDataCalltype].[ID] =[TableDataentry].[Calltype_ID]
WHERE [TableDataentry].[Date] Between
[forms]![FormReportDateRange]![Beginning Date] And
[forms]![FormReportDateRange]![Ending Date]
UNION SELECT [SubDataCallType].[Type], null,null FROM SubDataCalltype;

And another stating Date-1 as my WHERE

Both of these are gathered via sub reports into my main report. The
main report headings are such.
Calltype - Call Amount - Percentage of Total - Calltype - Call
Amount(date-1) - Percentage of Total (date-1)

What I would like to do is sum these totals as rows and then show a
total difference at the end of the report along with the already shown
subreport1 and subreport2 totals. When I make reference to those
objects in the report it only returns one =SUM for the first set of
records. All attempts at making these calculations in queries have led
to operation too complex for aggregate somethingoranother. I'm a hunter
of baddies with a little more computer experiance than the others so I
got elected for this and any help you smart group of fellas could
provide would be greatly appreaciated. Oh right, using Access 2000 on
an XP workstation if that helps

B. Markland

.

Relevant Pages

  • Re: Report is making too many numbers
    ... My report is grouped by Vendor, then Project, ... source query before putting it in the report so every record shows). ... A Running Sum text box accumlates its total. ... I've got a problem with Sum Totals at the end of a report, ...
    (microsoft.public.access.reports)
  • Re: Report is making too many numbers
    ... And you want to display the sum of in the Vendor header or footer ... My report is based on a query. ... A Running Sum text box accumlates its total. ... records--you could create a Totals query by depressing the Total ...
    (microsoft.public.access.reports)
  • RE: How to add a total from three other sums in a report
    ... Sometimes the report totals each record correctly sometimes not. ... "Jeff C" wrote: ... However the NZ sum of all three text boxes is adding correctly. ...
    (microsoft.public.access.reports)
  • Re: Report is making too many numbers
    ... from the $'s being listed more than once in the query. ... And you want to display the sum of in the Vendor header or footer ... My report is based on a query. ... those records--you could create a Totals query by depressing the ...
    (microsoft.public.access.reports)
  • Re: Report is making too many numbers
    ... A Running Sum text box accumlates its total. ... I am not able to Sum any text box in a report. ... Another option is to add a group footer for the primary key of table A. ... I've got a problem with Sum Totals at the end of a report, ...
    (microsoft.public.access.reports)