Small Database Design Problem

[PalmerR]

Hi all,

I'm very much a 'newbie' at using MS Access, but have followed a few tutorials now and am confident that I now have a reasonable grasp of the basics; enough, I hope, to be able to create the database & report I need. However, I can't quite wrap my head around how exactly I design the DB to do what I need, and was rather hoping that someone here could offer some sage advice. The problem is as follows:

I have a list of members, who attend a meeting each week, for which a simple record is kept to include 5 different 'types' of attendance (such as present, absent, sent someone in their place, etc); and a numerical record of any business referrals generated through the meeting. I would like to create a report to show the totals for each member, generated on a monthly, quarterly, and annual basis. The problem is, I just can't figure out how I create a unique dataset for each member, every week, without creating a dedicated table for each meeting date.

For clarity, the total data each week includes:
Date of Meeting
Member Name
Attendance Type - Present, Absent, Medical, Substitute, Visitor
Referrals Given Internally
Referrals Given Externally
Referrals Received
Total Referrals

Could you please give me some guidance on how I would go about designing a database, so that I can then create queries or reports for any given date range (I'm happy to figure that bit out myself through tutorials, etc., but need to get the design right first!).

I'd really appreciate any assistance offered. I've been at this for two days as a favour to a friend, and although I think I have the basics down, I'm stumped at how to design it!

Many thanks,

Palmer R

Edit: for clarity, I'm using Access 2007

[burrina]

Here is a link with a lot of Schema that may help you.
http://www.databaseanswers.org/data_models/

[John_G]

Hi -

There are two scenarios; one is that the meetings are all the same kind, in terms of location, audience, cost, sponsor, etc., and that the details of each don't matter, except for the date.

The second scenario is that the meetings are different in many details, and you want to keep track of who goes to which ones, etc.

For the first scenario:

Two basic tables, Members and Meeting_Attendance, with the relationship being one-to-many (one member attends one or more meetings)

The data in the first is obvious - all the relevant details of each member. Assume there is a unique Member_ID for each.

The other table Meeting_Attendance, would have these fields:

Attendance_ID
Date of Meeting
Member_ID
Attendance Type - Present, Absent, Medical, Substitute, Visitor
Referrals Given Internally
Referrals Given Externally
Referrals Received
Total Referrals

Notice that I identified the members by ID, not by name.
Attendance_ID is a unique, usually numeric identification (ID) assigned to each attendance record.


The second scenario is only a bit more complicated - it requires three tables

You have Members and Meetings, with the Meetings table containing details of each meeting (including date - see below). The relationship between these two is many-to-many: a member can attend many meetings, and meetings can be attended by many members.

A many-to-many relationship requires a third table, which in your case is the Meeting_Attendance table, which might look like this:

Attendance_ID
Meeting_ID
Member_ID
Attendance Type - Present, Absent, Medical, Substitute, Visitor
Referrals Given Internally
Referrals Given Externally
Referrals Received
Total Referrals

Notice I replace the Meeting Date with Meeting_ID. You don't need the date here, because you already have it in the Meetings table.

Either arrangement will allow you to extract all the summary data you need using MS Access queries (you would be using the "totals" feature, I think).

This should get you started - post back if you need more info.

John

[orange]

Further to the responses above, don't use embedded spaces (or special characters) in your field and object names.
Use something like ReferralsGivenInternally -- it will save you syntax issues in the long term.

[PalmerR]

Thank you all for your input, you've each helped a great deal! The schemas will undoubtedly come in useful later on, and I can see the wisdom of keeping the syntax as simple as possible. Thanks for both tips.

John, it's the first scenario. I was convinced that there was a more elegant, two-table solution than my own cumbersome design, but had no idea what I was missing. The AttendanceID field is the key to the problem (I've just realised that it is, literally, the primary key... no pun originally intended, I assure you), so thank you for taking the time out to give such a complete answer.

Once again, I really appreciate everyone's help. Thank you all.