neptune wrote:
At my company customers tend to change contracts in the middle of the
month. I need a query that will match 1 customer to 1 contract. If
the customer changes contracts before the 15th of the month, I want to
pick the 1st contract, but if he changes after the 15th I want to pick
the 2nd one. I have included sample data for 2 customers in Nov. My
query should pick records 2 and 3. I've been playing with if
statements in the Group By statement.
Customer Contract MonthMiddle ContractStart ContractEnd
888888 16663 11/15/2003 7/1/2003 11/26/2003
888888 16665 11/15/2003 11/27/2003 6/30/2006
777777 16663 11/15/2003 7/1/2003 11/12/2003
777777 16665 11/15/2003 11/13/2003 6/30/2006
I have no idea what you are wanting to accomplish here. I suppose you
could attempt to set up an IIF() statement or some such method to set a
column to true or false...but I doubt that would help you in the long run.
If you are a programmer I suggest you create a function. I guess you
would pass the customer, contract, start, end, whatever to it. Then
process your data and return a True or False. Here is what your column
would look like in the query builder for calling the function.
UseThisRecord : CheckContract([Customer],[Contract],...)
Then your function, in a module, would be
Public Function CheckContract(varCustomer As Variant, _
varContract As Variant...) '... indicates other fields
Dim strSQL As String
Dim rst As Recordset
strSQL = your SQL Statement
set rst = currentdb.openrecordset(strSQL,...)
Now loop through the records in the recordset
and compare the values to the current record.
Pass back True or False whether this record
is to be displayed.
ENd Function
Basically, you want to compare all records for either that customer or
contract and see if there is a break. If not, then use it otherwise see
how the break is done and then return true/false based on the data.
If you don't know how to write a function you may be SOL.
