Complicated search for data from 4 tables - can't do it sensibly!
Hi guys
I am currently using the query by form method to do my searches. The problems arise when i want to do a search on a query (permanent query, that is) which grabs info from over 4 tables. Now, i am using the LEFT join method because I need to be able to search for a customer who has been on a trip (and select the trip), a training course (and select it), and then also has certain skills (which is a load of checkboxes). i need to be able to use the form so that i can search for just people who have certain skills, or whatever, too. so basically i just made a query with all fields from the customer, skills, trip, and training tables.
this should be straight forward, BUT: when using the LEFT join method and query by form method to search this form, i get LOADS of info which is duplicated - what i mean is, just for two people who have gone on quite a few trips/training courses (the only two that wil show) i am getting nearly 200 records altogether! it's giving a horrible combination of trips and trainign courses - what i mean is, it's listing a specific training course 30 times (if there's 30 trips), and then listing another training course 30 times etc etc. it'f amazingly uneconomical. but i don't know how to search by any other method.
anything you could suggest would help! just the dummy data i am using now has 262 rows in this query datasheet! let alone when they start using this! it's too uneconomical.
the time is 4am and i have to hand over the db at 9am - if anyone could help me that would be appreciated!
Please, and cheers,
Edd
Hi guys
I am currently using the query by form method to do my searches. The problems arise when i want to do a search on a query (permanent query, that is) which grabs info from over 4 tables. Now, i am using the LEFT join method because I need to be able to search for a customer who has been on a trip (and select the trip), a training course (and select it), and then also has certain skills (which is a load of checkboxes). i need to be able to use the form so that i can search for just people who have certain skills, or whatever, too. so basically i just made a query with all fields from the customer, skills, trip, and training tables.
this should be straight forward, BUT: when using the LEFT join method and query by form method to search this form, i get LOADS of info which is duplicated - what i mean is, just for two people who have gone on quite a few trips/training courses (the only two that wil show) i am getting nearly 200 records altogether! it's giving a horrible combination of trips and trainign courses - what i mean is, it's listing a specific training course 30 times (if there's 30 trips), and then listing another training course 30 times etc etc. it'f amazingly uneconomical. but i don't know how to search by any other method.
anything you could suggest would help! just the dummy data i am using now has 262 rows in this query datasheet! let alone when they start using this! it's too uneconomical.
the time is 4am and i have to hand over the db at 9am - if anyone could help me that would be appreciated!
Please, and cheers,
Edd
