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 said:
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
..
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.
:
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
..
message
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!
:
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
:
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!
