Formuli in query

C

celticangyl

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
 
G

Gary Walter

B. Markland said:
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
Click to expand...
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
 
G

Gary Walter

I'm sorry...I tried to filter on the inner table
of an outer join which is a no-no.
I was trying to eliminate your UNION clause.

Save any query as "qryPreFilterDates" ...
it doesn't matter what it does because
we'll change its SQL in code anyway.

Dim dteBeginDate As Date
Dim dteEndDate As Date
Dim strSQL As String

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

'rewrite prefilter query ("qryPreFilterDates")

strSQL = "SELECT T.ID, T.[Date], T.Calltype_ID " _
& "FROM TableDataentry As T " _
& "WHERE T.[Date] Between #" _
& dteBeginDate & "# AND # " _
& dteEndDate & "#;"

CurrentDb.QueryDefs("qryPreFilterDates").SQL=strSQL

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

strSQL = "INSERT INTO tblReport " _
& "(Interval, CallType, ID, EntryDate) " _
& "SELECT 1, S.[Type], Q.ID, Q.[Date] " _
& "FROM SubDataCalltype AS S " _
& "LEFT JOIN " _
& "qryPreFilterDates As Q " _
& "ON S.ID =Q.Calltype_ID "

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 = "SELECT T.ID, T.[Date], T.Calltype_ID " _
& "FROM TableDataentry As T " _
& "WHERE T.[Date] Between #" _
& dteBeginDate & "# AND # " _
& dteEndDate & "#;"

CurrentDb.QueryDefs("qryPreFilterDates").SQL=strSQL

strSQL = "INSERT INTO tblReport " _
& "(Interval, CallType, ID, EntryDate) " _
& "SELECT 2, S.[Type], Q.ID, Q.[Date] " _
& "FROM SubDataCalltype AS S " _
& "LEFT JOIN " _
& "qryPreFilterDates As Q " _
& "ON S.ID =Q.Calltype_ID "

CurrentDb.Execute strSQL, dbFailOnError

'open report


This is of all air code so I could have
easily got something wrong.


B. Markland said:
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
Click to expand...
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting multiple field values in a single query/report 2
Query Help Needed 8
Need the SUM TOTAL in Hours for Time Out-Time In 2
Running Sum in query 1
Troubleshoot A Query 1
Crosstab query by date range 7
SUM in a UNION query 2
Send parameters from form to query 6

Top