Compare & flag values in same table based on value in another table
Hi all,
I have a database containing tables called ACCOUNT, CONTRACT & CONTRACTDETAILS. I have two tables in QV where one flags if contracts are going to expire within the next 60 days and another that shows all the new contracts currently in the process of being processed.
What I would like to do is to place a flag in the "Contract < 60 days" table if there is a similarly named contract currently being renewed. All contracts (current & upcoming) are stored in the CONTRACTS table in the database and the flag for upcoming projects (Status = 'Planned') is stored in the CONTRACTDETAILS table - all other info such as Start/End Dates, etc. are stored in the CONTRACT table. In QV, the tables look similar to below:
Contract < 60 Days:
ACCOUNT
CONTRACT REF
ENDDATE
Duffy's Shop
DUF - Support 2013-2014
01/04/2014
Sharp Supermarket
SHP - Maintenance 2013-2014
07/04/2014
Upcoming Contracts:
ACCOUNT
CONTRACT REF
STATUS
STARTDATE
Duffy's Shop
DUF - Support 2014-2015
Planned
02/04/2014
What I'm looking to accomplish is to read all values in the CONTRACTS table and flag if there is a similar named CONTRACT REF for the same account, where the Start Date (upcoming) >= End Date (current).
