0

I am working in ms access database2000 and my frontend is vb 6.0.

I have three tables namely:-
AREA:- areaid,areaname
ROUTE:-routeid,routeno,routename,areaids
PARTY:-partyid,lastname,firstname,address,areaid

The field areaids in ROUTE table consists of chain of areaids belonging to that particular route in this format '12,23,65,67,'

The problem is that i want to derive a query which is able to retrieve records from ROUTE and PARTY on the basis of a particular area.
response is required as early as possible.
thanks in adv.

2
Contributors
1
Reply
2
Views
10 Years
Discussion Span
Last Post by pty
0

I am working in ms access database2000 and my frontend is vb 6.0.

I have three tables namely:-
AREA:- areaid,areaname
ROUTE:-routeid,routeno,routename,areaids
PARTY:-partyid,lastname,firstname,address,areaid

The field areaids in ROUTE table consists of chain of areaids belonging to that particular route in this format '12,23,65,67,'

The problem is that i want to derive a query which is able to retrieve records from ROUTE and PARTY on the basis of a particular area.
response is required as early as possible.
thanks in adv.

Your database is designed wrong.

If an area has and belongs to many routes and a route has and belongs to many areas you need a link table:

AREA:-  1
areaid, ----------------+
areaname                |
                        |
AREA_ROUTE(link table)  |
id     0..*             |
areaid------------------+
routeid------------------------+
         0..*                  |
                               |
ROUTE:-   1                    |
routeid,-----------------------+
routeno,
routename,
areaids

Then you can find the included ones using a simple join.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.