Help with ideas for database layout (contract quality management)

[Roman]

Hi all! This is my first post in the Access forum.. I'm very good with Excel, but seem to be having a hard time finding my way around Access. Right now, I am stuck with 3 different Excel workbooks. One is a listing of contracts which need to have quality data analyzed, one with the quality data for the contracts, and one which organizes the contracts based on owner/location in the organization.

Would an Access database be the answer to this cumbersome situation? If so, any ideas on how to organize this thing so it makes sense and is cohesive? The quality metrics will be collected at different intervals (some monthly, some quarterly, some bi-annually, and some annually). Each contract may have anywhere from 1 to 10 quality metrics, and each metric may have a different collection interval. I would need to be able to leave comments regarding attempts to collect the quality data, and have the capability to run reports based on non-performing contracts, where the contract is located, organization-wise, and who owns the contract.

Each contract has heaps of data attached to it (start date, end date, value, type of contract, vendor, supplement number, description, status, cost center, contract manager, and notes).

Any ideas?

These results also have to be broken out into yearly (2012, 2013, 2014, etc).

[ssanfu]

You might check out the data models by Barry Williams at
http://databaseanswers.org/data_models/

If you don't see anything close to what you want, I think you can email him (see the other pages) to get help/ a design.

There are tutorials on designing at
http://databaseanswers.org/tutorials.htm

[June7]

Access is intended exactly for this - management of relational data.

Review 'sticky' thread tutorials at http://forums.aspfree.com/microsoft-access-help-18/

Design a data model and if you want, post it for review.

[Roman]

Thank you; I'll get started reviewing those links.

[orange]

I suggest you read this approach and example also
http://www.rogersaccesslibrary.com/T...lationship.zip

If you don't find an existing model at Barry's site that meets your needs (no one model may do it all = you mat need pieces from several and then some customization), the link above will lead you through the process.

I have suggested videos to others to familiarize them with the concepts involved.

For erd this is a great tutorial
http://www.rogersaccesslibrary.com/T...lationship.zip

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation. The example relates Customers, Orders and Items.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck with your project.

[Roman]

Thank you! All of this information is wonderful!! The only thing that I'm having a hard time conceptualizing is how to integrate the year into the model. I have the whole thing conceptualized, but would I need to make a whole different subset for every calendar year? Thoughts?

[orange]

Make Year an attribute of the table and you can retrieve info for selected year. More specifically you would have a ContractDate or similar and retrieval by Date is then possible.

There is a good article on naming conventions at
https://skydrive.live.com/?cid=cc4b0...OoLSHjXEuh4Eao

you should read it before naming fields and tables.

good luck.

[Roman]

Ok, I have put this together so far. Here is a screenshot of the relationships.. any advice? I feel like, after reading through those tutorials, as if there needs to be a looping of the relationships. Which I do not have. Is that necessary, or is this ok?

[orange]

You could be missing some entities, but we have not seen a write up with any detail of what exactly you are tryiiing to do.

I see a few things that suggest more detail is needed. Are these things defined? Where do they fit? What Status values do you plan on having? Do you have test data? Did you try working the sample data through your model? Can you get the necessary outputs from your model?

Contractor
Status
CostCenter
ContractManager
Contact
Department

Did you read the naming standards article?

[Roman]

The purpose is to input 250 specific contracts, cite three to four quality measures (some contracts may share quality measures) per contract, and then collect quality data in order to ascertain whether the contracts are performing up to specification.

The status values can be "fully executed," "edit mode," or "pending approval." I have some real data I can put through the database to test it, which I will do shortly.. and I haven't thought about the outputs yet, though this will be crucial.

I did not see a naming standards article.. I'll look for one of those, and correct the naming I have so that it is more programming-friendly.

Off topic question.. is it possible to set up a table with the Contractors, list which contracts belong to which contractors, and then auto populate (or query) the result, instead of putting it in the way I have listed? Or is the way I have the easier way? I would have to do this with department, contact, contract manager, cost center, and contractor.. Hmmmm...

[orange]

You need to read the article on entity relationship diagramming.

Please go back and review my posts #5 and #7.

[ssanfu]

I did not see a naming standards article..

There are many naming conventions. The main thing is to be use one and be consistent.

* "ID" as a field name is not good..... "ID" of what??

* Don't use spaces or special characters in names. Bad: "Contract ID" ... Good/better: "ContractID" or "Contract_ID"

* Beware of reserved words... (ex. "Type", "Date", "Year") . Here is a list >> http://allenbrowne.com/AppIssueBadWord.html
(If you have a naming convention - and use it - reserved words shouldn't be a problem. )
Having a field named "Description" in two tables gets confusing - for you and Access. "Description" of what??

----------------
A friend of mine names tables like "Employee_Name_Def", "hour_entry_type" or "company_carrier_link" (junction table).
The field names are prefixed with the letters from the table name :
- in table "Employee_Name_Def", the field names are: "end_FName", "end_LName", "end_City".....
- in table "hour_entry_type", the field names are: "het_desc", "het_factor", "het_rate".......

A little weird in the beginning, but you always know which table the field is from. Queries are a lot easier to read/understand.
Now it makes perfect sense to me.

[Roman]

Thank you! I have changed those. That makes perfect sense. Screenshot below..

I'm still not sure about whether or not I need more entities in order to create a loop. Am I just being OCD or do I need to create the looped relationship?

[ssanfu]

if there needs to be a looping of the relationships.

or do I need to create the looped relationship?

Haven't got a clue about what you mean.

[Roman]

I'm referring to the image below. In this example, the "relationships" are contained in a loop, not in a linear structure. Is that necessary, or best practice? Or is it just a coincidence that this relationship is a loop?