Query records with single Dates Automatically

I am trying to figure out how to restrict a query to only pull records with 
ONE date. I work for a church, and every Monday, we run several reports on 
visitors. We send letters to first-time visitors only. In our database, each 
visit is listed. How can I setup a query to only pull records with one date 
entered, and specify that the date must equal the previous Sunday?

I have figured out how to pull records from the previous Sunday, Date()-7. 
This pulls all records dated in the last week. Our records are ONLY dated on 
Sunday, regardless which day they are entered. But this query pulls all 
records with that date, meaning that those who have visited 2, 3, 10 times 
are included with those who have attended only once. 

Thanks!
0
Utf
4/30/2007 9:36:02 PM
access.queries 6343 articles. 1 followers. Follow

9 Replies
651 Views

Similar Articles

[PageSpeed] 37

If you are getting everything in the correct date range, then the date is not 
the problem. You need to be filtering on number of visits as well.
-- 
Dave Hargis, Microsoft Access MVP


"Josiah Rocke" wrote:

> I am trying to figure out how to restrict a query to only pull records with 
> ONE date. I work for a church, and every Monday, we run several reports on 
> visitors. We send letters to first-time visitors only. In our database, each 
> visit is listed. How can I setup a query to only pull records with one date 
> entered, and specify that the date must equal the previous Sunday?
> 
> I have figured out how to pull records from the previous Sunday, Date()-7. 
> This pulls all records dated in the last week. Our records are ONLY dated on 
> Sunday, regardless which day they are entered. But this query pulls all 
> records with that date, meaning that those who have visited 2, 3, 10 times 
> are included with those who have attended only once. 
> 
> Thanks!
0
Utf
4/30/2007 9:44:06 PM
That is the problem. The number of visits is determined by the number of 
dates entered into the "VisitDate" field. It's probably not a great setup, 
but I didn't build the database. To further extrappolate:

1. We have 2 types of visitors, Local and Out of Town.
2. We have a drop down field labeled "Type", the values of 1st, 2nd, 3rd 
Visit or "Out of Town"
3. The date(s) of attendance are listed in "VisitDate"
4. The local report is easy: query Date and "1st Visit"
5. The Out of Town report has problems. I have to find a way to specify a 
date AND filter the query to records with ONE date, resulting in records with 
ONLY the previous Sunday's date.

Thanks!

"Klatuu" wrote:

> If you are getting everything in the correct date range, then the date is not 
> the problem. You need to be filtering on number of visits as well.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Josiah Rocke" wrote:
> 
> > I am trying to figure out how to restrict a query to only pull records with 
> > ONE date. I work for a church, and every Monday, we run several reports on 
> > visitors. We send letters to first-time visitors only. In our database, each 
> > visit is listed. How can I setup a query to only pull records with one date 
> > entered, and specify that the date must equal the previous Sunday?
> > 
> > I have figured out how to pull records from the previous Sunday, Date()-7. 
> > This pulls all records dated in the last week. Our records are ONLY dated on 
> > Sunday, regardless which day they are entered. But this query pulls all 
> > records with that date, meaning that those who have visited 2, 3, 10 times 
> > are included with those who have attended only once. 
> > 
> > Thanks!
0
Utf
4/30/2007 9:50:01 PM
Without knowing more details on the structure of your tables and table and 
field names - here is a possible outline of an SQL statement.

Assumptions:
-- Two Tables Attendees and Visits
-- Attendees is related to Visits by a single field

SELECT Attendees.*
FROM Attendees
WHERE Attendees.AttendeeID IN (
   SELECT Visits.AttendeeID
   FROM Visits
   WHERE VisitType = "Out of Town"
   GROUP BY VisitorID
   HAVING Min(VisitDate) =  DateAdd("d",1-Weekday(Date()),Date()))

If you post the SQL of the query you have for the Local visits, it might be 
possible to construct a specific solution.
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
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in message 
news:94AF2297-E60A-44CE-821F-65D540B3516E@microsoft.com...
> That is the problem. The number of visits is determined by the number of
> dates entered into the "VisitDate" field. It's probably not a great setup,
> but I didn't build the database. To further extrappolate:
>
> 1. We have 2 types of visitors, Local and Out of Town.
> 2. We have a drop down field labeled "Type", the values of 1st, 2nd, 3rd
> Visit or "Out of Town"
> 3. The date(s) of attendance are listed in "VisitDate"
> 4. The local report is easy: query Date and "1st Visit"
> 5. The Out of Town report has problems. I have to find a way to specify a
> date AND filter the query to records with ONE date, resulting in records 
> with
> ONLY the previous Sunday's date.
>
> Thanks!
>
> "Klatuu" wrote:
>
>> If you are getting everything in the correct date range, then the date is 
>> not
>> the problem. You need to be filtering on number of visits as well.
>> -- 
>> Dave Hargis, Microsoft Access MVP
>>
>>
>> "Josiah Rocke" wrote:
>>
>> > I am trying to figure out how to restrict a query to only pull records 
>> > with
>> > ONE date. I work for a church, and every Monday, we run several reports 
>> > on
>> > visitors. We send letters to first-time visitors only. In our database, 
>> > each
>> > visit is listed. How can I setup a query to only pull records with one 
>> > date
>> > entered, and specify that the date must equal the previous Sunday?
>> >
>> > I have figured out how to pull records from the previous Sunday, 
>> > Date()-7.
>> > This pulls all records dated in the last week. Our records are ONLY 
>> > dated on
>> > Sunday, regardless which day they are entered. But this query pulls all
>> > records with that date, meaning that those who have visited 2, 3, 10 
>> > times
>> > are included with those who have attended only once.
>> >
>> > Thanks! 


0
John
5/1/2007 12:07:27 PM
Here is the code for the Local report:

SELECT tblVisitorsReport.*, [General mailing name] & " " & [Name Last] AS 
LetterName, [City] & ", " & [State] & "  " & [Zip] AS CSZ, "Dear " & [General 
Mailing Name] & ":" AS DearLine, tblVisitorsReport.[cc type visitor] FROM 
tblVisitorsReport WHERE (((tblVisitorsReport.[cc type visitor])="Visit 1" Or 
(tblVisitorsReport.[cc type visitor])="Visit 1 New Resident") AND 
((tblVisitorsReport.[Attender type])="Local Visitor"));

Here is the code for the Out of Town Report:

SELECT tblVisitorsReport.*, [General mailing name] & " " & [Name Last] AS 
LetterName, [City] & ", " & [State] & "  " & [Zip] AS CSZ, "Dear " & [General 
Mailing Name] & ":" AS DearLine FROM tblVisitorsReport WHERE (("Only" Between 
Date() And Date()-8) AND ((tblVisitorsReport.[Attender type])="Out of Town 
Visitor"));

As you can see, both queries pull from the same table. The local 1st time 
visitors are differentiated in [cc type visitor]. The Out of Town visitors 
are not differentiated in this field. 

"John Spencer" wrote:

> Without knowing more details on the structure of your tables and table and 
> field names - here is a possible outline of an SQL statement.
> 
> Assumptions:
> -- Two Tables Attendees and Visits
> -- Attendees is related to Visits by a single field
> 
> SELECT Attendees.*
> FROM Attendees
> WHERE Attendees.AttendeeID IN (
>    SELECT Visits.AttendeeID
>    FROM Visits
>    WHERE VisitType = "Out of Town"
>    GROUP BY VisitorID
>    HAVING Min(VisitDate) =  DateAdd("d",1-Weekday(Date()),Date()))
> 
> If you post the SQL of the query you have for the Local visits, it might be 
> possible to construct a specific solution.
> 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
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in message 
> news:94AF2297-E60A-44CE-821F-65D540B3516E@microsoft.com...
> > That is the problem. The number of visits is determined by the number of
> > dates entered into the "VisitDate" field. It's probably not a great setup,
> > but I didn't build the database. To further extrappolate:
> >
> > 1. We have 2 types of visitors, Local and Out of Town.
> > 2. We have a drop down field labeled "Type", the values of 1st, 2nd, 3rd
> > Visit or "Out of Town"
> > 3. The date(s) of attendance are listed in "VisitDate"
> > 4. The local report is easy: query Date and "1st Visit"
> > 5. The Out of Town report has problems. I have to find a way to specify a
> > date AND filter the query to records with ONE date, resulting in records 
> > with
> > ONLY the previous Sunday's date.
> >
> > Thanks!
> >
> > "Klatuu" wrote:
> >
> >> If you are getting everything in the correct date range, then the date is 
> >> not
> >> the problem. You need to be filtering on number of visits as well.
> >> -- 
> >> Dave Hargis, Microsoft Access MVP
> >>
> >>
> >> "Josiah Rocke" wrote:
> >>
> >> > I am trying to figure out how to restrict a query to only pull records 
> >> > with
> >> > ONE date. I work for a church, and every Monday, we run several reports 
> >> > on
> >> > visitors. We send letters to first-time visitors only. In our database, 
> >> > each
> >> > visit is listed. How can I setup a query to only pull records with one 
> >> > date
> >> > entered, and specify that the date must equal the previous Sunday?
> >> >
> >> > I have figured out how to pull records from the previous Sunday, 
> >> > Date()-7.
> >> > This pulls all records dated in the last week. Our records are ONLY 
> >> > dated on
> >> > Sunday, regardless which day they are entered. But this query pulls all
> >> > records with that date, meaning that those who have visited 2, 3, 10 
> >> > times
> >> > are included with those who have attended only once.
> >> >
> >> > Thanks! 
> 
> 
> 
0
Utf
5/1/2007 6:22:01 PM
Sorry, not enough information.
What field specifies the date of the visit?  What field(s) uniquely 
identifies the visitor?

Let's say I visit on Easter and Christmas at my daughter's church.  What 
fields identify me?  Name First and Name Last?  Not too great as I have a 
son and a father with the same first and last names.

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in message 
news:CE1B29F7-01CC-40C8-ADE8-1EE609EF6858@microsoft.com...
> Here is the code for the Local report:
>
> SELECT tblVisitorsReport.*, [General mailing name] & " " & [Name Last] AS
> LetterName, [City] & ", " & [State] & "  " & [Zip] AS CSZ, "Dear " & 
> [General
> Mailing Name] & ":" AS DearLine, tblVisitorsReport.[cc type visitor] FROM
> tblVisitorsReport WHERE (((tblVisitorsReport.[cc type visitor])="Visit 1" 
> Or
> (tblVisitorsReport.[cc type visitor])="Visit 1 New Resident") AND
> ((tblVisitorsReport.[Attender type])="Local Visitor"));
>
> Here is the code for the Out of Town Report:
>
> SELECT tblVisitorsReport.*, [General mailing name] & " " & [Name Last] AS
> LetterName, [City] & ", " & [State] & "  " & [Zip] AS CSZ, "Dear " & 
> [General
> Mailing Name] & ":" AS DearLine FROM tblVisitorsReport WHERE (("Only" 
> Between
> Date() And Date()-8) AND ((tblVisitorsReport.[Attender type])="Out of Town
> Visitor"));
>
> As you can see, both queries pull from the same table. The local 1st time
> visitors are differentiated in [cc type visitor]. The Out of Town visitors
> are not differentiated in this field.
>
> "John Spencer" wrote:
>
>> Without knowing more details on the structure of your tables and table 
>> and
>> field names - here is a possible outline of an SQL statement.
>>
>> Assumptions:
>> -- Two Tables Attendees and Visits
>> -- Attendees is related to Visits by a single field
>>
>> SELECT Attendees.*
>> FROM Attendees
>> WHERE Attendees.AttendeeID IN (
>>    SELECT Visits.AttendeeID
>>    FROM Visits
>>    WHERE VisitType = "Out of Town"
>>    GROUP BY VisitorID
>>    HAVING Min(VisitDate) =  DateAdd("d",1-Weekday(Date()),Date()))
>>
>> If you post the SQL of the query you have for the Local visits, it might 
>> be
>> possible to construct a specific solution.
>> 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
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> ..
>>
>> "Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in message
>> news:94AF2297-E60A-44CE-821F-65D540B3516E@microsoft.com...
>> > That is the problem. The number of visits is determined by the number 
>> > of
>> > dates entered into the "VisitDate" field. It's probably not a great 
>> > setup,
>> > but I didn't build the database. To further extrappolate:
>> >
>> > 1. We have 2 types of visitors, Local and Out of Town.
>> > 2. We have a drop down field labeled "Type", the values of 1st, 2nd, 
>> > 3rd
>> > Visit or "Out of Town"
>> > 3. The date(s) of attendance are listed in "VisitDate"
>> > 4. The local report is easy: query Date and "1st Visit"
>> > 5. The Out of Town report has problems. I have to find a way to specify 
>> > a
>> > date AND filter the query to records with ONE date, resulting in 
>> > records
>> > with
>> > ONLY the previous Sunday's date.
>> >
>> > Thanks!
>> >
>> > "Klatuu" wrote:
>> >
>> >> If you are getting everything in the correct date range, then the date 
>> >> is
>> >> not
>> >> the problem. You need to be filtering on number of visits as well.
>> >> -- 
>> >> Dave Hargis, Microsoft Access MVP
>> >>
>> >>
>> >> "Josiah Rocke" wrote:
>> >>
>> >> > I am trying to figure out how to restrict a query to only pull 
>> >> > records
>> >> > with
>> >> > ONE date. I work for a church, and every Monday, we run several 
>> >> > reports
>> >> > on
>> >> > visitors. We send letters to first-time visitors only. In our 
>> >> > database,
>> >> > each
>> >> > visit is listed. How can I setup a query to only pull records with 
>> >> > one
>> >> > date
>> >> > entered, and specify that the date must equal the previous Sunday?
>> >> >
>> >> > I have figured out how to pull records from the previous Sunday,
>> >> > Date()-7.
>> >> > This pulls all records dated in the last week. Our records are ONLY
>> >> > dated on
>> >> > Sunday, regardless which day they are entered. But this query pulls 
>> >> > all
>> >> > records with that date, meaning that those who have visited 2, 3, 10
>> >> > times
>> >> > are included with those who have attended only once.
>> >> >
>> >> > Thanks!
>>
>>
>> 


0
John
5/1/2007 6:55:17 PM
The field [DateVisit] specifies the date, but it isn't used in this query. 
Both the local and out of town visitors are pulled into a separate table from 
the main database using a different query based on date that is specified by 
the user. I could post a couple screenshots if it would help. Each visitor is 
uniquely identified by [General Mailing Name] & [Name Last]. 

The query for local visitors selects unique names based upon the following 
criteria:

1. Record found in tblVisitorsReport (determined by query results = 
[VisitDate] value for specified date is preset (multiple dates possible) & 
[Attender Type] value set to "visitor", 
2. Value for [cc type visitor] is "1st Time" or "1st Time New Resident"

I'm looking into the Query for Out of Town Visitors. So far, the problem I 
have found is:

1. While [cc type visitor] is used to differentiate "1st time" local 
visitors from "2nd time" local visitors, all out of town visitors are lumped 
into "Out of Town Visitor" in this field.

I am looking for a way to add a command to the SQL statement that will pull 
the Out of Town Visitors, but filter to only include the records in the table 
with 1 date, rather than multiple dates.

"John Spencer" wrote:

> Sorry, not enough information.
> What field specifies the date of the visit?  What field(s) uniquely 
> identifies the visitor?
> 
> Let's say I visit on Easter and Christmas at my daughter's church.  What 
> fields identify me?  Name First and Name Last?  Not too great as I have a 
> son and a father with the same first and last names.
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in message 
> news:CE1B29F7-01CC-40C8-ADE8-1EE609EF6858@microsoft.com...
> > Here is the code for the Local report:
> >
> > SELECT tblVisitorsReport.*, [General mailing name] & " " & [Name Last] AS
> > LetterName, [City] & ", " & [State] & "  " & [Zip] AS CSZ, "Dear " & 
> > [General
> > Mailing Name] & ":" AS DearLine, tblVisitorsReport.[cc type visitor] FROM
> > tblVisitorsReport WHERE (((tblVisitorsReport.[cc type visitor])="Visit 1" 
> > Or
> > (tblVisitorsReport.[cc type visitor])="Visit 1 New Resident") AND
> > ((tblVisitorsReport.[Attender type])="Local Visitor"));
> >
> > Here is the code for the Out of Town Report:
> >
> > SELECT tblVisitorsReport.*, [General mailing name] & " " & [Name Last] AS
> > LetterName, [City] & ", " & [State] & "  " & [Zip] AS CSZ, "Dear " & 
> > [General
> > Mailing Name] & ":" AS DearLine FROM tblVisitorsReport WHERE (("Only" 
> > Between
> > Date() And Date()-8) AND ((tblVisitorsReport.[Attender type])="Out of Town
> > Visitor"));
> >
> > As you can see, both queries pull from the same table. The local 1st time
> > visitors are differentiated in [cc type visitor]. The Out of Town visitors
> > are not differentiated in this field.
> >
> > "John Spencer" wrote:
> >
> >> Without knowing more details on the structure of your tables and table 
> >> and
> >> field names - here is a possible outline of an SQL statement.
> >>
> >> Assumptions:
> >> -- Two Tables Attendees and Visits
> >> -- Attendees is related to Visits by a single field
> >>
> >> SELECT Attendees.*
> >> FROM Attendees
> >> WHERE Attendees.AttendeeID IN (
> >>    SELECT Visits.AttendeeID
> >>    FROM Visits
> >>    WHERE VisitType = "Out of Town"
> >>    GROUP BY VisitorID
> >>    HAVING Min(VisitDate) =  DateAdd("d",1-Weekday(Date()),Date()))
> >>
> >> If you post the SQL of the query you have for the Local visits, it might 
> >> be
> >> possible to construct a specific solution.
> >> 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
> >> Center for Health Program Development and Management
> >> University of Maryland Baltimore County
> >> ..
> >>
> >> "Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in message
> >> news:94AF2297-E60A-44CE-821F-65D540B3516E@microsoft.com...
> >> > That is the problem. The number of visits is determined by the number 
> >> > of
> >> > dates entered into the "VisitDate" field. It's probably not a great 
> >> > setup,
> >> > but I didn't build the database. To further extrappolate:
> >> >
> >> > 1. We have 2 types of visitors, Local and Out of Town.
> >> > 2. We have a drop down field labeled "Type", the values of 1st, 2nd, 
> >> > 3rd
> >> > Visit or "Out of Town"
> >> > 3. The date(s) of attendance are listed in "VisitDate"
> >> > 4. The local report is easy: query Date and "1st Visit"
> >> > 5. The Out of Town report has problems. I have to find a way to specify 
> >> > a
> >> > date AND filter the query to records with ONE date, resulting in 
> >> > records
> >> > with
> >> > ONLY the previous Sunday's date.
> >> >
> >> > Thanks!
> >> >
> >> > "Klatuu" wrote:
> >> >
> >> >> If you are getting everything in the correct date range, then the date 
> >> >> is
> >> >> not
> >> >> the problem. You need to be filtering on number of visits as well.
> >> >> -- 
> >> >> Dave Hargis, Microsoft Access MVP
> >> >>
> >> >>
> >> >> "Josiah Rocke" wrote:
> >> >>
> >> >> > I am trying to figure out how to restrict a query to only pull 
> >> >> > records
> >> >> > with
> >> >> > ONE date. I work for a church, and every Monday, we run several 
> >> >> > reports
> >> >> > on
> >> >> > visitors. We send letters to first-time visitors only. In our 
> >> >> > database,
> >> >> > each
> >> >> > visit is listed. How can I setup a query to only pull records with 
> >> >> > one
> >> >> > date
> >> >> > entered, and specify that the date must equal the previous Sunday?
> >> >> >
> >> >> > I have figured out how to pull records from the previous Sunday,
> >> >> > Date()-7.
> >> >> > This pulls all records dated in the last week. Our records are ONLY
> >> >> > dated on
> >> >> > Sunday, regardless which day they are entered. But this query pulls 
> >> >> > all
> >> >> > records with that date, meaning that those who have visited 2, 3, 10
> >> >> > times
> >> >> > are included with those who have attended only once.
> >> >> >
> >> >> > Thanks!
> >>
> >>
> >> 
> 
> 
> 
0
Utf
5/1/2007 10:04:00 PM
You need a query that identifies visitors and the first date they visited. 
That could look something like the following (depending on your table 
structure)

SELECT [General mailing name] & " " & [Name Last] AS  LetterName
, [City] & ", " & [State] & "  " & [Zip] AS CSZ
, "Dear " & [General Mailing Name] & ":" AS DearLine
, Min([Date Visit]) as FirstVisit

FROM [Some Table]

WHERE [Attender Type] = "Visitor"

GROUP BY [General mailing name] & " " & [Name Last]
, [City] & ", " & [State] & "  " & [Zip]
, "Dear " & [General Mailing Name] & ":"

HAVING Min([Date Visit]) BETWEEN Date()-7 and Date()


-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in message 
news:3D240CE4-BE1E-49DB-940F-F0896A666246@microsoft.com...
> The field [DateVisit] specifies the date, but it isn't used in this query.
> Both the local and out of town visitors are pulled into a separate table 
> from
> the main database using a different query based on date that is specified 
> by
> the user. I could post a couple screenshots if it would help. Each visitor 
> is
> uniquely identified by [General Mailing Name] & [Name Last].
>
> The query for local visitors selects unique names based upon the following
> criteria:
>
> 1. Record found in tblVisitorsReport (determined by query results =
> [VisitDate] value for specified date is preset (multiple dates possible) &
> [Attender Type] value set to "visitor",
> 2. Value for [cc type visitor] is "1st Time" or "1st Time New Resident"
>
> I'm looking into the Query for Out of Town Visitors. So far, the problem I
> have found is:
>
> 1. While [cc type visitor] is used to differentiate "1st time" local
> visitors from "2nd time" local visitors, all out of town visitors are 
> lumped
> into "Out of Town Visitor" in this field.
>
> I am looking for a way to add a command to the SQL statement that will 
> pull
> the Out of Town Visitors, but filter to only include the records in the 
> table
> with 1 date, rather than multiple dates.
>
> "John Spencer" wrote:
>
>> Sorry, not enough information.
>> What field specifies the date of the visit?  What field(s) uniquely
>> identifies the visitor?
>>
>> Let's say I visit on Easter and Christmas at my daughter's church.  What
>> fields identify me?  Name First and Name Last?  Not too great as I have a
>> son and a father with the same first and last names.
>>
>> -- 
>> John Spencer
>> Access MVP 2002-2005, 2007
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> ..
>>
>> "Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in message
>> news:CE1B29F7-01CC-40C8-ADE8-1EE609EF6858@microsoft.com...
>> > Here is the code for the Local report:
>> >
>> > SELECT tblVisitorsReport.*, [General mailing name] & " " & [Name Last] 
>> > AS
>> > LetterName, [City] & ", " & [State] & "  " & [Zip] AS CSZ, "Dear " &
>> > [General
>> > Mailing Name] & ":" AS DearLine, tblVisitorsReport.[cc type visitor] 
>> > FROM
>> > tblVisitorsReport WHERE (((tblVisitorsReport.[cc type visitor])="Visit 
>> > 1"
>> > Or
>> > (tblVisitorsReport.[cc type visitor])="Visit 1 New Resident") AND
>> > ((tblVisitorsReport.[Attender type])="Local Visitor"));
>> >
>> > Here is the code for the Out of Town Report:
>> >
>> > SELECT tblVisitorsReport.*, [General mailing name] & " " & [Name Last] 
>> > AS
>> > LetterName, [City] & ", " & [State] & "  " & [Zip] AS CSZ, "Dear " &
>> > [General
>> > Mailing Name] & ":" AS DearLine FROM tblVisitorsReport WHERE (("Only"
>> > Between
>> > Date() And Date()-8) AND ((tblVisitorsReport.[Attender type])="Out of 
>> > Town
>> > Visitor"));
>> >
>> > As you can see, both queries pull from the same table. The local 1st 
>> > time
>> > visitors are differentiated in [cc type visitor]. The Out of Town 
>> > visitors
>> > are not differentiated in this field.
>> >
>> > "John Spencer" wrote:
>> >
>> >> Without knowing more details on the structure of your tables and table
>> >> and
>> >> field names - here is a possible outline of an SQL statement.
>> >>
>> >> Assumptions:
>> >> -- Two Tables Attendees and Visits
>> >> -- Attendees is related to Visits by a single field
>> >>
>> >> SELECT Attendees.*
>> >> FROM Attendees
>> >> WHERE Attendees.AttendeeID IN (
>> >>    SELECT Visits.AttendeeID
>> >>    FROM Visits
>> >>    WHERE VisitType = "Out of Town"
>> >>    GROUP BY VisitorID
>> >>    HAVING Min(VisitDate) =  DateAdd("d",1-Weekday(Date()),Date()))
>> >>
>> >> If you post the SQL of the query you have for the Local visits, it 
>> >> might
>> >> be
>> >> possible to construct a specific solution.
>> >> 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
>> >> Center for Health Program Development and Management
>> >> University of Maryland Baltimore County
>> >> ..
>> >>
>> >> "Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in 
>> >> message
>> >> news:94AF2297-E60A-44CE-821F-65D540B3516E@microsoft.com...
>> >> > That is the problem. The number of visits is determined by the 
>> >> > number
>> >> > of
>> >> > dates entered into the "VisitDate" field. It's probably not a great
>> >> > setup,
>> >> > but I didn't build the database. To further extrappolate:
>> >> >
>> >> > 1. We have 2 types of visitors, Local and Out of Town.
>> >> > 2. We have a drop down field labeled "Type", the values of 1st, 2nd,
>> >> > 3rd
>> >> > Visit or "Out of Town"
>> >> > 3. The date(s) of attendance are listed in "VisitDate"
>> >> > 4. The local report is easy: query Date and "1st Visit"
>> >> > 5. The Out of Town report has problems. I have to find a way to 
>> >> > specify
>> >> > a
>> >> > date AND filter the query to records with ONE date, resulting in
>> >> > records
>> >> > with
>> >> > ONLY the previous Sunday's date.
>> >> >
>> >> > Thanks!
>> >> >
>> >> > "Klatuu" wrote:
>> >> >
>> >> >> If you are getting everything in the correct date range, then the 
>> >> >> date
>> >> >> is
>> >> >> not
>> >> >> the problem. You need to be filtering on number of visits as well.
>> >> >> -- 
>> >> >> Dave Hargis, Microsoft Access MVP
>> >> >>
>> >> >>
>> >> >> "Josiah Rocke" wrote:
>> >> >>
>> >> >> > I am trying to figure out how to restrict a query to only pull
>> >> >> > records
>> >> >> > with
>> >> >> > ONE date. I work for a church, and every Monday, we run several
>> >> >> > reports
>> >> >> > on
>> >> >> > visitors. We send letters to first-time visitors only. In our
>> >> >> > database,
>> >> >> > each
>> >> >> > visit is listed. How can I setup a query to only pull records 
>> >> >> > with
>> >> >> > one
>> >> >> > date
>> >> >> > entered, and specify that the date must equal the previous 
>> >> >> > Sunday?
>> >> >> >
>> >> >> > I have figured out how to pull records from the previous Sunday,
>> >> >> > Date()-7.
>> >> >> > This pulls all records dated in the last week. Our records are 
>> >> >> > ONLY
>> >> >> > dated on
>> >> >> > Sunday, regardless which day they are entered. But this query 
>> >> >> > pulls
>> >> >> > all
>> >> >> > records with that date, meaning that those who have visited 2, 3, 
>> >> >> > 10
>> >> >> > times
>> >> >> > are included with those who have attended only once.
>> >> >> >
>> >> >> > Thanks!
>> >>
>> >>
>> >>
>>
>>
>> 


0
John
5/2/2007 1:21:02 PM
Here is what I have:

SELECT tblVisitorsReport.*, [General mailing name] & " " & [Name Last] AS 
LetterName, [City] & ", " & [State] & "  " & [Zip] AS CSZ, "Dear " & [General 
Mailing Name] & ":" AS DearLine, tblVisitorsReport.VisitDate FROM 
tblVisitorsReport WHERE (((tblVisitorsReport.[Attender type])="Out of Town 
Visitor")) HAVING Min(tblVisitorsReport.[VisitDate]) Between Date()-7 And 
Date();

This is the error I get:

"You tried to execute a query that does not include the specified expression 
'ProfileID' as part of an aggregate function."

Any ideas?

"John Spencer" wrote:

> You need a query that identifies visitors and the first date they visited. 
> That could look something like the following (depending on your table 
> structure)
> 
> SELECT [General mailing name] & " " & [Name Last] AS  LetterName
> , [City] & ", " & [State] & "  " & [Zip] AS CSZ
> , "Dear " & [General Mailing Name] & ":" AS DearLine
> , Min([Date Visit]) as FirstVisit
> 
> FROM [Some Table]
> 
> WHERE [Attender Type] = "Visitor"
> 
> GROUP BY [General mailing name] & " " & [Name Last]
> , [City] & ", " & [State] & "  " & [Zip]
> , "Dear " & [General Mailing Name] & ":"
> 
> HAVING Min([Date Visit]) BETWEEN Date()-7 and Date()
> 
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in message 
> news:3D240CE4-BE1E-49DB-940F-F0896A666246@microsoft.com...
> > The field [DateVisit] specifies the date, but it isn't used in this query.
> > Both the local and out of town visitors are pulled into a separate table 
> > from
> > the main database using a different query based on date that is specified 
> > by
> > the user. I could post a couple screenshots if it would help. Each visitor 
> > is
> > uniquely identified by [General Mailing Name] & [Name Last].
> >
> > The query for local visitors selects unique names based upon the following
> > criteria:
> >
> > 1. Record found in tblVisitorsReport (determined by query results =
> > [VisitDate] value for specified date is preset (multiple dates possible) &
> > [Attender Type] value set to "visitor",
> > 2. Value for [cc type visitor] is "1st Time" or "1st Time New Resident"
> >
> > I'm looking into the Query for Out of Town Visitors. So far, the problem I
> > have found is:
> >
> > 1. While [cc type visitor] is used to differentiate "1st time" local
> > visitors from "2nd time" local visitors, all out of town visitors are 
> > lumped
> > into "Out of Town Visitor" in this field.
> >
> > I am looking for a way to add a command to the SQL statement that will 
> > pull
> > the Out of Town Visitors, but filter to only include the records in the 
> > table
> > with 1 date, rather than multiple dates.
> >
> > "John Spencer" wrote:
> >
> >> Sorry, not enough information.
> >> What field specifies the date of the visit?  What field(s) uniquely
> >> identifies the visitor?
> >>
> >> Let's say I visit on Easter and Christmas at my daughter's church.  What
> >> fields identify me?  Name First and Name Last?  Not too great as I have a
> >> son and a father with the same first and last names.
> >>
> >> -- 
> >> John Spencer
> >> Access MVP 2002-2005, 2007
> >> Center for Health Program Development and Management
> >> University of Maryland Baltimore County
> >> ..
> >>
> >> "Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in message
> >> news:CE1B29F7-01CC-40C8-ADE8-1EE609EF6858@microsoft.com...
> >> > Here is the code for the Local report:
> >> >
> >> > SELECT tblVisitorsReport.*, [General mailing name] & " " & [Name Last] 
> >> > AS
> >> > LetterName, [City] & ", " & [State] & "  " & [Zip] AS CSZ, "Dear " &
> >> > [General
> >> > Mailing Name] & ":" AS DearLine, tblVisitorsReport.[cc type visitor] 
> >> > FROM
> >> > tblVisitorsReport WHERE (((tblVisitorsReport.[cc type visitor])="Visit 
> >> > 1"
> >> > Or
> >> > (tblVisitorsReport.[cc type visitor])="Visit 1 New Resident") AND
> >> > ((tblVisitorsReport.[Attender type])="Local Visitor"));
> >> >
> >> > Here is the code for the Out of Town Report:
> >> >
> >> > SELECT tblVisitorsReport.*, [General mailing name] & " " & [Name Last] 
> >> > AS
> >> > LetterName, [City] & ", " & [State] & "  " & [Zip] AS CSZ, "Dear " &
> >> > [General
> >> > Mailing Name] & ":" AS DearLine FROM tblVisitorsReport WHERE (("Only"
> >> > Between
> >> > Date() And Date()-8) AND ((tblVisitorsReport.[Attender type])="Out of 
> >> > Town
> >> > Visitor"));
> >> >
> >> > As you can see, both queries pull from the same table. The local 1st 
> >> > time
> >> > visitors are differentiated in [cc type visitor]. The Out of Town 
> >> > visitors
> >> > are not differentiated in this field.
> >> >
> >> > "John Spencer" wrote:
> >> >
> >> >> Without knowing more details on the structure of your tables and table
> >> >> and
> >> >> field names - here is a possible outline of an SQL statement.
> >> >>
> >> >> Assumptions:
> >> >> -- Two Tables Attendees and Visits
> >> >> -- Attendees is related to Visits by a single field
> >> >>
> >> >> SELECT Attendees.*
> >> >> FROM Attendees
> >> >> WHERE Attendees.AttendeeID IN (
> >> >>    SELECT Visits.AttendeeID
> >> >>    FROM Visits
> >> >>    WHERE VisitType = "Out of Town"
> >> >>    GROUP BY VisitorID
> >> >>    HAVING Min(VisitDate) =  DateAdd("d",1-Weekday(Date()),Date()))
> >> >>
> >> >> If you post the SQL of the query you have for the Local visits, it 
> >> >> might
> >> >> be
> >> >> possible to construct a specific solution.
> >> >> 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
> >> >> Center for Health Program Development and Management
> >> >> University of Maryland Baltimore County
> >> >> ..
> >> >>
> >> >> "Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in 
> >> >> message
> >> >> news:94AF2297-E60A-44CE-821F-65D540B3516E@microsoft.com...
> >> >> > That is the problem. The number of visits is determined by the 
> >> >> > number
> >> >> > of
> >> >> > dates entered into the "VisitDate" field. It's probably not a great
> >> >> > setup,
> >> >> > but I didn't build the database. To further extrappolate:
> >> >> >
> >> >> > 1. We have 2 types of visitors, Local and Out of Town.
> >> >> > 2. We have a drop down field labeled "Type", the values of 1st, 2nd,
> >> >> > 3rd
> >> >> > Visit or "Out of Town"
> >> >> > 3. The date(s) of attendance are listed in "VisitDate"
> >> >> > 4. The local report is easy: query Date and "1st Visit"
> >> >> > 5. The Out of Town report has problems. I have to find a way to 
> >> >> > specify
> >> >> > a
> >> >> > date AND filter the query to records with ONE date, resulting in
> >> >> > records
> >> >> > with
> >> >> > ONLY the previous Sunday's date.
> >> >> >
> >> >> > Thanks!
> >> >> >
> >> >> > "Klatuu" wrote:
> >> >> >
> >> >> >> If you are getting everything in the correct date range, then the 
> >> >> >> date
> >> >> >> is
> >> >> >> not
> >> >> >> the problem. You need to be filtering on number of visits as well.
> >> >> >> -- 
> >> >> >> Dave Hargis, Microsoft Access MVP
> >> >> >>
> >> >> >>
> >> >> >> "Josiah Rocke" wrote:
> >> >> >>
> >> >> >> > I am trying to figure out how to restrict a query to only pull
> >> >> >> > records
> >> >> >> > with
> >> >> >> > ONE date. I work for a church, and every Monday, we run several
> >> >> >> > reports
> >> >> >> > on
> >> >> >> > visitors. We send letters to first-time visitors only. In our
> >> >> >> > database,
> >> >> >> > each
> >> >> >> > visit is listed. How can I setup a query to only pull records 
> >> >> >> > with
> >> >> >> > one
> >> >> >> > date
> >> >> >> > entered, and specify that the date must equal the previous 
> >> >> >> > Sunday?
> >> >> >> >
> >> >> >> > I have figured out how to pull records from the previous Sunday,
> >> >> >> > Date()-7.
> >> >> >> > This pulls all records dated in the last week. Our records are 
> >> >> >> > ONLY
> >> >> >> > dated on
> >> >> >> > Sunday, regardless which day they are entered. But this query 
> >> >> >> > pulls
> >> >> >> > all
> >> >> >> > records with that date, meaning that those who have visited 2, 3, 
> >> >> >> > 10
> >> >> >> > times
> >> >> >> > are included with those who have attended only once.
> >> >> >> >
> >> >> >> > Thanks!
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Utf
5/2/2007 4:13:01 PM
You have included the entire table tblVisitorsReport in the query.  Try 
removing tblVisitorsReport.* from the SELECT clause.  If you need any other 
fields then add them in individually..  Also your posted query DOES NOT show 
any group by.

Try this this
-- OPEN  a new query
-- Select TblVisitorsReport as the source
-- Select View Totals from the menu
-- add the fields you want to see into the query
-- add VisitDate to the query and change Group By to Min
    set Criteria under Visit Date to Between Date()-7 and Date()
-- add Attender Type and change Group by to WHERE
   set criteria under Attender type to "out of Town"

If that works then you can combine your fields as you want them.

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in message 
news:1B7BEF61-EAAF-4149-887A-5A0F1DB5AC28@microsoft.com...
> Here is what I have:
>
> SELECT tblVisitorsReport.*, [General mailing name] & " " & [Name Last] AS
> LetterName, [City] & ", " & [State] & "  " & [Zip] AS CSZ, "Dear " & 
> [General
> Mailing Name] & ":" AS DearLine, tblVisitorsReport.VisitDate FROM
> tblVisitorsReport WHERE (((tblVisitorsReport.[Attender type])="Out of Town
> Visitor")) HAVING Min(tblVisitorsReport.[VisitDate]) Between Date()-7 And
> Date();
>
> This is the error I get:
>
> "You tried to execute a query that does not include the specified 
> expression
> 'ProfileID' as part of an aggregate function."
>
> Any ideas?
>
> "John Spencer" wrote:
>
>> You need a query that identifies visitors and the first date they 
>> visited.
>> That could look something like the following (depending on your table
>> structure)
>>
>> SELECT [General mailing name] & " " & [Name Last] AS  LetterName
>> , [City] & ", " & [State] & "  " & [Zip] AS CSZ
>> , "Dear " & [General Mailing Name] & ":" AS DearLine
>> , Min([Date Visit]) as FirstVisit
>>
>> FROM [Some Table]
>>
>> WHERE [Attender Type] = "Visitor"
>>
>> GROUP BY [General mailing name] & " " & [Name Last]
>> , [City] & ", " & [State] & "  " & [Zip]
>> , "Dear " & [General Mailing Name] & ":"
>>
>> HAVING Min([Date Visit]) BETWEEN Date()-7 and Date()
>>
>>
>> -- 
>> John Spencer
>> Access MVP 2002-2005, 2007
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> ..
>>
>> "Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in message
>> news:3D240CE4-BE1E-49DB-940F-F0896A666246@microsoft.com...
>> > The field [DateVisit] specifies the date, but it isn't used in this 
>> > query.
>> > Both the local and out of town visitors are pulled into a separate 
>> > table
>> > from
>> > the main database using a different query based on date that is 
>> > specified
>> > by
>> > the user. I could post a couple screenshots if it would help. Each 
>> > visitor
>> > is
>> > uniquely identified by [General Mailing Name] & [Name Last].
>> >
>> > The query for local visitors selects unique names based upon the 
>> > following
>> > criteria:
>> >
>> > 1. Record found in tblVisitorsReport (determined by query results =
>> > [VisitDate] value for specified date is preset (multiple dates 
>> > possible) &
>> > [Attender Type] value set to "visitor",
>> > 2. Value for [cc type visitor] is "1st Time" or "1st Time New Resident"
>> >
>> > I'm looking into the Query for Out of Town Visitors. So far, the 
>> > problem I
>> > have found is:
>> >
>> > 1. While [cc type visitor] is used to differentiate "1st time" local
>> > visitors from "2nd time" local visitors, all out of town visitors are
>> > lumped
>> > into "Out of Town Visitor" in this field.
>> >
>> > I am looking for a way to add a command to the SQL statement that will
>> > pull
>> > the Out of Town Visitors, but filter to only include the records in the
>> > table
>> > with 1 date, rather than multiple dates.
>> >
>> > "John Spencer" wrote:
>> >
>> >> Sorry, not enough information.
>> >> What field specifies the date of the visit?  What field(s) uniquely
>> >> identifies the visitor?
>> >>
>> >> Let's say I visit on Easter and Christmas at my daughter's church. 
>> >> What
>> >> fields identify me?  Name First and Name Last?  Not too great as I 
>> >> have a
>> >> son and a father with the same first and last names.
>> >>
>> >> -- 
>> >> John Spencer
>> >> Access MVP 2002-2005, 2007
>> >> Center for Health Program Development and Management
>> >> University of Maryland Baltimore County
>> >> ..
>> >>
>> >> "Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in 
>> >> message
>> >> news:CE1B29F7-01CC-40C8-ADE8-1EE609EF6858@microsoft.com...
>> >> > Here is the code for the Local report:
>> >> >
>> >> > SELECT tblVisitorsReport.*, [General mailing name] & " " & [Name 
>> >> > Last]
>> >> > AS
>> >> > LetterName, [City] & ", " & [State] & "  " & [Zip] AS CSZ, "Dear " &
>> >> > [General
>> >> > Mailing Name] & ":" AS DearLine, tblVisitorsReport.[cc type visitor]
>> >> > FROM
>> >> > tblVisitorsReport WHERE (((tblVisitorsReport.[cc type 
>> >> > visitor])="Visit
>> >> > 1"
>> >> > Or
>> >> > (tblVisitorsReport.[cc type visitor])="Visit 1 New Resident") AND
>> >> > ((tblVisitorsReport.[Attender type])="Local Visitor"));
>> >> >
>> >> > Here is the code for the Out of Town Report:
>> >> >
>> >> > SELECT tblVisitorsReport.*, [General mailing name] & " " & [Name 
>> >> > Last]
>> >> > AS
>> >> > LetterName, [City] & ", " & [State] & "  " & [Zip] AS CSZ, "Dear " &
>> >> > [General
>> >> > Mailing Name] & ":" AS DearLine FROM tblVisitorsReport WHERE 
>> >> > (("Only"
>> >> > Between
>> >> > Date() And Date()-8) AND ((tblVisitorsReport.[Attender type])="Out 
>> >> > of
>> >> > Town
>> >> > Visitor"));
>> >> >
>> >> > As you can see, both queries pull from the same table. The local 1st
>> >> > time
>> >> > visitors are differentiated in [cc type visitor]. The Out of Town
>> >> > visitors
>> >> > are not differentiated in this field.
>> >> >
>> >> > "John Spencer" wrote:
>> >> >
>> >> >> Without knowing more details on the structure of your tables and 
>> >> >> table
>> >> >> and
>> >> >> field names - here is a possible outline of an SQL statement.
>> >> >>
>> >> >> Assumptions:
>> >> >> -- Two Tables Attendees and Visits
>> >> >> -- Attendees is related to Visits by a single field
>> >> >>
>> >> >> SELECT Attendees.*
>> >> >> FROM Attendees
>> >> >> WHERE Attendees.AttendeeID IN (
>> >> >>    SELECT Visits.AttendeeID
>> >> >>    FROM Visits
>> >> >>    WHERE VisitType = "Out of Town"
>> >> >>    GROUP BY VisitorID
>> >> >>    HAVING Min(VisitDate) =  DateAdd("d",1-Weekday(Date()),Date()))
>> >> >>
>> >> >> If you post the SQL of the query you have for the Local visits, it
>> >> >> might
>> >> >> be
>> >> >> possible to construct a specific solution.
>> >> >> 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
>> >> >> Center for Health Program Development and Management
>> >> >> University of Maryland Baltimore County
>> >> >> ..
>> >> >>
>> >> >> "Josiah Rocke" <JosiahRocke@discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:94AF2297-E60A-44CE-821F-65D540B3516E@microsoft.com...
>> >> >> > That is the problem. The number of visits is determined by the
>> >> >> > number
>> >> >> > of
>> >> >> > dates entered into the "VisitDate" field. It's probably not a 
>> >> >> > great
>> >> >> > setup,
>> >> >> > but I didn't build the database. To further extrappolate:
>> >> >> >
>> >> >> > 1. We have 2 types of visitors, Local and Out of Town.
>> >> >> > 2. We have a drop down field labeled "Type", the values of 1st, 
>> >> >> > 2nd,
>> >> >> > 3rd
>> >> >> > Visit or "Out of Town"
>> >> >> > 3. The date(s) of attendance are listed in "VisitDate"
>> >> >> > 4. The local report is easy: query Date and "1st Visit"
>> >> >> > 5. The Out of Town report has problems. I have to find a way to
>> >> >> > specify
>> >> >> > a
>> >> >> > date AND filter the query to records with ONE date, resulting in
>> >> >> > records
>> >> >> > with
>> >> >> > ONLY the previous Sunday's date.
>> >> >> >
>> >> >> > Thanks!
>> >> >> >
>> >> >> > "Klatuu" wrote:
>> >> >> >
>> >> >> >> If you are getting everything in the correct date range, then 
>> >> >> >> the
>> >> >> >> date
>> >> >> >> is
>> >> >> >> not
>> >> >> >> the problem. You need to be filtering on number of visits as 
>> >> >> >> well.
>> >> >> >> -- 
>> >> >> >> Dave Hargis, Microsoft Access MVP
>> >> >> >>
>> >> >> >>
>> >> >> >> "Josiah Rocke" wrote:
>> >> >> >>
>> >> >> >> > I am trying to figure out how to restrict a query to only pull
>> >> >> >> > records
>> >> >> >> > with
>> >> >> >> > ONE date. I work for a church, and every Monday, we run 
>> >> >> >> > several
>> >> >> >> > reports
>> >> >> >> > on
>> >> >> >> > visitors. We send letters to first-time visitors only. In our
>> >> >> >> > database,
>> >> >> >> > each
>> >> >> >> > visit is listed. How can I setup a query to only pull records
>> >> >> >> > with
>> >> >> >> > one
>> >> >> >> > date
>> >> >> >> > entered, and specify that the date must equal the previous
>> >> >> >> > Sunday?
>> >> >> >> >
>> >> >> >> > I have figured out how to pull records from the previous 
>> >> >> >> > Sunday,
>> >> >> >> > Date()-7.
>> >> >> >> > This pulls all records dated in the last week. Our records are
>> >> >> >> > ONLY
>> >> >> >> > dated on
>> >> >> >> > Sunday, regardless which day they are entered. But this query
>> >> >> >> > pulls
>> >> >> >> > all
>> >> >> >> > records with that date, meaning that those who have visited 2, 
>> >> >> >> > 3,
>> >> >> >> > 10
>> >> >> >> > times
>> >> >> >> > are included with those who have attended only once.
>> >> >> >> >
>> >> >> >> > Thanks!
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
John
5/2/2007 4:58:00 PM
Reply:

Similar Artilces:

Outlook 2003 Automatically put people
Hi, With Outlook 2003, is it possible to have the same function which was into Outlook Express : "Automatically put people I reply to in my Adress Book" Great Thanks ! Not natively; to automatically add the email address of people you respond to to your Contacts Folder requires code: http://www.outlookcode.com/d/code/autoaddrecip.htm To automatically add recipients to Contacts in Outlook using VBA http://www.slipstick.com/contacts/addauto.htm To add addresses automatically Hal -- Hal Hostetler, CPBE -- hhh@kvoa.com Senior Engineer/MIS -- MS MVP-Print/Imaging -- WA7BGX http://ww...

Date entry
How can I enter a date without having to type a "/" between the month, day, and year? magstate, have a look here http://www.cpearson.com/excel/DateTimeEntry.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "magstate" <magstate@discussions.microsoft.com> wrote in message news:41CD9BD7-1836-447E-8811-32EE53D8EA8C@microsoft.com... > How can I enter a date without having to type a "/" between the...

Data query drive error
After running a sql qeury in excel 2002, receive a message error with "\" character and the help info box stating "driver error". ...

Open a Contact Record From a Task Using Code
Hi All, I have tasks which are regarding Cases which in turn are linked to contacts. These tasks MUST be regarding cases. Then, when i close the task and a certain check box is not check i need to open the contact record that is the customer for the case associated with the task. Before opening the contact record i need to have an alert " Would you like to update the contact record " yes/no, if yes then open it otherwise save and close the task. I know i need to use window.open() but i'm not sure how to pass the right parameters in order to get the right contact record. A...

IIF function help in query
Hello, I have the following field in the query to look at a field PartDesc, if any of the text have a *BUSH* word, return yes, otherwise no. BUSH: IIF(PartDesc= Like *BUSH*, "yes","no") Why the Like *BUSH* not working? Thanks Because = and LIKE are two operators, a little bit like: 4 * / 3 would mean what (multiply followed by divide)? Probably better to remove the =, in this case: BUSH: IIF(PartDesc Like *BUSH*, "yes","no") Vanderghast, Access MVP "Cam" <Cam@discussions.microsoft.com> wrote in ...

Windows Live Mail "Received Date" issue
Hi, I have recently decided to move my Google Apps business email account to Office Live. The only way I have found to 'migrate' my emails to my Office Live email account is by dragging and dropping the emails from "GMail" to my Office Live account using the WLM GUI. WLM (GUI and Web) will display an incorrect received date - which is the migration date of when I have subscribed to Google Apps email. The only way I have found to show a correct date is to display the "sent" column in WLM GUI. Is there a way to correct this ? Maybe there is a ...

Automatic Graphs/ Dynamic Graphs
I have a list of test results, one row for each participant. (Each result is in a seperate column) I need to make a graph of all of their results when I click on their names. Obvously I cannot create a graph for each one, as there could be hundreds of participants, I need it to create a graph using relevent cell references for each person on the fly. I have seen this done in the past, but am unsure on how to accomplish this myself. Thankyou for any help you can give me Dan Hi, Maybe you used one of these sites previous to get your information. http://peltiertech.com/Excel/Charts/Dy...

Simple question: Difference between two dates?
Suppose cell A2 has 6/27/05 - 7/1/05 A3 has 7/4/05 - 7/8/05... and so I need to know the formula to coomputer the number of days betwee those dates, which is obviously 5. The reason it need to keep the rang in one cell is this will be used in a VLookup function. Thanks -- dstoc ----------------------------------------------------------------------- dstock's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2422 View this thread: http://www.excelforum.com/showthread.php?threadid=38132 Anyway you slice it, you have to get the 2 dates seperated... So you can have ...

Query 12-27-07
I have a query which gathers a product list from an imported ship history table. To do this I use a group by on [ProdID] and Max on [ShipDate] and [UnitPrice]. My problem is this makes a list where the last Ship Date is obtained and the highest price is listed. Instead I would like it to list the Unit Price associated with the Ship Date (ie. the last shipment price) How would I do this? -- TIA Hi, you need the Max on [ShipDate] without the Max on [UnitPrice]. You might find it easier to get the Max of [ShipDate] then use that to look up the [UnitPrice] for all products for that date...

Sum products prices that are between two dates
Hi all, I was wondering if someone could tell me what the formula would be if I wanted to add the prices of products that were purchased between two dates. If column A was formatted with the dates between 01/01/06 and 31/01/06 and column B with the prices. Thanks in advance Maddoktor Hi Maddoktor In cell D1, enter the Start date required in cell E1 enter the end date required. In F1 =SUMPRODUCT(--($A$1:$A$100>=D1),--($A$1:$A$100<=E1),$B$1:$B$100) Change the ranges to suit. -- Regards Roger Govier "Maddoktor" <maddoktor@clinic.com> wrote in message news:e1v...

Query criteria 02-02-08
I am looking for a way to format a criteria line to search for a specific character within a group of letters/numbers. Specifically, I am working with 17 digit automotive VIN #'s (Vehicle Identification Numbers) and need to create hits on all VIN's within a table that have a specific character at a specific location within the VIN - 8th character needs to be either a 2, 8 or V. The seven preceding characters are either letters or numbers (in no defined sequence). I know I can use an * after the eighth character as a wildcard. Thanks in advance..... On Sat, 2 Feb 2008 09:47:01 ...

Duplicate Queries
I am using Access 2007 query wizards to do the following, but seem to be unable to take it one step further. In the Wizard, I identify 3 columns if they are duplicated to give me the results No problem with this. But I would like to add a fourth column to this query ONLY if this fourth column does not Match itself. Hope I am making this clear enough. Thanks -- Tom On Fri, 15 Jan 2010 08:23:03 -0800, Tom <Tom@discussions.microsoft.com> wrote: >I am using Access 2007 query wizards to do the following, but seem to be >unable to take it one step further. >...

New email opens automatically when computer is idle
Does anyone have any idea why this is happening: When outlook is OPEN and it seems like when the screen saver is running or computer is idle (monitor turns off using windows power saving configuration) and an email is open. When new email comes in, it will automatically open it. No settings have been changed from the installation (not that im aware of) - Auto archive and messenger was turned off (un checked). When the computer is in use and outlook is open, this does not happen. It seems to happen only when the computer is idle and or the screensaver is running. I have never herd of this...

EXPERT HELP REQUIRED
Thanks very much.....all sorted now. I did have some playing around to do but it works, thats the main thin and will be a very useful function in future -- Esson ----------------------------------------------------------------------- Essonc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1465 View this thread: http://www.excelforum.com/showthread.php?threadid=26273 ...

Addenda Records for EFT Payment
I am using GP 9 with eBanking. Is there a way to add addenda records for the EFT file generated for vendor payments? Thanks. How much information are you wanting to get in the addenda record? "GSL" wrote: > I am using GP 9 with eBanking. Is there a way to add addenda records for the > EFT file generated for vendor payments? Thanks. Thank you for getting back to me. We upgraded to GP 10 and it has the function to do addenda recores for EFT payment. Thanks again. "Leslie Vail" wrote: > How much information are you wanting to get in the addenda record?...

Automatic Chart Expansion Problem
For years, I've be charting temperature variations. As I added new data points, the chart automatically expanded to accomdate the new datum. At 72 data points, the chart stopped expanding automatically. Anyone know if this is a standard limit to expanding charts? Did I accidently turn some feature off? Is there a way to fix this? I'm running MS Win and Office XP with all updates. Thanks Mike Hi, It really depends on how your expansion was working. Is your chart set to a fixed data range with 72 rows or columns that you have now filled up? If so you just need to extend the ra...

Flagging up dates in cells #2
Cheers Alex All sorted now -- midase ------------------------------------------------------------------------ midase's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15863 View this thread: http://www.excelforum.com/showthread.php?threadid=273554 ...

How do I sort by date (not days, weeks, months) in Excel 2000?
Hi Tony not sure what you're asking here, if you could type out say five lines of your data and then what you want to see, i'm sure we'll be able to help (please, however, do not attach a workbook) Regards julieD "Tony" <Tony@discussions.microsoft.com> wrote in message news:C78C1E27-F4A9-4DB4-BA16-EBACE47042A5@microsoft.com... > ...

I cannot change the date format to English canada
Hi- if you'd like to change the format of one cell, select the cell and reformat it. After selecting the cell, go tot he format menu, choose the first option: cells. From the pop-up form, goto the Number tab (should be the first one)- in the category section- choose date, in the locale section choose English(Canada)- then choose the format in the Type section. choose OK. It should work. If this is not what you ment, please let me know. Also, indicate what you've tried and why it hasn't worked- Good Luck! pim -- Pim ---------------------------------------------------------...

averaging a value between two serial dates
Hi all, I have two spreadsheets. The first spreadsheet contains two columns, one for start date/time (in serial format) and the second for stop date/time (in serial format). The second spreadsheet contains two columns. One containing a serial date/time and the other containing a meteorological value (eg. temperature). I want Excel to read the start and stop date/time from the first spreadhseet and then search the second spreadsheet for all temperature values which were recorded during this time frame. Lastly, I want Excel to average these values. Is this possible using a single Excel func...

Button to Append then Open Form to Record
I am trying to create a button that appends the TrackingID field to my table "tblContractPOTracking" then opens form "frmContractPOTracking" to the TrackingID that was just appended. Does that make sense? What doe would i enter and where? the Private Sub Command31_Click() On Error GoTo Err_Command31_Click Dim stDocName As String stDocName = "AppendTrackingId" DoCmd.OpenQuery stDocName, acNormal, acEdit Exit_Command31_Click: Exit Sub ...

Choosing records to print based on latest revision
I have a training matrix that has the following fields: Procedure Employee Rev Date trained There can be multiple revisions for the same procedure. I would like to print a report by procedure that only shows the latest rev number. How would I do this? Thanks! "madmckenna" <madmckenna@discussions.microsoft.com> wrote in message news:05FAE845-9000-49D4-AF3F-CD05B199184D@microsoft.com... >I have a training matrix that has the following fields: > Procedure > Employee > Rev > Date trained > > There can be multiple revi...

mdf file date wrong
I hvae a client that has the log and the data file in the same place for years. Same drive, same folder, etc. )I know it's a good idea to put the log elsewhere but it's not necessary here). Anyway, my question is regarding the date of the mdf file. THis is in SQL 2000 running on Windows Server 2003. Everything is up to date. We're moving things over to SQL 2005 so I was going to do some testing last night when I noticed the date on the log file was 1/10/2010 but the date on the data file is 11/15/2009 11:57 AM. Their office has been up and running this morning for a f...

Date Formula Needed-Business Days
I need a formula that calculates business days, but the NETWORKDAYS function is not adequate. I need the formula to return the number of days between two dates, excluding a range of holidays. Since my store is open seven days a week, the NETWORKDAYS function will not suffice. When I used Quattro Pro in the past, it had a BDAY function, and it accepted parameters to indicate if weedends were to be included or not. Is there a comparable function in Excel? I am trying to calculate the number of days the store is open every week. I thought of using NETWORKDAYS and simply adding 2 (Satu...

InputBox within a query
I have multi records and I want a query where an input box asks for one column record. Column 1 Column 2 Column 3 data place port day plant box I want an input box for Column 1 so if I type day into the box all the data from Column 2 and Column 3 and Column 1 display in a query for me to use in a report and/or form In the criteria for Column1, just type something like: [Please Enter Parameter Value] and the query will prompt for the criteria for Column1 and will only return records that match what the user typed in. But i want to enter the...