Re: Query records with single Dates Automatically

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CE1B29F7-01CC-40C8-ADE8-1EE609EF6858@xxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:94AF2297-E60A-44CE-821F-65D540B3516E@xxxxxxxxxxxxxxxx
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!





.

Relevant Pages

  • Re: Query records with single Dates Automatically
    ... Here is the code for the Out of Town Report: ... both queries pull from the same table. ... -- Two Tables Attendees and Visits ... Please copy and post the SQL of your query. ...
    (microsoft.public.access.queries)
  • Re: Query records with single Dates Automatically
    ... "You tried to execute a query that does not include the specified expression ... "John Spencer" wrote: ... both queries pull from the same table. ... and specify that the date must equal the previous ...
    (microsoft.public.access.queries)
  • Re: Date function voodoo in my query
    ... I have an excel sheet that is linked ... pull the data, no need for a user prompt since this is all behind the scenes ... due to the data being pulled into excel automatically from this query. ... that week you'll need to specify that too, ...
    (microsoft.public.access.queries)
  • Re: Query records with single Dates Automatically
    ... You need a query that identifies visitors and the first date they visited. ... both queries pull from the same table. ... -- Two Tables Attendees and Visits ... and specify that the date must equal the previous ...
    (microsoft.public.access.queries)
  • Re: between ... and ...
    ... Access would not care that one of these dates was in the future. ... specify "between 01/01and 12/31" then it would pull all dates in that range. ... but how can i set this in a query? ...
    (microsoft.public.access.queries)
Loading