I am working with 3 tables as follows:
1)Customer 2)Products 3)States
Each customer can have multiple products.
Each customer record has a Date Lost field representing the date that the customer last laid claim to the products that he or she owns. These products are deemed Abandoned Property after a certain period of time and become the property of the state in which the customer resides.
The states are broken down into 2 categories, Fall States and Spring States.
a) Fall States require that products be turned over to the state in which the customer resides on 6/30 of the current year (a Base Date of 6/30/2005) minus a certain number of years determined by each state and depending on the product (Stocks, Bonds, Mutual Funds or Cash).
b) Spring States require that products be turned over to the state in which the customer resides on 12/31 of the prior year (a Base Date of 12/31/2004) minus a certain number of years determined by each state and depending on the product (Stocks, Bonds, Mutual Funds or Cash).
Futhermore, the products can be of 1 of 4 types as follows:
1) Cash
2) Mutual Funds
3) Bonds
4) Stocks
With respect to the State table, there are colunms for State, Cash, Mutual Funds, Bonds, Stocks, and a Check Mark field which if true, signifies a Fall State (if unchecked, it signifies a Spring State). The Cash, Mutual Funds, Bonds and Stocks fields for each state have an integer value.
For example, the state record for NY, a Spring State and CA, a fall state, could have the following values:
State Cash, Mutual Funds, Bonds, Stocks Check Mark
NY 3 4 5 6 unchecked
CA 1 2 3 2 checked
For the NY records, this means:
Cash: turned over to the state if it's 3 years old.
Mutual Funds: turned over to the state if it's 4 yrs old.
Bonds: turned over to the state if it's 5 years old.
Stocks: turned over to the states if it's 6 years old.
I'll give a couple of examples to clarify the queries:
a) Customer Jane Doe lives in New York which is deemed a Spring State. Jane Doe owns Bonds. Her property is deemed abandoned on 12/31 since NY is a spring state. Thus, since
Bonds are considered abandoned after 5 years, the abandonment date is calculated as 12/31/1999. (12/31/2004, the base period, minus 5 years = 12/31/1999).
b) Customer John Smith lives in CA which is deemed a Fall State. John Smith owns Stocks. His property is deemed abandoned on 6/30 since CA. is a fall state.
The year of abandonment is determined by the type of product. Thus, since Stocks are considered abandoned after 2 years, the abandonment date is calculated as 6/30/2003. (6/30/2005, the base period, minus 2 years = 6/30/2003).
The application I am writing is based on reading records and comparing the Date Lost field from the Customer's record
against the calculated date of abandonment depending on the type of property and the rule for the state in which the customer resides.
For Jane Doe, in the example above, if she owned Bonds and her cutomer record reflected a Date Lost prior to 12/31/1999, I would extract this record as an Abandoned Property. If her Date Lost record for Bonds reflected a Date Lost of 3/24/2004, I would bypas this record since the property had not been lost on or before 12/31/1999.
For John Smith, in the example above, if he owned Stocks and his cusomter record reflected a Date Lost of 9/15/2001, I would extract this record since the property
has been lost on or before 6/30/2003.
Would this be a regular Select Query or a Union if I wanted to select ALL customers and their products regardless of whether the customer followed the Fall State or Spring State rules ?
The query for the Spring State records is basically the same except:
a) the Date comparison for the products is 12/31
b) the FallCycle is No (for a Spring State).
I can create a query for the Fall States as 1 query and another query for the Spring States.
I don't think I can represent collectively in the QBE grid
a query to select ALL records for Spring AND Fall States.
Am I wrong ? Is it possible to combine the Spring AND Fall State queries into 1 query using the QBE grid.
The query for the Fall State records is as follows:
Select *
From tblCustomers, tblProducts, tblStates
WHERE (((tblCustomers.DateLost)<=DateAdd("yyyy",[tblStatesAll].[BondsFS]*-1,CDate("06/30/" & Year(Now())))) AND ((tlbProducts.PropertyType)="BONDS") AND ((tblStatesAll.FallCycle)=Yes)) OR (((tblCustomers.DateOfBirth)<=DateSerial(Year(Now())-70,1,1)) AND ((tblCustomers.DateLost)<=DateAdd("yyyy",[tblStatesAll].[IRAFS]*-1,CDate("06/30/" & Year(Now())))) AND ((tlbProducts.PropertyType)="IRA") AND ((tblStatesAll.FallCycle)=Yes)) OR (((tblCustomers.DateLost)<=DateAdd("yyyy",[tblStatesAll].[CashFS]*-1,CDate("06/30/" & Year(Now())))) AND ((tlbProducts.PropertyType)="CASH") AND ((tblStatesAll.FallCycle)=Yes)) OR (((tblCustomers.DateLost)<=DateAdd("yyyy",[tblStatesAll].[StocksFS]*-1,CDate("06/30/" & Year(Now())))) AND ((tlbProducts.PropertyType)="STOCKS") AND ((tblStatesAll.FallCycle)=Yes))
I created a combo box to allow the users to pick a Fall Report, a Spring Report or ALL (meaning run both).
I guess the answer is if the User picks ALL, behind the scenes I will run the query for the Fall and the query for the Spring, one after the other instead of trying to combine them into 1 query. What do you think ?
1)Customer 2)Products 3)States
Each customer can have multiple products.
Each customer record has a Date Lost field representing the date that the customer last laid claim to the products that he or she owns. These products are deemed Abandoned Property after a certain period of time and become the property of the state in which the customer resides.
The states are broken down into 2 categories, Fall States and Spring States.
a) Fall States require that products be turned over to the state in which the customer resides on 6/30 of the current year (a Base Date of 6/30/2005) minus a certain number of years determined by each state and depending on the product (Stocks, Bonds, Mutual Funds or Cash).
b) Spring States require that products be turned over to the state in which the customer resides on 12/31 of the prior year (a Base Date of 12/31/2004) minus a certain number of years determined by each state and depending on the product (Stocks, Bonds, Mutual Funds or Cash).
Futhermore, the products can be of 1 of 4 types as follows:
1) Cash
2) Mutual Funds
3) Bonds
4) Stocks
With respect to the State table, there are colunms for State, Cash, Mutual Funds, Bonds, Stocks, and a Check Mark field which if true, signifies a Fall State (if unchecked, it signifies a Spring State). The Cash, Mutual Funds, Bonds and Stocks fields for each state have an integer value.
For example, the state record for NY, a Spring State and CA, a fall state, could have the following values:
State Cash, Mutual Funds, Bonds, Stocks Check Mark
NY 3 4 5 6 unchecked
CA 1 2 3 2 checked
For the NY records, this means:
Cash: turned over to the state if it's 3 years old.
Mutual Funds: turned over to the state if it's 4 yrs old.
Bonds: turned over to the state if it's 5 years old.
Stocks: turned over to the states if it's 6 years old.
I'll give a couple of examples to clarify the queries:
a) Customer Jane Doe lives in New York which is deemed a Spring State. Jane Doe owns Bonds. Her property is deemed abandoned on 12/31 since NY is a spring state. Thus, since
Bonds are considered abandoned after 5 years, the abandonment date is calculated as 12/31/1999. (12/31/2004, the base period, minus 5 years = 12/31/1999).
b) Customer John Smith lives in CA which is deemed a Fall State. John Smith owns Stocks. His property is deemed abandoned on 6/30 since CA. is a fall state.
The year of abandonment is determined by the type of product. Thus, since Stocks are considered abandoned after 2 years, the abandonment date is calculated as 6/30/2003. (6/30/2005, the base period, minus 2 years = 6/30/2003).
The application I am writing is based on reading records and comparing the Date Lost field from the Customer's record
against the calculated date of abandonment depending on the type of property and the rule for the state in which the customer resides.
For Jane Doe, in the example above, if she owned Bonds and her cutomer record reflected a Date Lost prior to 12/31/1999, I would extract this record as an Abandoned Property. If her Date Lost record for Bonds reflected a Date Lost of 3/24/2004, I would bypas this record since the property had not been lost on or before 12/31/1999.
For John Smith, in the example above, if he owned Stocks and his cusomter record reflected a Date Lost of 9/15/2001, I would extract this record since the property
has been lost on or before 6/30/2003.
Would this be a regular Select Query or a Union if I wanted to select ALL customers and their products regardless of whether the customer followed the Fall State or Spring State rules ?
The query for the Spring State records is basically the same except:
a) the Date comparison for the products is 12/31
b) the FallCycle is No (for a Spring State).
I can create a query for the Fall States as 1 query and another query for the Spring States.
I don't think I can represent collectively in the QBE grid
a query to select ALL records for Spring AND Fall States.
Am I wrong ? Is it possible to combine the Spring AND Fall State queries into 1 query using the QBE grid.
The query for the Fall State records is as follows:
Select *
From tblCustomers, tblProducts, tblStates
WHERE (((tblCustomers.DateLost)<=DateAdd("yyyy",[tblStatesAll].[BondsFS]*-1,CDate("06/30/" & Year(Now())))) AND ((tlbProducts.PropertyType)="BONDS") AND ((tblStatesAll.FallCycle)=Yes)) OR (((tblCustomers.DateOfBirth)<=DateSerial(Year(Now())-70,1,1)) AND ((tblCustomers.DateLost)<=DateAdd("yyyy",[tblStatesAll].[IRAFS]*-1,CDate("06/30/" & Year(Now())))) AND ((tlbProducts.PropertyType)="IRA") AND ((tblStatesAll.FallCycle)=Yes)) OR (((tblCustomers.DateLost)<=DateAdd("yyyy",[tblStatesAll].[CashFS]*-1,CDate("06/30/" & Year(Now())))) AND ((tlbProducts.PropertyType)="CASH") AND ((tblStatesAll.FallCycle)=Yes)) OR (((tblCustomers.DateLost)<=DateAdd("yyyy",[tblStatesAll].[StocksFS]*-1,CDate("06/30/" & Year(Now())))) AND ((tlbProducts.PropertyType)="STOCKS") AND ((tblStatesAll.FallCycle)=Yes))
I created a combo box to allow the users to pick a Fall Report, a Spring Report or ALL (meaning run both).
I guess the answer is if the User picks ALL, behind the scenes I will run the query for the Fall and the query for the Spring, one after the other instead of trying to combine them into 1 query. What do you think ?
