Re: Date Range Totals for Logical Fields
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Thu, 7 Feb 2008 15:19:20 -0500
Query reformatted for ease of reading
SELECT [Lead Date]
, Format([Lead Date],'mmmm yyyy') AS [Lead Date By Month]
, [Signed Up]
, Sum([Active Recruit]) AS [Sum Of Active Recruit]
, Sum([Info Kit Sent]) AS [Sum Of Info Kit Sent]
, Sum([Direct Recruit]) AS [Sum Of Direct Recruit]
, Sum([Initial Contact Made]) AS [Sum Of Initial Contact Made]
, Sum([DD Invite Letter Sent]) AS [Sum Of DD Invite Letter Sent]
, Sum([Accepted DD Invite]) AS [Sum Of Accepted DD Invite]
, Sum([Attended DD Mtg]) AS [Sum Of Attended DD Mtg]
, Sum([Follow-up After DD Mtg]) AS [Sum Of Follow-up After DD Mtg]
, Sum([Has Contract]) AS [Sum Of Has Contract]
, Count(*) AS [Count Of Qry_AllDDLeads by Date Range]
FROM [Qry_AllDDLeads by Date Range]
WHERE [Lead Date] Between #1/28/2008# And #2/1/2008#
GROUP BY [Lead Date]
, Format([Lead Date],'mmmm yyyy')
, [Signed Up]
, Year([Lead Date])*12+DatePart('m',[Lead Date])-1
Your query should return information for the period 1/28 up to midnight of
2/1 Basically for records where Lead Date runs from 1/28 to 1/31. IF you
want records for the Feb 1 also, then you need to change the where clause to
WHERE [Lead Date] >= #1/28/2008# And [Lead Date] < #2/2/2008#
I don't have any idea what you are attempting to accomplish with the
expression
, Year([Lead Date])*12+DatePart('m',[Lead Date])-1
That looks as if it is useless to me.
Do you have records for each of the dates 1/28 to 2/1? This query is only
going to work on those records. If you have no record for 1/31 then there
will be row returned for that date.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
"Susan May" <SusanMay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:02A634D9-BE7A-45EE-B543-93BA9D0C5789@xxxxxxxxxxxxxxxx
Hi John:
Here's the Sql statement:
SNIP
This creates 2 rows:
Lead Date Lead Date By Month Signed Up Sum Of Active Recruit Sum Of Info
Kit
Sent Sum Of Direct Recruit Sum Of Initial Contact Made Sum Of DD Invite
Letter Sent Sum Of Accepted DD Invite Sum Of Attended DD Mtg Sum Of
Follow-up
After DD Mtg Sum Of Has Contract Count Of Qry_AllDDLeads by Date Range
1/29/2008 1:00:00 PM January 2008 0 -2 -1 0 0 -1 -1 0 0 0 2
1/30/2008 1:00:00 PM January 2008 0 -8 -8 0 -1 0 0 0 0 0 8
For the period of 12/28/08 - 2/1/08 when I filter the data in Outlook, I
have 2 leads on 1/29; 8 on 1/30; 7 on 1/31 and 1 on 2/1. What's strange
is
that in other queries, the total number of active recruits in these
queries
total 137 which is what I get when I filter in Outlook. But when I put a
date range in there, it screws up all the data. None of these fields show
the correct number. Here's what I get when I filter in Outlook
Week of Active Info Direct Initial DD
DD
Recruits Kits Sent Recruits Contact Invites
Accepted
Made
1/28/2008 18 17 2 1 1 1
Follow-up # #
After DD Contracts Signed Up
0 0 0
The query is picking up the first two days, but not the rest of the week.
Why is it not competing the date sequence? This is driving me crazy as I
must produce this report every week. Thanks so much for your help.
"John Spencer" wrote:
Perhaps you could post the SQL statement that is not giving you the
expected
results.
Also post one or two rows of data that are "incorrect" and tell us what
you
think is "wrong" about the results.
Please copy and post the SQL of your query.
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
"Susan May" <SusanMay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C11E2FB4-DC8B-4C80-B698-67129808927F@xxxxxxxxxxxxxxxx
John, I'm trying what you said, but the data is coming back incorrect.
It's
not counting the right number of leads based on the date criteria. I'm
at
a
loss.
"John Spencer" wrote:
Try adding the fields to the query a second time and change the GROUP
BY
to
WHERE under the added fields. Then put your criteria there. Remove
the
criteria under any field that says GROUP By, Sum, etc.
Using the WHERE filters the data before it is aggregated (Summed,
Counted,
Averaged, Grouped, etc)
Applying criteria against a field that is aggregated means that the
criteria
is applied AFTER the aggregation. So with a boolean (true/False)
field
that
is Summed and with the criteria set to TRUE (which is -1), you would
be
returning records where the SUM of all the records in the group is
equal
to -1. Probably not going to happen very often.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
"Susan May" <SusanMay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C2C6941F-2845-4ED1-B8D9-E266949A5AA5@xxxxxxxxxxxxxxxx
Hi All:
I have a database I import from Outlook, and am keeping a list of
all
active
recruits. Each week, I need to update 10 logical fields to see how
many
more
entries were made to each logical question. What is the best way to
get
my
results. I created a query with these fields that did a sum of all
these
filds, but when I try to add to the criteria true for "Active
Recruit",
it
doesn't work; and if I put a lead date range of "Between #12/1/2007#
And
#1/31/2008#", It dosn't like that either. I like the way the sum
queries
work - it's one line with all the numbers. How do I add the
criteria
to
make
this work? I've spent days trying to figure this out and need
someone's
help.
Thanks again.
Susan
.
- Follow-Ups:
- Re: Date Range Totals for Logical Fields
- From: Susan May
- Re: Date Range Totals for Logical Fields
- References:
- Re: Date Range Totals for Logical Fields
- From: John Spencer
- Re: Date Range Totals for Logical Fields
- From: Susan May
- Re: Date Range Totals for Logical Fields
- From: John Spencer
- Re: Date Range Totals for Logical Fields
- From: Susan May
- Re: Date Range Totals for Logical Fields
- Prev by Date: Query Criteria Help
- Next by Date: Re: Year-To-Date Query Help Needed
- Previous by thread: Re: Date Range Totals for Logical Fields
- Next by thread: Re: Date Range Totals for Logical Fields
- Index(es):
Relevant Pages
|
