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

[scs]

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

 

[Jeff Boyce]

So, as a first step, you want to find CustomerIDs with a VisitDate that
comes after a RenewalDate (by the way, you really want to change the [Date]
field name in tblVisit -- "Date" is a reserved word in Access).

A query against tblPayment would give you a way to get the "last"
RenewalDate for each customer (who has a renewal date -- are you only
interested in those with RenewalDates, or could a customer not yet have
one?). You'd use a Totals query to get that, GroupBy CustomerID, Max of
RenewalDate.

A similar query against tblVisit would give you a way to get the "last"
visit.

A third query, joining the first two queries, gives you a way to get
CustomerID for customers where "last" VisitDate is greater than "last"
RenewalDate.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[scs]

Thanks very much for your help. It is possible that a customer could have a
visit without any renewal date. I'd suren need to know about it. I assume
I'll have to make the query for latest RenewalDate include Nulls? hmmm. Or
would I make a fourth query to extract RenewalDate Nulls? I'll play with
it.
Thanks again!

So, as a first step, you want to find CustomerIDs with a VisitDate that
comes after a RenewalDate (by the way, you really want to change the
[Date]
field name in tblVisit -- "Date" is a reserved word in Access).

A query against tblPayment would give you a way to get the "last"
RenewalDate for each customer (who has a renewal date -- are you only
interested in those with RenewalDates, or could a customer not yet have
one?). You'd use a Totals query to get that, GroupBy CustomerID, Max of
RenewalDate.

A similar query against tblVisit would give you a way to get the "last"
visit.

A third query, joining the first two queries, gives you a way to get
CustomerID for customers where "last" VisitDate is greater than "last"
RenewalDate.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

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

 

[scs]

Ok, I have been messing around for awhile trying to figure this out. I've
been playing with different joins etc. For the most part this does exactly
what I need but...

I don't know how to modify this to find people who have a record in tblVisit
but either they have no record at all in the tblPayment or the records
RenewalDate field is null.

I can see I'm going to need to know this to be alerted to data entry errors,
as well as, being notified that people are visiting past their renewal date.

Thanks for any advice on how to accomplish this.

So, as a first step, you want to find CustomerIDs with a VisitDate that
comes after a RenewalDate (by the way, you really want to change the
[Date]
field name in tblVisit -- "Date" is a reserved word in Access).

A query against tblPayment would give you a way to get the "last"
RenewalDate for each customer (who has a renewal date -- are you only
interested in those with RenewalDates, or could a customer not yet have
one?). You'd use a Totals query to get that, GroupBy CustomerID, Max of
RenewalDate.

A similar query against tblVisit would give you a way to get the "last"
visit.

A third query, joining the first two queries, gives you a way to get
CustomerID for customers where "last" VisitDate is greater than "last"
RenewalDate.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

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

 

[John Spencer]

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.

SELECT tblCustomer.*

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

 

[scs]

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

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.

SELECT tblCustomer.*

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

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

 

[Jeff Boyce]

You may need to use a "directional" join in your query. Open one of your
current queries in design mode. Highlight the "join" line, then right-click
it to get at its properties. You will want to select the option that
returns ALL of one of the tables (probably your Customer) and ANY of the
other. This gives you all customers whether or not there are matching
records in the other table.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Ok, I have been messing around for awhile trying to figure this out. I've
been playing with different joins etc. For the most part this does exactly
what I need but...

I don't know how to modify this to find people who have a record in tblVisit
but either they have no record at all in the tblPayment or the records
RenewalDate field is null.

I can see I'm going to need to know this to be alerted to data entry errors,
as well as, being notified that people are visiting past their renewal date.

Thanks for any advice on how to accomplish this.

So, as a first step, you want to find CustomerIDs with a VisitDate that
comes after a RenewalDate (by the way, you really want to change the
[Date]
field name in tblVisit -- "Date" is a reserved word in Access).

A query against tblPayment would give you a way to get the "last"
RenewalDate for each customer (who has a renewal date -- are you only
interested in those with RenewalDates, or could a customer not yet have
one?). You'd use a Totals query to get that, GroupBy CustomerID, Max of
RenewalDate.

A similar query against tblVisit would give you a way to get the "last"
visit.

A third query, joining the first two queries, gives you a way to get
CustomerID for customers where "last" VisitDate is greater than "last"
RenewalDate.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

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

 

[John Spencer]

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

 

[scs]

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.

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

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]

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

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.

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

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

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

 

[scs]

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.

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

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.

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

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