Need help with creating a history table in access

[Lilo27]

I have 3 tables
tblShares has information tracking a windows 2000 share (Share Name, Owner of the share, size, etc.)

tblGroups has information to track groups (security permission groups) associated with a share
(Group Name, owner, etc..)

tblJoin is a junction table that joins tblShares and tblGroups because there is a many-to-many relationship with both. One share can have several security groups and one security group can belong to many shares.

What I need to to is set up a history table to track when the security permissions on the shares and the groups are reviewed. I need to track every time a share is reviewed along with every time a group is review associated with that share. For example, share 1 was reveiwed on this date and has group1 that was reviewed on this date, group 2 that was reviewed on this date...etc. How can I do this in a history table? Also, would I need to link this history table to the junciton table so I could keep track of all shares and all groups?
Any ideas?
Thanks, Lilo

 

[BSman]

I'm not sure exactly what you are referring to when you say "Share", but I would suggest that each table needs an autonumber ID column.

Then make a history table with the following columns:

ShareID: the ID of the record in tblShare
GroupID: the ID of the record in tblGroups
JoinID: the ID of the record in tbleJoin
StatusDate: the date/time of the review
Remarks: comments about the review
Reviewer: who did the review

Note that you should set up (on the form) a test to be sure that there is data in only one of the three ID fields.

 

[Lilo27]

A "share" is a folder on the network that is shared among everyone. Sorry I should of given you more info but tblShares does have a Share ID (autonumber) and tblGroups has a Group id (autonumber). TblJoin is the junction table that has Share ID and Group ID. It is linked to the tblShares and tblGroups by a one-to-many relationship in order to create a many-to-many relationship with tblShares and tblGroups. What is the join id for? The problem that I have is I have to track multiple reviews for each share along with information for every review So for example, share A can be reviewed on this date with group a, group b Group A and group b will also have a date associated with them. Then another record Share B can be reviewed on a date and have group a, group b and group c. Should I have a seperate history table from the join table that tracks each review separately or should the join table be used to do track this?

 

[BSman]

OK, how about this:

In your history table, drop the JoinID. Always require a ShareID, since that's what you will be reviewing. The GroupID column will identify which group did the review. You might add a ResultID column that can use a lookup table with ResultID and ResultDescription. Examples of ResultDescriptions might be "No changes", "Added users", "Dropped users". This would enable you to sort the reviews to indicate how many times changes were needed (and the categories of changes) versus how many reviews didn't need any changes.

 

[Lilo27]

Bsman,
Group doesn't refer tot he person reviewing. It refers to groups on the share that are also getting reviewed. Also, I will make ShareID required. Also, we review each share and group whether or not they need changes. I have changed the tblJoin table structure a little and added a couple of tables. this is my strucure

tblShares
Share ID - primary key
Share Name
Server Name
Owner of share

tblGroups
Group ID - primary key
Group name
Group owner

tblShareReview
Share Review ID - primary key
SHare ID - same as entry from tblShares
Review check out date
Review completed date
Review person

tblGroupReview
Group Review ID - primary key
Group ID - key from tblGroups
Group review date
group check out date for review
group check out person

tblJoin
ShareReview ID - same as the one in tblShareReview
Groupreview ID - same as the one in tblGroupREview

Here's the logical info...
One share can have many reviews
One group can have many reviews
One share can contain many groups
ONe group can belong to many shares
Shares and groups are reviewed togehter usually

I will need to query the last time a share was reviewd or the last time a group was reviewed as well as view all the information about a share review

The relationships I have st up are
One-to-many with TblShareReview.ShareReviewID to tblJoin.SHareReview ID

One- to-many with TblGroupsREview.GroupReviewID to tblJoin GroupReviewID.

One-to- many with tblGroups.GroupId to tblGroupReview.GroupID

One- to-many with tblShares.Share id to tblShareReview.Share ID. I cant force referentail integirty for some weird reason

I was hoping by these relationships that tblJoin would have a record for every review, but its blank..?

What I want to do is be able to see allt he informtain for a particular share review. ex... share 1 was reviewed on this date with this group on this date, this group on this date. Do I need another key in the JOin table? Also, do the relationships look correct?

thx
lilo27