grouping using arrays and loops

[scroce]

trying to be brief and simple as possible.

my insurance company provides 5 policies to its clients: a, b, c, d, & e.
my company also works with several different agents.

some agents are associated with more than one policy.

there is a report that I'm trying to build that shows the client name, and below that, the policies provided, and which agent is associated with them.

right now, i've got it doing this:

Client X
PolicyA - Agent1
PolicyB - Agent2
PolicyC - Agent1

but what I really want on the report in this scenario is:

ClientX
PolicyA,C - Agent1
PolicyB - Agent2

in order to save space and for the report reader's simplicity.

I've tackled this by:

1. setting up several txtbox fields on the report to handle up to 5 different agents per client (although this is unlikely to happen)just in case each policy has a different agent.

2. setting up an array to hold all of the agentID #'s for each policy type

I need to traverse the array and find if the array contains two matching agentID's. If they are all different, then I need to populate 3 of the 5 boxes with the agent name. But if there is a duplicate, then only 2 of the 5 need to be populated. As you can extrapolate, the problem gets more involved the more coverages involved - in this project it will soon be up to 10, so I want to write a clean procedure now that will easily accomodate them.

I am uncertain how to proceed from here and could use some conceptual hints or examples of something similar. Is this even a problem best tackled by loops and arrays (i have a sneaky feeling recursion may be involved here?, but i was always bad at applying that concept, it just seems to short circuit my brain)

I can post my lousy code, which isn't working if need be, but i think my problem is more conceptual in nature.

if you're still reading, thanks.
if you reply - thanks even more!












I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[payback]

Two possibilities come to mind here -

The first is to sort your results in the report by Agent by Policy and group them. This will not save any space though but will provide a tidy report and will let you do any totalling in the agent group footer.

The second is similar to a similar constraint that I solved by writing a function to basically create a string of characters to report in a text box. Create a function and pass to it the variables of the client ID and the Agent ID. Within this function, select any policies that match your criteria by agent and append the details to a string. Return this string at the end of the function. With careful logic, you can tell it that if the agent changes, insert a vbCrLf to create a new line.

Make sense?

Let me know what you think and if you need any help with VB coding.

 

[scroce]

payback

so far i've kind of done that. each page on the report is dedicated to a client. Then I have functions that return booleans that tell me what policies they have.

WC, AL and PR are 3 types of policies - so right now, if the Validate funciton is true, then it finds the AgentID and sticks it into the appropriate element of the array - like so:

If ValidateWC(intMemID, strPolPd) Then
   intTemp = DeriveAgentData(intMemID, strPolPd, "WCAgentID", "WC")
   intAgentID(0) = intTemp
End If
If ValidateAL(intMemID, strPolPd) Then
   intTemp = DeriveAgentData(intMemID, strPolPd, "ALAgentID", "AL")
   intAgentID(1) = intTemp
End If
If ValidateProp(intMemID, strPolPd) Then
   intTemp = DeriveAgentData(intMemID, strPolPd, "PRAgentID", "PR")
   intAgentID(2) = intTemp
End If

This gives me an array that looks something like:

|109|109|78|

If, for example, the client has the same agent for WC and AL
policies, but a different one for PR

but I'm at a loss what to do with them now. to populate the text boxes for every combination of possibilties seems possible to do with a hugely complex series of If>Then>Elses or Select Cases, but not practical.

as a matter of fact I did it this way when I only was tracking 2 polices, but as you can imagine it was inelegant & messy, (but it worked so I didn't care)

Considering the above can you tell me more about your idea about using a string may apply here, or is this approach not conducive to it?

thanks

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[GummowN]

How about a crosstab query with a select query sitting on top.

Use the policies as the column headers (as you said there are 5 fix their headings), the row header should be the agent, and the value be a count of the policy they provide.

This is a bit of a hack - and only using queries and you might want to generate the SQL for the queries using VBA depending on the number of policies you have. If you need help with that then shout.

Example:

Table EG002_Agents:

Agent Ag_ID
Bill 1
Bob 2
Simon 3
Carl 4

Table EG006_Links:

Agent Policy
1 1
1 5
2 2
2 3
3 4
3 5
4 1
4 4

Query EG102_XTB:

TRANSFORM Count(*) AS Expr1
SELECT EG002_Agents.Agent
FROM EG002_Agents INNER JOIN EG006_Links ON EG002_Agents.Ag_ID = EG006_Links.Agent
GROUP BY EG002_Agents.Agent
PIVOT EG006_Links.Policy In (1,2,3,4,5);

Query EG104_Results:

SELECT EG102_XTB.Agent, Left(IIf([1]=1,"Policy 1, ") & IIf([2]=1,"Policy 2, ") & IIf([3]=1,"Policy 3, ") & IIf([4]=1,"Policy 4, ") & IIf([5]=1,"Policy 5, "),Len(IIf([1]=1,"Policy 1, ") & IIf([2]=1,"Policy 2, ") & IIf([3]=1,"Policy 3, ") & IIf([4]=1,"Policy 4, ") & IIf([5]=1,"Policy 5, "))-2) AS Policies
FROM EG102_XTB;



If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[scroce]

gummown

i don't know that i could go that way without completely revamping my report, which i'm trying to avoid at the moment.

maybe it would help if i took this in small pieces

so say I have this array:

WC| AL|PR|CR |EDP
109|109|78|109|46

the top row is a policy identifier - the bottom row is an agentID

I think I need to write a procedure that loops thru this array, and processes it down to a new array that looks something like:

WC,AL,CR|PR|EDP
109 |78|46

then I think I could extract the data out of this array and place it into my report.

I'm just having trouble visualizing the code to do this.

have i thoroughtly confused and annoyed everyone yet?


I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[payback]

Do we really need the array?

Can you give me a little more detail about the table structure and I will look at how I would code this for your report.

For example, this calculated field is in my query -

ListOfUnits: ListDispatchUnits([Dispatches.ID],[Orders_Units.ProductID])

and this is the function it calls -

Function ListDispatchUnits(DispatchID, ProductID) As String
'On Error GoTo Errorhandler

Dim dbs As Database
Dim rst As Recordset
Dim strCriteria As String
Dim strUnitNos As String

If IsNull(DispatchID) Then Exit Function
If IsNull(ProductID) Then Exit Function

Set dbs = CurrentDb

strCriteria = "SELECT Dispatches_Units.UnitNo FROM Orders_Units RIGHT JOIN Dispatches_Units ON Orders_Units.UnitNo = Dispatches_Units.UnitNo WHERE ParentID = " & DispatchID & " AND ProductID = " & ProductID & " ORDER BY Dispatches_Units.UnitNo;"

Set rst = dbs.OpenRecordset(strCriteria, dbOpenDynaset)
Do Until rst.EOF
If Len(strUnitNos) = 0 Then
strUnitNos = rst!UnitNo
Else
strUnitNos = strUnitNos & ", " & rst!UnitNo
End If
rst.MoveNext
Loop

ListDispatchUnits = strUnitNos

rst.Close
Set dbs = Nothing

Exit Function

Errorhandler:
Call Error_Display_Vars(Err, Application.CurrentObjectName)

End Function

The report has a text box called ListOfUnits as its source.

 

[scroce]

do you really need the array"

I don't know - i guess this is open to debate...

i see what you are trying to do, querying through the records, and then thru them records, finding which ones have unit numbers, and concatenating them into 1 large comma separated string - a good idea, I have employed that trick elsewhere in this report.

i thinking using sql is problematic here because of the sheer number of fields and criteria involved - i have oversimplified my example for ease of discussion - the SQL statement I think would not be manageable. The table structure is like this:

tblMembers (Parent)
tblPolicyWC (child)
tblPolicyAL (child)
tblPolicyPR (child). . . . etc

If a member doesn't have an AL policy, then there will be no record in tblPolicyAL for a particular fiscal year. That's why I wrote the Validate code discussed in the above thread, which asks for the memberID and fiscal year, and returns the boolean.

The array is not the end of the puzzle. Once I have the policy identifiers and the agentIDs, i need those to query the database to pull all of the agent's address and contact data which gets dumped on the report.

My idea is to boil down the duplicate array into a new array, and use this array to build the agents's contact data discussed above.



I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[GummowN]

Unless it is a complete pain - and really screws your table structure I would ditch your 1 table per policy design. Instead have a table with the policies in it - and then link to a table which has the policy ID in it.

HOWEVER why not try the following SQL statement:

SELECT EG002_Agents.Agent, Left([policybase],[pollength]+IIf([pollength]=0,0,-2)) AS Policies, IIf(IsNull([pol1]![agent]),"","Policy1, ") & IIf(IsNull([pol2]![agent]),"","Policy2, ") & IIf(IsNull([pol3]![agent]),"","Policy3, ") AS PolicyBase, Len([policybase]) AS PolLength
FROM ((EG002_Agents LEFT JOIN EG008_Policy1 AS Pol1 ON EG002_Agents.Ag_ID = Pol1.Agent) LEFT JOIN EG010_Policy2 AS Pol2 ON EG002_Agents.Ag_ID = Pol2.Agent) LEFT JOIN EG012_Policy3 AS Pol3 ON EG002_Agents.Ag_ID = Pol3.Agent;

It will give you the string you need - with a lot less effort. Windowdressing the report is the least of your worries

Let me know how you get on


If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[scroce]

it's going to take me a bit to apply your SQL statement, so forgive me if my post back is slow

you are saying to only have one table that holds all policies? The problem with that, is that each type of policy tracks a ton different information. - i.e. it's not uniform. Where would I store all those different fields?

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[GummowN]

The SQL statement in my last post assumes you have seperate tables for each policy, with the table containing a row for each agent that is in it.

It does sound like your data model is a little muddled - is it fully normalised. You can quickly get yourself into a muddle if your database structure is not properly sorted. Personally I would have:

1) A table of all agents including their details
2) A table of all policies including their details
3) A table of agentIDs, policyIDs, and year (make the 3 fields a primary key - so no duplicates)

Perhaps a better description and example of your data structure would give us more clues.

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[scroce]

I have the first two, but not the third. I have worked to normalize the data as best I knew how over the past few years. The data store got larger, and I wanted to share it out, so I moved it recently to SQL server.

When I built this DB I had originally had the third, with the 3 primary keys but it became problematic, so I abandoned that approach in favor of this one. I'm not sure what would be the point of having a table like you mention in item 3 - wouldn't that make duplicate data?

re: providing you a better snapshot of my data model - how would I do that, i guess you'd have to see the diagram somehow right? trying to explain it verbally would be difficult.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[GummowN]

How did the 3 field primary key become problematic?

Surely an agent can only have each policy once per year. If not then there is the problem and also a headache for anything you are trying to do.

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[scroce]

yes that is true - and currently the data model takes this into consideration and does not allow that to happen.

I have associated an agent directly with a policy, and in turn the policy directly with a member.

I debated several ways of doing this with myself, but finally settled on this one. I'm not saying its 100% right, however before i set it up this way I was writing much more and complex code.

All I know that since I changed the model the SQL is less complicated and there's been overall less code for what I need the DB to do.

I do have an eye towards the normalization of it. It may not be perfect, but there are no obvious flaws or duplication of information. However, your post is causing me to rethink this slightly.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[scroce]

well, i finally got this to work using loops and an array. the code is quite simple looking written out, and not very lengthy.

I will post it if anyone cares, but it looks different b/c makes calls to other functions not described here, etc. etc.

Gumm - you brought up some good points about my data model, I will have to keep vigilant to see if I made a colossal blunder.

but i thank you all for your input - it's valuable to be able to discuss these things with knowledgeable people.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[scroce]

How did the 3 field primary key become problematic?"

As I was jogging this weekend the recollection came back to me - as this was some time ago

If you do this - as is suggested above:

"A table of agentIDs, policyIDs, and year (make the 3 fields a primary key - so no duplicates)"

One major problem for my specific purposes is that this model would allow two different agent ID's for the same client in the same year.

This is a big big no no in my data model - in fact this started happening, creating duplicate records of bad data -and this is exactly why I changed it to the current model.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[GummowN]

You should be able to create a data structure that fits your model. Just a case of setting primary keys on the right fields. In your case you would want a table of:

AgentID
PolicyID
Year

With primary key on Year and AgentID.

If you get the chance play with your data structure, a link table as I have suggested and see what happens when you try insert certain data.

I have done this in the past and found it quite an enjotable learning experience

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.