Re: Query records with single Dates Automatically

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: Year-To-Date Query with a lime Twist
    ... DateSerialexpressions to explore what it actually was doing, ... enter the month and year of the report period ie 02/2007 and let it fly). ... When I copied and pasted your info into my query and replaced the ... 01/01/2007 the query does not pull the the last days of the month for ...
    (microsoft.public.access.queries)
  • Re: Query records with single Dates Automatically
    ... both queries pull from the same table. ... -- Two Tables Attendees and Visits ... Please copy and post the SQL of your query. ... I have to find a way to specify ...
    (microsoft.public.access.queries)
  • RE: Help needed with Access 2000 Reports
    ... Pull that into your query. ... the Employee ID in here] or something like that. ... It will then only pull that one record. ... Now go back to your report and change your fields to pull from this new ...
    (microsoft.public.access.reports)
  • Re: Report that pulls data from 6 tables
    ... you run the query in design mode, ... "KARL DEWEY" wrote: ... I am not the best with reports so my first attempt was to pull the ... what it is that you're attempting to display in your report. ...
    (microsoft.public.access.reports)
  • RE: Newbie question
    ... Use one query with the date criteria to pull by date. ... >> I finally made a form to input this data and can print a report of this data. ...
    (microsoft.public.access.gettingstarted)
Loading