[AccessD] How important is specifying exact fields

Hi John:

My rule of thumb is to always let the SQL Server do all the database work.
It is a lot faster and that frees Access up to do what it does
best...presents data.

Jim



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Saturday, June 18, 2011 6:18 PM
To: Sqlserver-Dba; Access Developers discussion and problem solving
Subject: [AccessD] How important is specifying exact fields

I am developing a strategy for selecting locations and AA meetings around a
prison camp.  My 
strategy is to have a table of cities "associated" with a camp through a m-m
table.  I also have 
camps "associated" with a volunteer through a m-m table.

Once I do this I want to select meetings / locations specific to the logged
in volunteer.  This 
involves a rather long set of joined tables as you can imagine.  Volunteer
to VolunteerCamp to Camp 
to CampCity to City to Location for the first view, and from there to
LocationMeeting.

I basically created the view, then I created a SP with a SELECT * FROM
MongoView WHERE 
MongoView.IDVol = @VolID.  @VolID is passed in to the SP and is used to
filter and return only the 
Locations (for the first SP) or the meetings (for the second SP)  The
results of the SP is then used 
in combos to select locations and meetings.

My question then is, how important is it to minimize the fields pulled along
the way.  I really have 
to have the VolID from the first (leftmost) table and many of the fields
from the last (right most 
table).

The point of this whole thing is to narrow down from hundreds or thousands
of locations and meetings 
only those relevant to the specific volunteer.

It just occurred to me I could have done this differently and "associat"
cities to each volunteer 
rather than to the camp.  This would allow each volunteer to decide what
cities (s)he cares about 
locations / meetings in.

In any event, the view / SP pulls a very small hand full of locations so do
I really care if it 
pulls a few fields I don't need in the end?  It seems like I might be using
the same query for 
several different SPs which require different sets of fields.

IOW should I custom build a view and SP for for each place I need it, such
as these combos?  Or 
build a hand full of more general views used in more places.

Also, if I do add more than the necessary fields in the View, can I narrow
it down in the SP?  IOW 
select the specific fields in the SP instead of Select * where every field
is necessary.

This is the first time I have started using views / SPs in SQL Server to
drive Access and so I don't 
really trust my instincts.

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com