Database Design/Report Issues

[j2curtis64]

I've created a database that I am using to track issues. I have two tables.



Contact table that consist of the following columns:

ID, full Name, Position, Site, HierLevelSix, and CC

Issues table consists of the following columns:

Monthly, CH_ID, AO_ID, DateIssueOpened, LastUpdateDate, RequisitionNumber, TransactionNumber, Category, Status

I am using a form to insert records in the Issues table.

To not have duplicate information in both tables, I am using the CH_ID and AO_ID fields in the issues table. These ID's correspond to the values in the contacts table (ID).


Here is a record from the contacts table

ID LastName Position Site HierLevelSix CC CREDIT LIMIT Card Status
161 STEVE STEPHENSON CH DN 12345 888888 $1.00 A
158 MARSHA BRADY AO DN 12345 121212 $2.00 A


Here is a record from the Issue table

ID MonthlyPackage CH_ID AO_ID DateIssueOpened LastUpdateDate RequisitionNumber TransactionNumber Category Status
19 01-Dec-10 161 158 24-Mar-11 03208324 1 Justification INFO Closed


So how would I link the two tables to get good information for a report? For each issue opened there are two people assigned (Position-CH, AO).

I would like to show in a report Month, LastName, Position, Site, CC, DateIssueOpened, LastUpdateDate, RequisitionNumber, TransactionNumber, Category, and Status

Thx for your assistance

[jzwp11]

For each issue opened there are two people assigned (Position-CH, AO).

If an issue can have more than 1 person associated with it, then that describes a one(issue)-to-many(persons) relationship which by normalization rules requires a separate, but related table

tblIssues
pkIssueID primary key, autonumber
MonthlyPackage
RequisitionNumber
TransactionNumber
Category
Status

tblIssuePeople
-pkIssuePeopleID primary key, autonumber
-fkIssueID foreign key to tblIssues
-fkPeopleID foreign key to your contact table
-fkRoleID foreign key to tblRoles

tblRoles (2 records CH and OH)
-pkRoleID primary key, autonumber
-txtRoleName

Additionally, the two fields in your issue tables (DateIssueOpened
LastUpdateDate) imply 2 actions: opening and some sort of follow-up. Again this is a one(issue)-to-many(actions) relationship

tblIssueActions
-pkIssueActionID primary key, autonumber
-fkIssueID foreign key to tblIssues
-txtAction (text field to hold the description of the action)
-dteAction

If you have the same actions re-occurring for all issues, you many want to put all possible actions in a table and then reference the action with a foreign key

tblIssueActions
-pkIssueActionID primary key, autonumber
-fkIssueID foreign key to tblIssues
-fkActionID foreign key to tblActions
-dteAction

tblActions (records such as Open, closed, called etc. i.e. any possible action that can take place on an issue)
-pkActionID primary key, autonumber
-txtAction

For your report, you will need a query that brings the relevant data together from the various tables.

[j2curtis64]

Thx.

I am new at this. How do i set a one-to-many relationship in the relationship window? Please see attachment for relationships.

What field from contacts should i link with?

[jzwp11]

When you join two tables with a line as you showed in your attachment, you create a relationship. I would go back to your diagram and enforce referential integrity. When you bring up the window to check the referential integrity check box, you will see a button for Join Type. If you click that you should see the type of join Access created. It probably defaulted to a one-to-many (show records of both tables where the key fields match).

The only problem I see is that you are joining the issues table and the issues_1 table to the issuepeople table. You only need 1 join, so I would remove issues_1. The join has to be from IssueID (primary key) field of the issues table to the issueID (foreign key) field of the issuepeople table

[j2curtis64]

Thx.

Now I want to populate the tables using a form. How do I set up a form for doing this? Currently, when I enter the information in the form the data is only stored in the Issue table. The other tables are blank.

[jzwp11]

In general, the one side of a one-to-many relationship is shown in the main form while the many side of the relationship is shown using a subform.

So in your case you can use your form based on the issues table. You would then need to create another form based on the issuepeople table. I usually use the wizard to do most of the work. Then in design view replace the control bound to fkPeopleID with a combo box that uses the contact table as its row source. Make sure to include the primary key field (ID) of the contact table and bind that to the peopleID field of the issuepeople table. If you use the combo box wizard, it will walk you through the process. Save the form & close it.

Now open the issue form in design view and then click on the name of the form for issuepeople you created above. Drag it into the issue form. If your relationships are properly set up, Access will automatically link the subform to the main form via the issueID fields in the respective tables.

[j2curtis64]

hello,

it doesnt seem to be working.

1). For some reason Access keeps displaying a dup copy of Issues. see 1).

2). The approving official and card holder fields are unbound. I have this so the person entering the issue can associate the issue to a approving official&cardholder.
I don’t store it in issues table since the persons name is stored in contacts.

When I enter a name in the drop down list below (approving official, card holder), issues people subform is not updating. When I save and close the form, the information is saving to issues table but not to the other tables.

[jzwp11]

2). The approving official and card holder fields are unbound. I have this so the person entering the issue can associate the issue to a approving official&cardholder.
I don’t store it in issues table since the persons name is stored in contacts.

You have to select the approving official and card holders associated with the issue in the subform (an issue has more than one person associated with it). You would not have the two unbound controls in the main form.

[j2curtis64]

But the AP or CH's name isnt in the IssuePeople table, only the PeopleId. How will the end user know what to select?

If I select an ID number in the PeopleId combo box and try and save the record it's telling me "you cannot add or change a record because a related record is required in table "issues".

[jzwp11]

Something is not right in the way the form and subform are linked. If you right click the frame of the subform while in design view of the main form and then go to the data tab are the master and child field properties populated or not? If not, you can manually enter the primary key (master) and the foreign key (child), both will be issueID. Or you can post the database with any sensitive data removed and we can take a look at it.

[j2curtis64]

I couldnt tell. Here is a stripped down version of the DB. The form in question is "Issue Details (MAIN FORM AND SUBFORM)".

Thx so much for your assistance.

[jzwp11]

I'll have to take a look at it tonight since I only have Access 2003 here at work.

[j2curtis64]

Thanks again for your assistance.

[jzwp11]

You still had the issueID of the issue table joined to the peopleID of the issuepeople table so that messed things up. I also modified the combo box in the subform so that you would see the name of the person that corresponds to the ID value. The amended DB is attached.

[j2curtis64]

Jzwp11- thank you ....