Report/Query based on two tables both on them many side.

Very nice! It shows everyone with a last visit date greater than their last
renewal date. If they don't have any payments in the payment table or a
payment was entered without a renewal date they show up also, with a renewal
date of 1/1/1900. Perfect! A little conditional formating makes the
1/1/1900 appear blank in the report to show there was no date entered or
they have no payments. Thanks for all your help.


"John Spencer" wrote in message
...
Well, good for you. You were on the right track.

Circular reference: Access doesn't like it when you name a calculated
column the
same as a field that is used in the calculation. So change as VisitDate
to
something else. Once you have done that you need to change anyplace in
the
other queries that had to previous "alias" to the new "alias".

Try this one.


SELECT tblCustomer.*, P.Renewal, V.LastVisit
FROM (tblCustomer INNER JOIN
(SELECT C.CustomerID
, Nz(Max(RenewalDate),#1/1/1900#) as Renewal
FROM tblCustomer as C Left JOIN tblPayment
ON C.CustomerId = tblPayment.CustomerID
GROUP BY C.CustomerID) as P
ON tblCustomer.CustomerID = P.CustomerID)
INNER JOIN
(SELECT CustomerID
, Max([VisitDate]) as LastVisit
FROM tblVisit
GROUP BY CustomerID) as V
ON tblCustomer.CustomerID = V.CustomerID
WHERE P.Renewal V.LastVisit

scs wrote:

Thanks John.

First I apologize for not being more descriptive and second I apologize
even
more for providing wrong information. The field I referred to as Date in
the tblVisit table is actually named VisitDate. The caption property was
just set to Date. Obviously this is going to cause some problems. When
I
run the revised code it brings up a message box asking me to enter the
parameter for Date. I attemped to fix the sql statement myself and got
two
results:

Changing , Max([Date]) as VisitDate to , Max([VisitDate]) as VisitDate
Message box says Circular reference caused by alias 'VisitDate in query
definition's SELECT list.

Changing , Max([Date]) as VisitDate to , Max(VisitDate)
Message box says Enter parameter value for V.VisitDate

I clicked cancel and choose not to save. That's what I have tried so
far.

Thanks.

"John Spencer" wrote in message
...
You did the correct thing in entering the code in the SQL window.

I did have an error in the SQL (at least one). I referred to
RenewalDate
in the
Where clause when I should have referred to Renewal.

Note that you might want to add other fields in the select clause -
such
as
P.Renewal since if the RenewalDate is null this will display 1/1/1900
and
give
you a clue that your data is bad for the relevant record.

If this still requests data be entered, then there are probably other
typing/syntax errors. IF so, please post what the query asks for.
Also,
"..doesn't seem to work" is not very descriptive.
Does that mean
--it errors out (what error),
--returns no records,
--returns the wrong records,
--returns some of the correct records
--???

Try this revised query

SELECT tblCustomer.*, P.Renewal, V.VisitDate
FROM (tblCustomer INNER JOIN
(SELECT C.CustomerID
, Nz(Max(RenewalDate),#1/1/1900#) as Renewal
FROM tblCustomer as C Left JOIN tblPayment
ON C.CustomerId = tblPayment.CustomerID
GROUP BY C.CustomerID) as P
ON tblCustomer.CustomerID = P.CustomerID)
INNER JOIN
(SELECT CustomerID
, Max([Date]) as VisitDate
FROM tblVisit
GROUP BY CustomerID) as V
ON tblCustomer.CustomerID = V.CustomerID
WHERE P.Renewal V.VisitDate


scs wrote:

Sorry John,

I'm pretty new to this. How would I enter the information you
provided.
I
tried a new query with no tables and went to sql view. I pasted in
what
you
provided. It starts asking for dates and then doesn't seem to work.
If
you
have a chance could you explain it further to me. I'm sure this is
easy
for
people that are used to working with queries and sql but I'm lost.

Thanks
Steve

"John Spencer" wrote in message
...
The following query may work for you. If Access thinks it is too
complex,
then
you will have to break it into three queries. Queries one and two
(the
two
inner
queries) would be saved and then you could use them as if they were
tables.



scs wrote:

I have the following tables in my database:

tblCustomer
CustomerID
FirstName
Etc.
-----------
tblVisit
VisitID
CustomerID
Date
Etc.
-----------
tblPayment
PaymentID
CustomerID
PaymentDate
RenewalDate
Etc.

I need a report or maybe a query first. I'm interested in finding
folks
that have visits after their last renewal date.

Each customer will have many visits and hopefully many payments.
How
can
I
create a report or a query to base the report on, which will list
people
who
are visiting past their last renewal date?

Thanks
Steve