Kept me up last night!

[jwindon]

OK. Anyone up for a real challenge?

Here's the scenerio...

There are 176 Districts.
Each District has 1 Health Coordinator
Each District has 1 HIV Coordinator, but they may be the same person as the Health Corr. or unnamed "HIV Coordinator"

Before I go on, I will tell you that I don't care if I have to rebuild my tables.

What we need?:

To be able to print labels for:

All Health Corr.
All HIV Corr.
All "HIV Corr." (where no HIV Corr name is given".

I tried and tried. Created options for HC only, HIV only, Both. Running append queries to create on new table, etc.

All to no avail? I'll take any suggestion.
http://www.access-programmers.co.uk/ubb/Forum3/HTML/002113.html

[This message has been edited by jwindon (edited 09-01-2001).]

 

[Carol]

I don't know how your tables are set up, but this should be quite easy to do. First create queries to display the information.
1 query would have the District and the corresponding Health Coordinator, your label would be based upon the field Health Coordinator.

For the second set of labels with the District, corresponding Health Coordinator and HIV Coordinator, again create a query and use the HIV Coordinator as the field.

For your third set of labels, create a query, the same as the second, but omitting the HIV Coordinator and in a blank column in the query grid, put the following:

=IIf(IsNull([HIV Coordinator]),[Health Coordinator],[HIV Cordinator])

What this will do is create a field that will put in the HIV Coordinator that is assigned, and if no HIV Coordinator is assigned, it will assign the Health Coordinator.

This could all be done within 1 query, but ! broke it down, so that you could easily follow it. These are based upon 3 tables,
District
HealthCordinator
HIVCordinator

with a One-To-Many relationship




[This message has been edited by Carol (edited 09-01-2001).]

 

[jwindon]

Thanks for your advice Carol. I will try it next week when I get to work. Or maybe I will get ambious and try something similary at home. I'll post back.

 

[raskew]

Try setting this up. It'll take about two minutes.

If you had a table (tblCoordinators) something like this (number of districts is immaterial):

DistID HealthC HIVC
1 John Joe
2 Sam Sam
3 Robert

…and you wanted to fill tblAddress, which has fields:

DistID, number
MyAddress, text

…the following code should do it. To create mailing labels, it'll take some additional steps (e.g., a separate table with street, city, state, zip for each DistID), but this should provide the basics. The code is very rudimentary, intended to demonstrate the process. It can be reduced to about half its size by creating and calling a separate function to accomplish the "With rs2" actions.

'******* Copy/paste the following to a new module. ***************


Function FillAddress()
Dim db As DATABASE
Dim rs As Recordset
Dim rs2 As Recordset
Dim strHold As String
Dim x As Integer, n As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblCoordinators")
Set rs2 = db.OpenRecordset("tblAddress")

If Not rs.EOF Then
Do While Not rs.EOF
Select Case rs!HIVC
Case Is = rs!HealthC
strHold = rs!HealthC & ", Health and HIV Coordinator"
strHold = strHold & ", District " & rs!DistID
With rs2
.AddNew
!DistID = rs!DistID
!MyAddress = strHold
.Update
End With

Case Is <> rs!HealthC
For x = 1 To 2
strHold = IIf(x = 1, rs!HealthC & ", Health Coordinator", rs!HIVC & ", HIV Coordinator")
strHold = strHold & ", District " & rs!DistID
With rs2
.AddNew
!DistID = rs!DistID
!MyAddress = strHold
.Update
End With

Next x
Case Else
For x = 1 To 2
strHold = IIf(x = 1, rs!HealthC & ", Health Coordinator", "HIV Coordinator")
strHold = strHold & ", District " & rs!DistID
With rs2
.AddNew
!DistID = rs!DistID
!MyAddress = strHold
.Update
End With

Next x

End Select
rs.MoveNext
Loop
End If

rs.Close
rs2.Close
db.Close
Set db = Nothing
End Function


[This message has been edited by raskew (edited 09-01-2001).]