Re: Formuli in query

B. Markland wrote:
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

Hi B,

In situations like this, I have created a report table
with fields for the data, plus one extra field called
"Interval".

Dim dteBeginDate As Date
Dim dteEndDate As Date

If IsDate(Forms!FormReportDateRange!BeginningDate) Then
dteBeginDate = CDate(Forms!FormReportDateRange!BeginningDate)
Else
'quit and tell user
End If

If IsDate(Forms!FormReportDateRange!EndingDate) Then
dteEndDate = CDate(Forms!FormReportDateRange!EndingDate)
Else
'quit and tell user
End If

You empty the table,

CurrentDb.Execute "DELETE * FROM tblReport", dbFailOnError

then append data for first interval using "1" for value
as Interval.

strSQL = "INSERT INTO tblReport " _
& "(Interval, CallType, ID, EntryDate) " _
& "SELECT 1, S.[Type], T.ID, T.[Date] " _
& "FROM SubDataCalltype AS S " _
& "LEFT JOIN " _
& "TableDataentry AS T " _
& "ON S.ID =T.Calltype_ID " _
& "WHERE T.[Date] Between #" _
& dteBeginDate & "# AND # " _
& dteEndDate & "#;"

CurrentDb.Execute strSQL, dbFailOnError

then append data for second interval using "2" for
value of Interval.

dteBeginDate = DateAdd('yyyy', -1, dteBeginDate)
dteEndDate = DateAdd('yyyy', -1, dteEndDate)

strSQL = "INSERT INTO tblReport " _
& "(Interval, CallType, ID, EntryDate) " _
& "SELECT 2, S.[Type], T.ID, T.[Date] " _
& "FROM SubDataCalltype AS S " _
& "LEFT JOIN " _
& "TableDataentry AS T " _
& "ON S.ID =T.Calltype_ID " _
& "WHERE T.[Date] Between #" _
& dteBeginDate & "# AND # " _
& dteEndDate & "#;"

CurrentDb.Execute strSQL, dbFailOnError

Then base your report(s) off tblReport.

One man's opinion, you are welcome to take it
or leave it.

good luck,

gary




.

Relevant Pages

  • 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 ... subreport1 and subreport2 totals. ...
    (microsoft.public.access.queries)
  • RE: Invalid free space report in IDS 7.31.FD8 on Solaris 9
    ... informix$ onstat -d ... When I look in onmonitor's report on corresponding dbspace, ... Chunk Chunk Pages Pages Full Pathname of Chunk ...
    (comp.databases.informix)
  • Re: Tcl/Tk 8.5.2 Release Candidates
    ... While running tests with eTcl builds with this RC, and/or running htmlviewer3, found a problem which is not really a bug, but a serious incompatibility related to new http 2.7 packages merged into 8.5 branch, and more exactly to chunk transfer support. ... It means any current code whuch calls geturl with a -handler argument (e.g. hv3, but also probably most code caring about reporting progress during non-blocking http transfer) will get corrupted answer. ... shall I report this in SF bug report as well, or, since RC has been announced in c.l.t, is this report enough? ...
    (comp.lang.tcl)
  • Re: Chunk errors
    ... This really does not seem to be a security issue. ... The report displays fine when run from report manager ... > displayed in the body of the emailed report which I pasted below TABLE ... > GetReportChunk('C_10_S', 1), chunk was not found! ...
    (microsoft.public.win2000.security)