Duane,
Here is what I have now. I wrote your expression in SQL and when I run
it,
it is telling me "Invalid Bracketing of Name". Can you help??
SELECT Sum(Abs([DB2ADMIN_AGENT_PRODUCTION_SUMMARY.SRC_AUDIT_YR_MO])
Between
#11/1/2005# AND #1/30/2006#) *
([DB2ADMIN_AGENT_PRODUCTION_SUMMARY.NAFYC_CR_AMT]) AS PriorTo,
Sum(Abs([DB2ADMIN_AGENT_PRODUCTION_SUMMARY.SRC_AUDIT_YR_MO] Between
#3/1/2006# AND #5/30/2006#) *
[DB2ADMIN_AGENT_PRODUCTION_SUMMARY.NAFYC_CR_AMT]) AS After
FROM (([Seminar Attendees for Field Development] LEFT JOIN [FDO Seminar
Codes & Dates] ON [Seminar Attendees for Field Development].[Seminar Code]
=
[FDO Seminar Codes & Dates].[Seminar Code]) LEFT JOIN
DB2ADMIN_AGENT_PRODUCTION_SUMMARY ON [Seminar Attendees for Field
Development].[Agent ID] = DB2ADMIN_AGENT_PRODUCTION_SUMMARY.AGT_ID) LEFT
JOIN
DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION ON [Seminar Attendees for Field
Development].[Agent ID] = DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_ID
WHERE (((DB2ADMIN_AGENT_PRODUCTION_SUMMARY.SRC_AUDIT_YR_MO)>=[Enter Start
Date (3 Months Before Seminar)] And
(DB2ADMIN_AGENT_PRODUCTION_SUMMARY.SRC_AUDIT_YR_MO)<[Year/Month of
Seminar])
AND (([Seminar Attendees for Field Development].[Seminar Code])="EPS-06"))
GROUP BY [Seminar Attendees for Field Development].[Agent ID], [FDO
Seminar
Codes & Dates].[Year/Month of Seminar],
DB2ADMIN_AGENT_PRODUCTION_SUMMARY.LINE_OF_BUSINESS,
Right([DB2ADMIN_AGENT_PRODUCTION_SUMMARY]![SRC_AUDIT_YR_MO],2), [FDO
Seminar
Codes & Dates].[Seminar Name], [FDO Seminar Codes & Dates].[Seminar Start
Date];
Duane Hookom said:
When I asked about tables/fields, I was hoping to get all the information
required to provide an answer.
Abs() is Absolute Value which will convert a True expression to 1 and
leave
a False expression as 0.
You can modify the hard-coded dates with date expressions that might use
DateAdd() to add or subtract a number of months to the seminar start
date.
--
Duane Hookom
MS Access MVP
Erika M. said:
What is "Abs"? I've never heard that function.
Also - I don't want to have to change the dates each time I run the
report.
I want the query to know that one column is 3 months worth of
production
PRIOR to the Seminar Start Date, and the other column is 3 months worth
of
production AFTER the seminar start date. Is this possible? Or am I
trying
to do too much??
Thank you for your prompt assistance.
:
SELECT Sum(Abs([Source Audit Date] Between #11/1/2005# AND
#1/30/2006#) *
NAFYC_Credit_Amount) as PriorTo,
Sum(Abs([Source Audit Date] Between #3/1/2006# AND #5/30/2006#) *
NAFYC_Credit_Amount) as After
FROM [query that has many fields];
--
Duane Hookom
MS Access MVP
The Table I am pulling info from in this query has many fields - the
two I
am
using are:
"Source Audit Date" & "NAFYC_Credit_Amount"
In another table, which lists my seminar attendees and the dates of
the
seminar, I am pulling the "Seminar Start Date" (i.e. 2/1/2006). I
want
my
query to sum the "NAFYC_Credit_Amount" in two separate fields: one
pulling
"NAFYC_Credit_Amount" from any entry (in the Production Table) made
between
11/1/2006 & 1/30/2006 (3 months) and the other column to sum
"NAFYC_Credit_Amount" from any entry (in the Production Table) made
between
3/1/2006 & 5/30/2006 (3 months after seminar). Does this help?
:
Any information you could share on tables and fields? How about
sample
records and desired display?
--
Duane Hookom
MS Access MVP
I am trying to build a report from a query, and here is what I
want
to
do:
We have a table on our main database that lists production, by
date,
for
our
agents. Each day a policy gets paid, the agent's production is
posted
as
a
separate record in the table. We conduct seminars and I am
trying
to
track
agents' production using our production table. In my query, for
the
"source
audit date", I want to have field that sums the production from 3
months
prior to the seminar start date, and another field that sums the
production
from 3 months after the seminar start date: to show benefits of
attending
the seminar. Can anybody help me??
