Query on the wrong data type
This problem is Bugging the hell out of me
I am wanting to perform some searches and apply some filters etc to my database however I keep incurring the same problem. The problem is that when I run queries on the data, the query engine doesn't recognise "CAU21" to sort by or filter by, it only can perform the sorting via the IDnumber that is associated with "CAU21" in the Lookup-RelayType table.
I hope I am making sense.
I will give you a breif example to explain.
My main table looks something like this:
ID Plant Area RelayType Datasheet1 SerialNumber
7 1 2 2 CDAG51 RP34288
8 1 2 4 VAF RP34296
9 1 2 2 CDAG51 RP34290
10 1 2 4 VAF RP34297
11 1 2 5 CDG11 RP34285
12 1 2 2 CDAG51 RP34286
13 1 2 6 CAG14 RP34292
14 1 2 4 VAF RP34294
15 1 2 2 CDAG51 RP34291
16 1 2 4 VAF RP34295
17 1 2 3 MUL269
18 1 2 3 MUL269
19 1 2 3 MUL269
20 1 2 3 MUL269
21 1 2 3 MUL269
22 1 2 2 CDAG51 RP34287
23 1 2 7 VAA RP34293
24 1 2 2 CDAG51 RP34289
25 1 2 6 CAG14 RP35023
27 2 3 22 149546
28 2 3 22 149616
29 2 3 22 149627
30 2 3 22 149536
32 2 3 8 CAG14 RP71463
33 2 3 9 CAU21 RP71382
34 2 4 23 91494
35 2 4 8 CAG14 RP22013
36 2 4 23 91501
37 2 4 11 043849J
38 2 4 12
39 2 4 3
40 2 4 3
41 2 5 22 149619
And then one of the lookup tables (Lookup-RelayType for an example) looks like this:
ID RelayType
2 CDAG51
3 Multilin 269Plus
4 VAA31/VAF
5 CDG31
6 CAG14/CAA
7 VAA11
8 CAG14
9 CAU21
10 Thermal O/L
11 MVAG
12 Multilin SR735
13 VAG11
14 CDG64
15 VAG51
16 DTH
17 VAA51
18 CDG11
19 CDG54
20 CAG13
21 MCAG
22 5A/R/95
23 M5A
So when I want to perform a search on all the CAU21 type relays (CAU21 has ID 9) I have to do a search on "9" or a filter/sort on "9". This is far from practicle because the end user doesn't know what the "ID" is of the particular type that they want to look up, and even if they did, it doesn't make sense to filter using "9" in order to get all the CAU21 relay information being displayed.
Any suggestions?
Mike
|
