If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Report/Query based on two tables both on them many side.
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 |
#2
|
|||
|
|||
Report/Query based on two tables both on them many side.
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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "scs" wrote in message ... 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 |
#3
|
|||
|
|||
Report/Query based on two tables both on them many side.
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! "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... 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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "scs" wrote in message ... 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 |
#4
|
|||
|
|||
Report/Query based on two tables both on them many side.
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. "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... 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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "scs" wrote in message ... 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 |
#5
|
|||
|
|||
Report/Query based on two tables both on them many side.
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 ON tblCustomer.CustomerID = V.CustomerID WHERE P.RenewalDate V.VisitDate 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 |
#6
|
|||
|
|||
Report/Query based on two tables both on them many side.
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. 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 ON tblCustomer.CustomerID = V.CustomerID WHERE P.RenewalDate V.VisitDate 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 |
#7
|
|||
|
|||
Report/Query based on two tables both on them many side.
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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "scs" wrote in message ... 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. "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... 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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "scs" wrote in message ... 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 |
#8
|
|||
|
|||
Report/Query based on two tables both on them many side.
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 |
#9
|
|||
|
|||
Report/Query based on two tables both on them many side.
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 |
#10
|
|||
|
|||
Report/Query based on two tables both on them many side.
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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Form based on multiple tables | Shaldaman | Using Forms | 2 | September 26th, 2005 02:01 PM |
adding new records to tables based on items selected | ashley | Using Forms | 1 | October 7th, 2004 02:48 PM |
how can I find duplicates in two tables based on phone number? | dustinw | Running & Setting Up Queries | 2 | October 5th, 2004 12:36 AM |
data entry form based on more than 3 tables? | prafull | Using Forms | 1 | September 30th, 2004 09:43 PM |
Mutliple Tables lookup? | Westley | Database Design | 4 | June 15th, 2004 01:07 AM |