Lots of Queries and Forms

[vickster_h]

I have three tables, namely main, categories and areas each are linked to each other through ID numbers.

There are about 20 Categories which are yes/no and 12 areas also yes/no. i have an opening form where a user can select to look at records which relate to a specific category eg Health. The Health form is linked to a Health Query.

My first problem is that for 20 categories i have to list 20 queries and make 20 identical (apart from category title) forms, is there an easier way????

My next problem is that i want the user to be able to then reduce the selection of records further by selecting an area eg Southampton. Which would mean 20 categories broken down into 12 areas, 240 queries!!!! and 240 forms! No there must be an easier way.

 

[capri]

sounds like you need a parameter query. I'm not the best to give advise on these as I'm still learning how. If you do a search at this site, sure you will come up with lots of previous posts about them.

I have one query, that I managed to set up, where when the user runs it, it asks for a category input, then only those records are returned. The next person to run the query will be asked for category and only get the ones they want. Saves having several different queries.

I should think if you made a parameter query the undelying link to your identical forms, then you would only require one form. When the person clicked on the form to open, they would need to key in the parameter first.

you might want to look at this post http://www.access-programmers.co.uk/ubb/Forum3/HTML/002098.html

[This message has been edited by capri (edited 08-30-2001).]

 

[Chris RR]

Yes, vickster_h, there's an easier way.

First, I'd be concerned about the design of your tables. In general, using yes/no fields for something like a category or an area means that you've got some problems in the database design. Get help from a DBA-type person if you can, or find a good reference book.

Then, start reading up on parameter queries. You are probably looking at writing one (1) parameter query, which will select records based on the category and area, and one (1) form, which would be a generic form that displays the results of your parameter query.

First, I'd be concerned about the design of your tables. In general, using yes/no fields for something like a category or an area means that you've got some problems in the database design.

[This message has been edited by Chris RR (edited 08-30-2001).]

 

[vickster_h]

Thank you both very much