Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 1st, 2006, 03:38 AM
Authorized User
 
Join Date: Dec 2005
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to ggiibboo
Default applying Filters to a relational database

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


 
Old February 1st, 2006, 04:52 AM
Authorized User
 
Join Date: Jan 2006
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to gws_byte
Default

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
 
Old February 1st, 2006, 07:34 PM
Authorized User
 
Join Date: Dec 2005
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to ggiibboo
Default

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.....

 
Old February 2nd, 2006, 02:55 AM
Authorized User
 
Join Date: Jan 2006
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to gws_byte
Default

ggibboo,

Yeah, you can send the DB to carcharodon_bite@hotmail.com

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting Records Within Relational Tables rit01 SQL Server 2000 5 May 11th, 2006 03:11 AM
non-relational database Michel SQL Server 2000 4 February 21st, 2006 04:05 PM
Post/Get from 2 Tables in Relational Database TSEROOGY Classic ASP Databases 1 November 16th, 2004 03:28 AM
Converting Flat File Database to Relational DB rj_655@hotmail.com Access 5 June 12th, 2004 09:33 AM
Indexing XML in relational database - performance cooold XML 1 November 13th, 2003 03:49 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.