A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Row source Best practice??



 
 
Thread Tools Display Modes
  #1  
Old September 29th, 2009, 07:33 AM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default Row source Best practice??

Since i am new to all this access stuff i have been tending to create new
queries everywhere i need them instead of just creating stand alone queries
(in the database window) and referring back to them...

For example every form that uses a employee name combobox will have its own
query for the rowsource (in the combos properties) rather than having a
employee names query in the database window and having all employee combos
reference that query.. the same goes for report data sources where each
report has its own query (in properties) rather than referencing a query in
the database window's Queries page..

I guess i really have three related questions:

1. will all of these queries eventually cause me problems or do they really
not matter??

2. Are there design rules or methods to keep queries efficient.

3. in a report is it better to just use the table as the reports record
source with dlookups for related data or to use ALL related tables in a query
in the reports datasource?

im sorry if the question is vague or off, i am just trying to keep Dumb from
running wild..

Thanks

Barry
  #2  
Old September 29th, 2009, 08:23 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Row source Best practice??

Good questions.

Re #1 & #2:
No -- using saved queries won't cause you grief later. If you use a SQL
statement directly in the RecordSource of a form/report or the RowSource
property of a combo/listbox, Access creates a hidden query and saves it
anyway. Therefore there is no practical difference, and it's a matter of
style.

Advantages of using a saved query:
a) Single maintenance point. Particularly for combos, I like to create a
saved query and re-use it throughout the application (e.g. for other forms
where you also want a combo for clients also.) If you need to make a change
later (e.g. to sort all inactive clients to the bottom of the list), there's
only one query to change, and all your client combos get the same treatment.

b) May actually result in fewer saved queries (since they are reused.)

Disadvantage of saved query:
a) You have to manually copy the source queries also if you reuse the
form/report in another database.

b) There are more visible items to sort through on the Queries tab of the
Database Window / Navigation Pane.

Re #3
You *definitely* want to use a source query rather than have a bunch of
DLookup()s in your form/report. DLookups are slow and resource hungry. The
multi-table query will give you orders of magnitude better performance than
a table with a bunch of DLookups.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Barry A&P" wrote in message
...
Since i am new to all this access stuff i have been tending to create new
queries everywhere i need them instead of just creating stand alone
queries
(in the database window) and referring back to them...

For example every form that uses a employee name combobox will have its
own
query for the rowsource (in the combos properties) rather than having a
employee names query in the database window and having all employee combos
reference that query.. the same goes for report data sources where each
report has its own query (in properties) rather than referencing a query
in
the database window's Queries page..

I guess i really have three related questions:

1. will all of these queries eventually cause me problems or do they
really
not matter??

2. Are there design rules or methods to keep queries efficient.

3. in a report is it better to just use the table as the reports record
source with dlookups for related data or to use ALL related tables in a
query
in the reports datasource?

im sorry if the question is vague or off, i am just trying to keep Dumb
from
running wild..

Thanks

Barry


  #3  
Old September 29th, 2009, 05:57 PM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default Row source Best practice??

Thank you allen,

In regard to my question #2

I have come across talk somewhere about query performance. and SQL ordering
to keep queries efficient. maybe running a query with criteria first then
joining larger tables later or sorting after a using filtering criteria? i
dont really know what the difference is as the access "design View" doesnt
allow many options for the SQL arrangement.

If design view is good enough then design view is good enough..
Ill spend a little time on your tips page..

Thanks again for your earlier response

Barry


"Allen Browne" wrote:

Good questions.

Re #1 & #2:
No -- using saved queries won't cause you grief later. If you use a SQL
statement directly in the RecordSource of a form/report or the RowSource
property of a combo/listbox, Access creates a hidden query and saves it
anyway. Therefore there is no practical difference, and it's a matter of
style.

Advantages of using a saved query:
a) Single maintenance point. Particularly for combos, I like to create a
saved query and re-use it throughout the application (e.g. for other forms
where you also want a combo for clients also.) If you need to make a change
later (e.g. to sort all inactive clients to the bottom of the list), there's
only one query to change, and all your client combos get the same treatment.

b) May actually result in fewer saved queries (since they are reused.)

Disadvantage of saved query:
a) You have to manually copy the source queries also if you reuse the
form/report in another database.

b) There are more visible items to sort through on the Queries tab of the
Database Window / Navigation Pane.

Re #3
You *definitely* want to use a source query rather than have a bunch of
DLookup()s in your form/report. DLookups are slow and resource hungry. The
multi-table query will give you orders of magnitude better performance than
a table with a bunch of DLookups.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Barry A&P" wrote in message
...
Since i am new to all this access stuff i have been tending to create new
queries everywhere i need them instead of just creating stand alone
queries
(in the database window) and referring back to them...

For example every form that uses a employee name combobox will have its
own
query for the rowsource (in the combos properties) rather than having a
employee names query in the database window and having all employee combos
reference that query.. the same goes for report data sources where each
report has its own query (in properties) rather than referencing a query
in
the database window's Queries page..

I guess i really have three related questions:

1. will all of these queries eventually cause me problems or do they
really
not matter??

2. Are there design rules or methods to keep queries efficient.

3. in a report is it better to just use the table as the reports record
source with dlookups for related data or to use ALL related tables in a
query
in the reports datasource?

im sorry if the question is vague or off, i am just trying to keep Dumb
from
running wild..

Thanks

Barry



  #4  
Old September 30th, 2009, 01:49 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Row source Best practice??

Yes, JET exposes limited optimization techniques

This particular page may be of interest:
http://allenbrowne.com/QueryPerfIssue.html

Towards the end, there's a link for the JET Showplan, which could take you
further.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Barry A&P" wrote in message
...
Thank you allen,

In regard to my question #2

I have come across talk somewhere about query performance. and SQL
ordering
to keep queries efficient. maybe running a query with criteria first then
joining larger tables later or sorting after a using filtering criteria?
i
dont really know what the difference is as the access "design View" doesnt
allow many options for the SQL arrangement.

If design view is good enough then design view is good enough..
Ill spend a little time on your tips page..

Thanks again for your earlier response

Barry


"Allen Browne" wrote:

Good questions.

Re #1 & #2:
No -- using saved queries won't cause you grief later. If you use a SQL
statement directly in the RecordSource of a form/report or the RowSource
property of a combo/listbox, Access creates a hidden query and saves it
anyway. Therefore there is no practical difference, and it's a matter of
style.

Advantages of using a saved query:
a) Single maintenance point. Particularly for combos, I like to create a
saved query and re-use it throughout the application (e.g. for other
forms
where you also want a combo for clients also.) If you need to make a
change
later (e.g. to sort all inactive clients to the bottom of the list),
there's
only one query to change, and all your client combos get the same
treatment.

b) May actually result in fewer saved queries (since they are reused.)

Disadvantage of saved query:
a) You have to manually copy the source queries also if you reuse the
form/report in another database.

b) There are more visible items to sort through on the Queries tab of the
Database Window / Navigation Pane.

Re #3
You *definitely* want to use a source query rather than have a bunch of
DLookup()s in your form/report. DLookups are slow and resource hungry.
The
multi-table query will give you orders of magnitude better performance
than
a table with a bunch of DLookups.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Barry A&P" wrote in message
...
Since i am new to all this access stuff i have been tending to create
new
queries everywhere i need them instead of just creating stand alone
queries
(in the database window) and referring back to them...

For example every form that uses a employee name combobox will have its
own
query for the rowsource (in the combos properties) rather than having
a
employee names query in the database window and having all employee
combos
reference that query.. the same goes for report data sources where
each
report has its own query (in properties) rather than referencing a
query
in
the database window's Queries page..

I guess i really have three related questions:

1. will all of these queries eventually cause me problems or do they
really
not matter??

2. Are there design rules or methods to keep queries efficient.

3. in a report is it better to just use the table as the reports record
source with dlookups for related data or to use ALL related tables in a
query
in the reports datasource?

im sorry if the question is vague or off, i am just trying to keep Dumb
from
running wild..

Thanks

Barry



  #5  
Old September 30th, 2009, 03:12 AM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Row source Best practice??

Allen Browne has largely provided fine answers to your questions,
but I'd like to add a number of points.

=?Utf-8?B?QmFycnkgQSZQ?= wrote
in :

1. will all of these queries eventually cause me problems or do
they really not matter??


It depends on your app. I used to make saved queries for everything,
on the theory that they were more efficiently optimized. But since
Jet saves optimization plans for recordsource and rowsource queries,
there was never much advantage there.

Where you have the same rowsource used on multiple forms, it does
make sense to use a saved query for that as long as there's never
any reason the instance of your combo box ever need to fork. I
recently screwed up a combo box on one form that used a saved query
that I edited for use in a combo box on a different form that needed
an altered rowsource. I simply forgot that the changes I was making
to the saved query were going to impact the other use of it.

So, you really have to keep track of your usage of shared QueryDefs,
because the sharing is a two-edged sword -- great when all uses are
identical, problematic when there needs to be variation.

In general, I don't create many shared queries any more. My most
recent complex project has a grand total of one combo box saved
QueryDef. That's about par for the course.

2. Are there design rules or methods to keep queries efficient.


Saved or otherwise? In general, I use saved queries when I have a
common central set of data that gets used in multiple locations. I
then write an individual SQL string for each
form/report/combo/listbox that uses that saved query as a source.
This has worked quite well as it leaves the full control in the UI
object, but still allows a common core datasource that is edited in
one place.

Efficient queries in general is a huge question and deserves a
thread of its own, seems to me.

3. in a report is it better to just use the table as the reports
record source with dlookups for related data or to use ALL related
tables in a query in the reports datasource?


As Allen said, DLookups are to be avoided. My philosophy of
DLookups() is that they should never be used in a SQL SELECT
statement. They only belong in the UI, as the Controlsource of a
control on a form or a report, or in code that is doing a single
DLookup() for some logical purpose (e.g., you might use a DLookup()
to see if you have existing records matching certain criteria).

A DLookup() in a SELECT statement will likely execute once per each
row returned (as long as it accepts row-based arguments, which is
the usual scenario), and that's just way too inefficient. If the
data you're looking for is accessible via data stored in the current
records in your SELECT statement, then you can join the data and get
to it directly, without needing to look up the values row-by-row.
With a join, Jet will pull the indexes for the joined tables, and
return in a batch the records that need to be joined. So, if you
join two tables, you're likely pulling two sets of data in large
batches. With a DLookup(), Jet pulls the set of data and then Access
executes the DLookup() for each row of your dataset. By the time
Access *starts* executing the first DLookup(), you could likely
already have the full resultset with the joined version.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #6  
Old October 1st, 2009, 03:13 PM posted to microsoft.public.access.tablesdbdesign
Armen Stein
external usenet poster
 
Posts: 507
Default Row source Best practice??

On 30 Sep 2009 02:12:38 GMT, "David W. Fenton"
wrote:

They only belong in the UI, as the Controlsource of a
control on a form


Another nice thing about a DLookup on a form is that they are
asynchronous. That is, you can have one (or any function call, for
that matter) as a column on a continuous form. You'll see the other
columns appear, then a moment later (while you see "calculating" in
the status bar) the calculated columns will appear. This means that
you can navigate, double-click, etc. without waiting for the
late-arriving values.

The trade-off is that you can't sort on a Controlsource column. If
you want to allow the user to sort on that column, you need to do it
down in the query and try to make it go as fast as possible.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

  #7  
Old October 2nd, 2009, 02:41 AM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default Row source Best practice??

David

ThankYou for the info regarding my post..

Orginization (or my lack of) is deffinately a good reason to keep SQL
attached to what it is used for

Barry



"David W. Fenton" wrote:

Allen Browne has largely provided fine answers to your questions,
but I'd like to add a number of points.

=?Utf-8?B?QmFycnkgQSZQ?= wrote
in :

1. will all of these queries eventually cause me problems or do
they really not matter??


It depends on your app. I used to make saved queries for everything,
on the theory that they were more efficiently optimized. But since
Jet saves optimization plans for recordsource and rowsource queries,
there was never much advantage there.

Where you have the same rowsource used on multiple forms, it does
make sense to use a saved query for that as long as there's never
any reason the instance of your combo box ever need to fork. I
recently screwed up a combo box on one form that used a saved query
that I edited for use in a combo box on a different form that needed
an altered rowsource. I simply forgot that the changes I was making
to the saved query were going to impact the other use of it.

So, you really have to keep track of your usage of shared QueryDefs,
because the sharing is a two-edged sword -- great when all uses are
identical, problematic when there needs to be variation.

In general, I don't create many shared queries any more. My most
recent complex project has a grand total of one combo box saved
QueryDef. That's about par for the course.

2. Are there design rules or methods to keep queries efficient.


Saved or otherwise? In general, I use saved queries when I have a
common central set of data that gets used in multiple locations. I
then write an individual SQL string for each
form/report/combo/listbox that uses that saved query as a source.
This has worked quite well as it leaves the full control in the UI
object, but still allows a common core datasource that is edited in
one place.

Efficient queries in general is a huge question and deserves a
thread of its own, seems to me.

3. in a report is it better to just use the table as the reports
record source with dlookups for related data or to use ALL related
tables in a query in the reports datasource?


As Allen said, DLookups are to be avoided. My philosophy of
DLookups() is that they should never be used in a SQL SELECT
statement. They only belong in the UI, as the Controlsource of a
control on a form or a report, or in code that is doing a single
DLookup() for some logical purpose (e.g., you might use a DLookup()
to see if you have existing records matching certain criteria).

A DLookup() in a SELECT statement will likely execute once per each
row returned (as long as it accepts row-based arguments, which is
the usual scenario), and that's just way too inefficient. If the
data you're looking for is accessible via data stored in the current
records in your SELECT statement, then you can join the data and get
to it directly, without needing to look up the values row-by-row.
With a join, Jet will pull the indexes for the joined tables, and
return in a batch the records that need to be joined. So, if you
join two tables, you're likely pulling two sets of data in large
batches. With a DLookup(), Jet pulls the set of data and then Access
executes the DLookup() for each row of your dataset. By the time
Access *starts* executing the first DLookup(), you could likely
already have the full resultset with the joined version.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

  #8  
Old October 3rd, 2009, 02:09 AM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Row source Best practice??

=?Utf-8?B?QmFycnkgQSZQ?= wrote
in :

Orginization (or my lack of) is deffinately a good reason to keep
SQL attached to what it is used for


But that still means you're have a DRY problem, i.e., breaking the
"Don't Repeat Yourself" rule. In certain situations, that can be a
more serious drawback than the organizational problem.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.