Access Relationships

[Guest]

I believe I have a simple database to create but the correct end result
alludes me.
Here is the end result I need: (Each member can work mutiple days on mutiple
subcommittees)Below is how I would like my form to be set up.

SubCommittee Date
Member Name Hours Worked
Member Name Hours Wroked
etc...

I need a report at the end of the day that list each member and how many
hours they have worked on each subcommittte. And one other report that tells
me how many hours were worked on each subcommittee. I am desperate. I have
spent 3 full days and I keep getting close but "no banana." Could someone
take pity on me and please tell me how to do this?

 

[Jeff Boyce]

Dee Anne

You've described a form and a pair of reports. What is your (current)
starting point? That is, what is the data structure (i.e., tables) you are
pulling from to get these?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Hi Jeff,
Thank you for responding. My first table is Member Name, Address, telephone
#'s etc. The second table, I think, needs to be the different subcommittees
with the dates and hours worked. I think I may have a problem because my
subcommitte table is not poplulated with members on each subcommittee. As it
is, each member is on each subcommittee and chooses which subcommittee they
will work on each year.

 

[Joseph Meehan]

Hi Jeff,
Thank you for responding. My first table is Member Name, Address,
telephone #'s etc. The second table, I think, needs to be the
different subcommittees with the dates and hours worked.

I suggest that second table should be just subcommittees.

Then you will want a table for attendance showing a record for each
attendee for each meeting. It would be linked to the first and second
tables. With a link to the subcommittees' table, and the members' table.
Each record should have a link field (ID Number?) to each subcommittee a
link to the subcommittee (also an ID number) and the date (or other method
of identifying the meeting) and duration etc for the meeting. Under some
situations there may be an additional table for the details about the
meeting like subjects, time location etc.

With that kind of construction you should be able to prepare your
reports with little trouble.

I think I

 

[Guest]

I tried this Joseph but when I try to enter the data into the query I get an
error message that I can't find or replace now. Let me see if I set this up
correctly.

Table1 Table 2
Table 3
MemberID SubID
AttendanceID
MemberName Subcommittee MemberID
Address
SubID
Telephone
HoursWorked
email
Date Worked
My relationships are one to many from member to attendance.
Your help is greatly appreciated!
Dee Anne

 

[Jeff Boyce]

Dee Anne

I'd add to Joseph's suggestion...

You have Persons, you have Committees, you have Membership, and you have
Attendance. I suspect you need a table for each.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Let me try this again:
table One- MemberID, memberName, address, telephone
table two- SubID, Subcommittee Name
table three-AttendanceID, MemberID, SubID, DateWorked, HoursWorked

 

[Guest]

But what are the relationships between all of them?

 

[Joseph Meehan]

I tried this Joseph but when I try to enter the data into the query I
get an error message that I can't find or replace now. Let me see if
I set this up correctly.

That sounds like a relationship problem, not a table design.

 

[Jeff Boyce]

Dee Anne

I'm not there, how could I tell you what the relationships are for your
data?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John Gray]

"Dee Anne" originally wrote ...

Each member can work mutiple days on mutiple subcommittees [and]
I need a report at the end of the day that list each member and
how many hours they have worked on each subcommittee. And
one other report that tells me how many hours were worked on
each subcommittee.

Following on Joseph's and Jeff's comments...

If we describe your situation with a series of statements like this:

- *many* Persons can have Membership in *many* Committees.
- *each* (one) Committee can have *many* Meetings.
- *many* Persons can Attend *many* Meetings.

then it's apparent there are 5 tables: Persons, Membership, Committees,
Meetings, and Attendance. Relationships should be evident from the
descriptions (note the "many" and "each" words).

- The Persons table would have personal info only (name, address, etc).
- The Committees table would have committee names and the function
of each committee.
- The Membership table is a junction table between Persons and
Committees. This would hold foreign keys from Persons and
Committees, to indicate which committees a person belongs to.
You would probably also have "start" and "stop" dates, to show
the time period for a person's membership on a committee.
(You could also indicate member "status" here, eg: Chairman,
Secretary, and so on.)
- The Meetings table would hold meeting info (date, place, start time,
end time, etc), along with a foreign key from Committees, to indicate
which committee was having the meeting.
- The Attendance table is a junction table between Persons and
Meetings. Similar to the Membership table, it holds foreign keys,
but from Persons and Meetings in this case, to indicate which
meetings a person attends.


If you build a table structure along these lines, you should have little
difficulty in creating time and attendance reports (and more, if desired).

hth,
John

[/QUOTE]
[...]