Multiple Tables Query (1 Viewer)

[Eacaw]

I'm working on a database model for a hospital and I need to come up with a solution for the database to be able to automatically generate which colour each employee's uniform will have to be depending on which department they work in, and also which grade they are at.

I know that I am going to have to run a query against both tables and check for the consitant record. But I have no idea what the **** to do to achieve it. Here's a basic idea of what I've come up with so far.

GradeID | Colours Available
---------|-----------------
Grade a | Blue, black, grey
Grade b | White, Grey, Red
Grade c | Green, White Stripes, Black
etc...

DeptID | Colours Available
--------|-----------------
Dept a | Green, White Stripes, Black
Dept b | Blue, black, grey
Dept c | White, Grey, Red
etc...

So if someone working in Department c was at Grade a they'd have to wear Grey... I'm sure you could work that out, what I need to do is somehow work out how to make this work in Access. Currently I have a table for the three separate data types (Grades, Depts, Colours). What I also need for this to do is be able to write the colour that they are going to need to wear into another table.

Dave

 

[spoole]

Havn't really got time to go into it can be complicated but do search on nested IIF statements

 

[Eacaw]

Having just wandered around the hospital for a good hour or so looking for an epiphany I've come up with the idea of using 2D arrays... Although that's about as far as I got. I'm not sure how to really set it up in such a way that I can then code it to work.... Maybe i'll move this over to the VBA forum and ask them.

 

[gaby]

A possible simple solution. Make 3 tables: 1-Dept_Colors (DeptID, Color), 2-Grade_Colors (GradeID, Color), 3-Emply (EmplyID,Dept,Grade).
Make a query where you join the Employee Dept and grade with the respective table id.
then filter results where the color form the grade matches the color for the department. (Note that with the sample colors and departments you gave an employee may have multiple matches - An emply on Dept c grade B could use grey or white).
Here is a sample of the code:

SELECT Emply.ID, Emply.Dept, Emply.Grade, Grade_Colors.Color, Dept_Colors.Color
FROM (Emply INNER JOIN Dept_Colors ON Emply.Dept = Dept_Colors.DeptID) INNER JOIN Grade_Colors ON Emply.Grade = Grade_Colors.GradeID
WHERE (([Grade_Colors].[Color]=[Dept_Colors].[Color]));

 

[Eacaw]

Okay, cheers for the advice, on friday after going to a long walkabout around the hospital I came up with a solution, 2Dimensional Arrays. So I set up the array and inputed all the data, (I'll spare you looking at that) and coded a series of loops that would pick out the correct colour.

Here's the code, IT DOESNT WORK! But I'm getting no errors. (GradeID and DeptID are textbox's on the form)

'Variables
Dim grade As String
Dim dept As String
Dim Hours As String
Dim CounterG As Integer
Dim CounterD As Integer
Dim Colour As String
Dim FinishedG As Integer
Dim FinishedD As Integer
'PreDefinition
grade = GradeID.Text
dept = DeptID.Text
CounterG = 0
CounterD = 0
FinishedG = 0
FinishedD = 0
'GradeFinder
Do Until FinishedG = 1
If GradeColour(0, CounterG) = grade Then
    FinishedG = 1
Else
    CounterG = CounterG + 1
End If
Loop
grade = CounterG
'DeptFinder
Do Until FinishedD = 1
If DeptColour(0, CounterD) = grade Then
    FinishedD = 1
Else
    CounterD = CounterD + 1
End If
Loop
dept = CounterD
'Reset Counters
CounterG = 1
CounterD = 1
FinishedD = 0
'Find Colour Match
Do Until FinishedD = 1
    If GradeColour(GounterG, grade) = DeptColour(CounterD, dept) Then
        Colour = GradeColour(CounterG, grade)
        FinishedD = 1
    Else
        CounterD = CounterD + 1
        CounterG = CounterG + 1
    End If
Loop
ColReq.Caption = Colour
MsgBox (Colour)