Multiple query question

[Jen0dorf]

Hi



Have my database working perfectly as I want (thanks to everyones help).

I'm now trying to use my accumulated vast and expert knowledge ;-) to tidy up things.

I have six tables set up in three sections for want of a better description

tbl Parish ---tbl Church ---tbl baptism

tbl Parish ---tbl Church ---tbl banns

tbl Parish ---tbl Church ---tbl marriage

tbl Parish ---tbl Church ---tbl death

with tblparish, tbl church being common

1 parish linked to many churches linked to many births, marriages, etc etc.

OK I have multiple queries to search births, banns marriages and deaths by year, surname and forename etc etc.

It all works no problem but I do have 52 queries and multiple "identical" forms based on these queries.

As I said it all works and is not slow.

Should I just be happy or should I be looking for a way to reduce the number of tables and queries?

thanks

Ian

[CJ_London]

no of tables - probably OK, but perhaps the last 4 can be combined into one with the addition of a 'type' field and maybe a couple of redundant fields for variation of type. Depends on the fields you have.

number of queries seems excessive and you should really not have multiple identical forms. If they are multiple because of adding/editing etc these functions can be combined and parameters set depending on function required.

For forms, you have 6 tables, so as a minimum you should only need to have 6 forms (plus perhaps a navigation form) and no queries (recordsource would be the table) or if you want a query then 6 queries.

You could also have a single 'base' query for reporting purposes and just modify the criteria for what is required - you would left join parish to church and left join church to each of the other 4 tables.

[davegri]

If you have "Identical forms" only because each has a different recordsource query, you can use a single form and change its recordsource property in code before opening it.

Alternately, you can also create queries on the fly in code with a common name, such as qTempQuery. The form above would have qTempQuery as its recordsource and a different as needed qTempQuery would be created before opening it.

These techniques, especially the latter can reduce the number of named queries significantly. The syntax for the 52 queries (if still needed) would be in a module, all created as needed with a set of common query names to be used by reports or forms.

If this appeals to you, I can post a demo db showing how to create queries on the fly from their SQL strings and how they work as a form recordsource.

[Jen0dorf]

If you have "Identical forms" only because each has a different recordsource query, you can use a single form and change its recordsource property in code before opening it.

Alternately, you can also create queries on the fly in code with a common name, such as qTempQuery. The form above would have qTempQuery as its recordsource and a different as needed qTempQuery would be created before opening it.

These techniques, especially the latter can reduce the number of named queries significantly. The syntax for the 52 queries (if still needed) would be in a module, all created as needed with a set of common query names to be used by reports or forms.

If this appeals to you, I can post a demo db showing how to create queries on the fly from their SQL strings and how they work as a form recordsource.

Hi

thanks for the the idea would love to see the database and how it works

cheers

Ian

[davegri]

Attachment 25107

Attachment 25108

OK, see the above pic. The attached db shows how to use a named query, qtempQuery as the named recordsource for the two forms and the two reports and how to change what qtempQuery pulls from the tables as needed on the fly.
Notice that qtempQuery is the only query shown in the navigation pane, not 4 separate queries.

[Jen0dorf]

THanks, I'll start studying

Ian