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

hi ggiibboo,

I copied your two tables into access and linked them, then i built a query to acheive your results, the trick is to use the relay type field from the OTHER table to filter on, i named the two tables "plant" and "relay", if you copy the following SQL and paste it into SQL view of a blank query in access, then change the table part of the field name to match yours, it should work. :)

SELECT plant.ID, plant.Plant, plant.Area, relay.RelayType, plant.Datasheet1, plant.SerialNumber
FROM relay INNER JOIN plant ON relay.ID = plant.RelayType
WHERE (((relay.RelayType)="CAU21"));

Let me know if this helps


Dave - MAD

nil illegitimi carborundum

Semper in excreta, sumus solum profundum variat

I have tried this and it has thrown me into utter confusion.

Basically I want to be able to fill in some parameters into a form and the query or filter will return only the forms that match those parameters.

Would it help if I sent you a copy of the whole database?

My main problem seems to rely in the fact that i have used ID's in my main table that relates to values in other tables.....

ggibboo,

Yeah, you can send the DB to [email protected]

alternativly, send me the actual names of your tables and i'll create the query using the names you have.

the problem you are having is that the value that are stored in the field are the ID numbers, you see the other information because access tries to present it in a 'human friendly' format. In your query you need BOTH tables and you filter on the actual field from the lookup table.

Let me know how it goes...

Dave - MAD
nil illegitimi carborundum
Semper in excreta, sumus solum profundum variat