Unanswered: Searching a table based on results from a rs
I am having an issue creating a SQL query to get the data I want an populate a record set. I will explain the layout of the mySQL table first, then tell you what I am trying to do.
The layout is like follows, some fields removed for simplicity here:
1) refnumber (containts either a Policy# or Acct#)
2) typeofrec (can be DN, PP, FA)
2) policycount (count of avaliable policy numbers)
The policy1-9 fields contain policy numbers that correspond to the acct# being held in the refnumber field. This only has data if the typeofrec field is a DN. if it is a PP or FA the refnumber field contains the PolicyNumber, and the policycount and policy1-9 fields are all set to "0".
What I am trying to do is search by ACCT#, which will look at the refnumber and if it finds a match it will pull out the values for Policy1-9...and then build the SQL query based on the refnumber and the policy1-9 field. The problem is, if there is more then one record with that ACCT# (they can be from different dates), it only selects the policy1-9 values for the last record, not any of the other ones. Any one have an idea how I could build this query to take into account what I have stated above??
I have a policy # search working fine, because all I have to do is search the refnumber & policy1-9 fields to get the data.
I also need to be able to do partial number searches on acct#, but if I can get the problem stated above fixed it shouldn't be an issue getting the partial searches to work.