to specify a read-only Recordset in Access 2003 Visual Basic

[John Budding]

I'm trying to program a Database with Access 2003 where Several Rotas may
occur on a Weekly, 2-Weekly or Monthly basis staffed from a pool of
volunteers.
More than one rota, coded A, B, C (for example) may occur on the same Date.
The Rota Table's primary Key is Date and RotaCode.
A Volunteer may not appear in more than one rota per day.
Forms indicate how many Volunteers are required for each particular Rota.
Example Rota Table of records -
01/01/2008 Rota A Volunteers 01, 20, 25
01/01/2008 Rota C Volunteers 03, 21, 30, 11
01/05/2008 Rota B Volunteers 01, 21
01/05/2008 Rota D Volunteers 20, 30, 03
01/12/2008 Rota D Volunteers 03, 05, 15
New Rota 01/01/2008 Rota F (no volunteer already on Rota A or C for the date
may be picked)
Rotas are populated with Queries which extract / create records based on the
Primary Key's component parts.
Thus, when generating any Rota, another Rota may already exist for a
specific date in the Rota Table.
When I start creating a new record, I can save the primary key of the new
record in the Table, so that I may then immediately extract any other records
containing the same date.
I can then compare any Volunteer being added to the new record, to ensure
they do not exist on these extracted records.
I need to specify a ?read-only? recordset in Visual Basic which will obtain
any other records containing the same date, so that I can construct a
variable table of all volunteers allocated to rotas on that date, by RotaCode
and Volunteer Code, for validation purposes, and then set this variable table
to nothing as I start to create the next new record.

 

[Tom van Stiphout]

On Wed, 3 Dec 2008 20:07:02 -0800, John Budding

I couldn't quite follow your story. If you have a table:
tblRotas
RotaDate PK
RotaNumber PK
Volunteer1
V2
V3
V4
etc.
then that is really bad design and you should fix that before
continuing.

I'm not at all sure you need a readonly recordset, but if you do:
OpenRecordset(..., dbOpenSnapshot)
will do that.

-Tom.
Microsoft Access MVP

 

[Dale Fye]

Agree with Tom regarding your table structure.

Personally, I prefer to use surrogate keys (an autonumber field that allows
me to refer to a record by a single value). I then have unique, multi-field
indexes which accomplish the task of making sure you don't have two copies of
the same Rota on the same day. I think you need two tables

tblRotas
RotaID
RotaDate
RotaCode (A, B, ...)

and

tblRotaVolunteers
RotaID
VolID

Then, you can create a query to give you the available volunteers for a
particular date by writing a query that looks something like:

SELECT V.VolID, V.VolLastName
FROM tblVolunteers as V
LEFT JOIN (SELECT RV.VolID
FROM tblRotas as R
INNER JOIN tblRotaVolunteers as RV
ON R.RotaID = RV.RotaID
WHERE R.RotaDate = #12/15/08#) as T
ON V.VolID = T.VolID
WHERE T.VolID IS NULL

You could modify this query to look at the date field of your form, and then
use this as the RowSource for a listbox. Then, you wouldn't have to check to
see whether a volunteer was available, you would already know who was
available.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[John Budding]

Perhaps I rather shortcut the explanation of the database
First off . . .
There is a Table of Volunteers by NAME whose Autonumber is used within the
Rotas Table.
the Volunteers Table indicates by Rota Codes which rotas the volunteer is
available for as Autonumber, Volunteer name, RotaCodes A, B, C . . . G in
fixed positions within each record (using a "Volunteers Form")
example
01 John Brown | A | | C | | | F | |
02 Amy Smith | | B | | D | E | | G |
03 Mary Jones | A | | C | D | | | |
04 James Mates | A | B | | | E | F | G |
Secondly
Queries select these volunteers into a variable number of combo boxes
(showing the names) in the appropriate "ROTA form" depending on the Rota
being constructed
This then gives me the Rota Table containing the autonumbers of the
Volunteers shown in my original question.
(the number of volunteer fields in the RotaTable is determined by the
maximum volunteers for the largest rota, unused fields in smaller rotas are
set to 0 (zero) by default.
I need to validate, as I construct any dated record for any one Rota, that a
volunteer has not previously been selected for another Rota due on the same
date.
It's this recordset I need to work with in VB as I add each volunteer's
autonumber to the new Rota record (how do I specify the various fields within
these records to VB ?)

The Published Rotas then currently work by a query selecting all records for
any one RotaCode and listing them in date order, showing the Volunteers who
have been selected for that rota. (The reporting part of the database works
fine ! )

 

[John Budding]

Thank you: the response to your first criticism about tables is (I hope)
shown in my reply to Tom.
This then stops me adding a second copy of a dated rota record, but does not
stop me adding a Volunteer to MORE than one rota on any ONE date - this is
the problem I am trying to solve . . . :- )

I'm not so familiar with SQL to understand your code - does it effectively
replace the queries that offer me the volunteers who could be shown in the
combo boxes I currently use when constructing the Rota record ?

 

[Dale Fye]

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)

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Tom van Stiphout]

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)