Creating Queries.

[BillBee]

Although I have been creating a file related to a Bird Show for several years I consider myself very much a novice when it comes to working with MS Access. The file I have built up has reached 7000kb and contains 128 queries. In some instances I need 6 queries to obtain the data I require for a report. An example is Novice points are awarded. I need to separate Novices from Champion, find classes which contain 5 or more birds tand exclude wrong classed and non benched birds, and finally those classes which have 5 different exhibitors before the Novice receives the point. Although I have tried to name the queries similar to the Form or Report to make them easier to find I have wondered that maybe I should have used the Query Builder associated with each of these items. Could some one please advise which is the best to use and give a reason why. Thanks

 

[plog]

Without more information about your data and exactly how you need this data, we can't really provide you with specifics. In general, it sounds like you need to read some query tutorials, plan out what you need, how often you need it and what those queries have in common so you can build the most efficient method of getting it.

My guess is you are essentially pulling 2 or 3 different data sets, but in multiple ways (one total query for this exhibitor, another total query for another exhibitor, another total query for a third exhibitor, etc.). In those instances its best to build one query to aggregate your data by exhibitor (showing all of them) and then using a form to allow further refinement of what is pulled, or possible generate a report that groups by exhibitor.

Again, first and foremost, read a few online query tutorials.

 

[BillBee]

Thanks for suggestion re online query tutorials. You are not in line with my quetion. Basically when you have a Form or Report is it preferable to create a Query, or, is it preferable to use the Query Builder from within the Form or Report Design. It is over time I have built up so many queries I wondered which method was the best.

 

[mahenkj2]

Plog already answered what you are looking for.

In those instances its best to build one query to aggregate your data by exhibitor (showing all of them) and then using a form to allow further refinement of what is pulled, or possible generate a report that groups by exhibitor.

When you feel that a particular kind of dataset is going to be used again and again in your database, you may opt to make a query first and then use in different forms and reports by suitably modifying the needs setting criteria etc. Query builder is helpful when you need to build queries on the fly or that particular query is to be used not so often.