Re: to specify a read-only Recordset in Access 2003 Visual Basic
- From: Tom van Stiphout <tom7744.no.spam@xxxxxxx>
- Date: Fri, 05 Dec 2008 20:10:17 -0700
On Fri, 5 Dec 2008 05:04:08 -0800, Dale Fye <dale.fye@xxxxxxxxxx>
wrote:
Indeed I was going to complain further about your database design. It
is really not up to standard and it is not productive use of time to
try to help you with a SQL problem while the obvious elephant is still
in the room.
I agree with Dale that especially if you are a novice programmer you
need to use the rules of normalization (e.g.
http://en.wikipedia.org/wiki/Database_normalization) to your
advantage.
Best of luck,
-Tom.
Microsoft Access MVP
In answer to your last question. Yes..
Assuming your form contains a date (and that field contains 12/15/2008 for
our example), then the query I gave you would identify all of the volunteers
that have not already been assigned to a Rota on that date. So, rather than
use your old queries, you could use this one, to present the user with a list
of those volunteers that are available for assignment on that date, rather
than presenting them with a list of all the volunteers, allowing the user to
select one, and then be told that individual has already been assigned (this
can be very frustrating).
The way the query works (lets start with the part inside the parenthesis) is
that it joint tblRotas to tblRotaVolunteers based on the RotaID. By doing
this you can then identify which ones are already assigned to any Rota on a
given date). You could save this as a separate query, but don't really need
to. You then joint that "subquery" to the Volunteers table, using a left
join from Volunteers to the subquery. By using the Left Join, you are
telling Access to return all values from the volunteers table, and to match
them up with those in the subquery. If there is a match, then it means that
that person is already assigned to a rota. So the final step is to limit the
result set to those records where the Volunteer table does not have a match
in the subquery (WHERE T.VolID IS NULL).
Your result is a list of those volunteers that have not been assigned to a
Rota on the specified date.
Having just looked at your reply to Tom, my bet is that he will tell you
your Volunteers table is configured poorly as well. In a well structured
database, you should never (well there might be some extremely rare cases)
use fieldnames to represent data. All of the "data" should reside in rows of
the table. In your case the columns that contain the Rota (I would assume
these are Yes/No data type, but could be wrong) are the problem. Had I
developed this, I would have a Volunteers table (which would contain VolID,
name, addres, phone, other pertinent information), and then I would have a
VolunteerRota table (VolID, RotaCode, IsAvailable) field. For each voluneer,
I would have one record for each of the Rota Codes, and the IsAvailable field
would be checked if they had volunteered for that Rota. The way you get data
into this second table would be as a subform on the Volunteer information
page.
Then, (assuming you have a combo box cbo_Rota on your form) you could extend
the query I gave you in my last post by adding a single row to the criteria
that looks like:
AND V.VolID IN (SELECT VolID) from VolunteerRota WHERE RotaID =
[Forms]![yourFormName].cbo_Rota)
- References:
- to specify a read-only Recordset in Access 2003 Visual Basic
- From: John Budding
- RE: to specify a read-only Recordset in Access 2003 Visual Basic
- From: Dale Fye
- RE: to specify a read-only Recordset in Access 2003 Visual Basic
- From: John Budding
- RE: to specify a read-only Recordset in Access 2003 Visual Basic
- From: Dale Fye
- to specify a read-only Recordset in Access 2003 Visual Basic
- Prev by Date: Re: Network access
- Next by Date: Re: Calendar
- Previous by thread: RE: to specify a read-only Recordset in Access 2003 Visual Basic
- Next by thread: Creating user level access based on department
- Index(es):
Relevant Pages
|
