Need help

[Dorish3]

Hello,

I am a novice with MS Access so I want to apologize ahead of time with
the fact that I know very little about Access.

I have an Access database that keeps track of volunteers and assigns
them out to specific events(this is a high end entertainment center).
They of course have volunteers that come in for specific events who
donate their time and they are generally assigned to take tickets,
tend bar, serve as ushers at various doors. Each volunteer has been
assigned job codes, some have 1 job code and some have several job
codes...depending on the experience they have had. Such as ticket
takers we have a code of TT, ushers the code is US, etc. Now I have
to produce a Roster report and on the report I have to list the
various locations such as for ushers where they would be assigned
to.....such as Left Lobby Door and a Right Lobby Door, Mezzanine both
Left and Right, Grand Tier Right and Left....etc. The number of
ushers they assign to each of these locations is dependant on the
house size....for instance a house size 1 would only assign 1 usher to
Lobby Left and 1 usher to Lobby Right, where a house size 2 would
assign 2 ushers to Lobby Left and 2 ushers to Lobby Right and so
forth....we go up to house size 6. I have used different queries and
subReports but still have not be able to solve the problem. I have
been pulling my hair out!!!!!

Can anyone help me with this problem?

Doris

[deaconj999]

On Mar 26, 6:25 pm, [e-mail] wrote:

Hello,

I am a novice with MS Access so I want to apologize ahead of time with
the fact that I know very little about Access.

I have an Access database that keeps track of volunteers and assigns
them out to specific events(this is a high end entertainment center).
They of course have volunteers that come in for specific events who
donate their time and they are generally assigned to take tickets,
tend bar, serve as ushers at various doors. Each volunteer has been
assigned job codes, some have 1 job code and some have several job
codes...depending on the experience they have had. Such as ticket
takers we have a code of TT, ushers the code is US, etc. Now I have
to produce a Roster report and on the report I have to list the
various locations such as for ushers where they would be assigned
to.....such as Left Lobby Door and a Right Lobby Door, Mezzanine both
Left and Right, Grand Tier Right and Left....etc. The number of
ushers they assign to each of these locations is dependant on the
house size....for instance a house size 1 would only assign 1 usher to
Lobby Left and 1 usher to Lobby Right, where a house size 2 would
assign 2 ushers to Lobby Left and 2 ushers to Lobby Right and so
forth....we go up to house size 6. I have used different queries and
subReports but still have not be able to solve the problem. I have
been pulling my hair out!!!!!

Can anyone help me with this problem?

Doris

Doris,

How big is your database, are you willing to email it so I can take a
look for you ?

Joe

[DavidB]

All depends on how well your DB is set up. If you have one table with
each voulnteer and somehow all the job codes in that table uyou are
pretty much screwed. If you have a well normalized databse it should
be easy! Can you decribe the table structure please and Im sure
someone can help you with the query to draw the data and display it
the way you want to see it.

On Mar 26, 1:25 pm, [e-mail] wrote:

Hello,

I am a novice with MS Access so I want to apologize ahead of time with
the fact that I know very little about Access.

I have an Access database that keeps track of volunteers and assigns
them out to specific events(this is a high end entertainment center).
They of course have volunteers that come in for specific events who
donate their time and they are generally assigned to take tickets,
tend bar, serve as ushers at various doors. Each volunteer has been
assigned job codes, some have 1 job code and some have several job
codes...depending on the experience they have had. Such as ticket
takers we have a code of TT, ushers the code is US, etc. Now I have
to produce a Roster report and on the report I have to list the
various locations such as for ushers where they would be assigned
to.....such as Left Lobby Door and a Right Lobby Door, Mezzanine both
Left and Right, Grand Tier Right and Left....etc. The number of
ushers they assign to each of these locations is dependant on the
house size....for instance a house size 1 would only assign 1 usher to
Lobby Left and 1 usher to Lobby Right, where a house size 2 would
assign 2 ushers to Lobby Left and 2 ushers to Lobby Right and so
forth....we go up to house size 6. I have used different queries and
subReports but still have not be able to solve the problem. I have
been pulling my hair out!!!!!

Can anyone help me with this problem?

Doris

[Rich P]

Have you created any tables yet? Entered any data? If you have not
created any tables yet, you could start by creating a table to store the
names of your volunteers which could include an autonum field to assign
each volunteer an ID column then a FirstName column, LastName column,
and maybe some personal data like home address, teleophone. Name this
table tblVoluneerData (note: best not to have spaces in the column names
- if you need a space then use an underscore - First_Name)

Then create another table which would contain the and ID column that
would be the value of each volunteer for each position the volunteer
has, create a Position column, and it would look something like this:

tblVolunteer
ID Name
1 Joe Smith
2 Sue Jones
3 Mark Adams

tblPosition
ID Position
1 Usher
1 Doorman
1 Receptionist
2 Usher
2 Ticket Taker
3 Usher
3 Doorman
3 Security

You could have an additional table where you store the location of each
volunteer

tblLocation
ID Location
1 Lobby
1 Mezanine
1 Left Door
2 Left tier
2 Right Tier
3 Grand Tier
3 Right Door

One thing to avoid is to collect duplicate/redundant data like

Name Position Location
Joe Smith Usher Lobby
Joe Smith Usher Mezanine
Joe Smith Doorman Lobby
Joe Smith Doorman Mezanine
J. Smith Doorman Left Door
...

here the name Joe Smith and all the information pertaining to him is
being entered several times, along with the Positions... The problem
here is that one entry may be Joe Smith, the next entry might be J.
Smith, another entry might be Joe A. Smith... so you lose consistency.

Access is a Relational database management system (RDBMS) for desktop
computers. The key feature of an RDBMS is that the data gets entered
only one time, and then it gets propagated throughout the system by
queries or by programming code of the RDBMS. The RDBMS will not change
the data once it is entered. But multiple entries of the same data
always gets changed which makes it very difficult to report on with any
degree of accuracy.

In case you care, the concept underlying RDBMS is called "Normalization"
- the concept of eliminating redundant data. Once you have some tables
with data and need help with writing queries to query the data you can
post a question that is a little more specific.

hth
Rich

*** Sent via Developersdex http://www.developersdex.com ***