Help with many to many relationship setup

[lonerjohn]

My office has an Excel spreadsheet that contains information they want to turn into a database. Some of the Excel cells contain numerous pieces of information that I'd need to store individually in Access. So, I've been advised to do many-to-many relationships. My tables are:



tblMain: contains all of the fields across all of the tables. Primary Key is RecNum (autonumber field)

tblContracts: contains ContractNum, PayeeName, ContractType, PriorityCategory and 13 fiscal fields to indicate values of the contracts. ContractNum is PK.

tblPayees: contains PayeeName and ContractNum. PayeeName is PK.

tblCountyDetails: contains the County (there can be more than one for each record) and ContractNum. County is currently the PK.


In setting up the relationships, I have the infinity sign coming out of tblMain and going to each of the tables. (tblPayees linked by PayeeName, tblContracts listed by ContractNum and tblCountyDetails linked by County.) I also have Cascade Update Related Fields and Cascade Delete Related Records both checked.


I currently have no records in the database because I want to make sure users can create brand new, non-existing records.


I have a form linked to tblMain. There are two subforms on the form; subContractDetails (which is in datasheet view) and subCounties (which is a continuous form and is one field). In theory, subCounties will allow the user to enter in multiple counties for each contract. subContractDetails will allow the user to enter in the financial values for each of the contract periods of that specific contract.


The problem I'm running into is when I try to create new records, it will not allow me to do so. It keeps telling me that I don't have related records in tblContracts. I've only worked with a single one-to-many relationship before. I've been at this for 3 hours trying to figure out what I'm doing wrong. Could anyone help? I can't be that far off.

[twinnyfo]

Before I get too far into this thread, I must ask a few questions to understand the databse a bit better.

First, what is this database "for"? If we have a general idea of what you are trying to accomplish in the big picture, we can understand better how to set up your tables. Also, knowing what types of data are supposed to be in which tables will be helpful.

Second, You have ContractNum in your Payee and CountyDetails Tables, which is a bit confusing. (Not that it should be), but if ContractNum is the same as that found in tblContracts, shouldn't it be related?

Third, tblMain has addresses in it. This is odd for a "Main" table.

Fourth (again, as mentioned in a previous post), tblContracts has field names for FY14-FY25--in principle, this is a bad idea, as over time your tables will get too wide to handle. However there may be ways to resolve this.

But again, knowing what this is for and how you want things to work will be helpful.

My initial thought is to hold off on solving your immediate problem until we fix the structure properly.

[lonerjohn]

It's to keep track of the contracting information that we need to report on. For example, we will likely have to report all contracts of a specific type (Attorney) for a specific range (only values from FY1415-FY1819) that serves a specific county (New York). I would then need to know the total dollar value of each payee, as well as a total value of all the contracts that fit into the range specified. Everything will be Text with the exception of the FY fields, which would be currency and of course, the autonumber fields.

I threw ContractNum in both Payee and CountyDetails tables so that I could accurately verify that the correct contract information is being pulled. A Payee could have dozens of contracts, but the ContractNum would be unique. ContractNum would be the same value throughout all tables.

The way I was taught (which is probably why I'm looking for assistance) is that tblMain should be the "catch all" table that includes all of the fields needed for the database.

Would you suggest a table for each FY?

[PhilOfWalton]

I agree with Twinny, so await further information.

A couple of small points. Sending your relationship view is always a great idea, but it would be helpful if we could see all the fields in tblMain. My gut feeling is that the database is far from being normalised, and that will make things difficult.

There is nothing wrong with having an alpha primary key, but this can slow searches down. Much better to have a long number (AutoNumber) as the primary key.

Phil

[lonerjohn]

Here are the fields from tblMain

[lonerjohn]

Given the suggestion to use a long number as the primary key, I think ContractNum would be the best bet. ContractNumbers will be unique, will start with the letter "C" and will be followed by either a 6 digit number (i.e. C123456) or a 5 digit number followed by the letters "GG" (i.e. C12345GG).

[twinnyfo]

Rather, what Phil is suggesting (my guess--correct me if I am wrong, Phil), is the the PK for tblContracts would be a long integer, Auto-Number. The Contract Number can be whatever you want it and user- (or DB-generated). Having a Long Integer PK aid in searches and is simply a "better" means for linking tables together.

[twinnyfo]

Also, from your first post, based upon how you are setting up your tables, DO NOT set Cascade Delete of records. This could be disastrous to your db if you were to accidentally delete something you shouldn't.

[twinnyfo]

tblContracts
ContractID      AutoNumber
ContractNum     Short Text (user-generated)
ContractTypeID  Long - FK to tblContractTypes
CountyID        Long - FK to tblCounties
AttorneyID      Long - FK to tblAttorneys
PriorityCatID   Long - FK to tblPriorityCategories
PriorityNotes   Short Text
PayeeID         Long - FK to tblPayees
Amount          Number
SettlementDate  Date
====Not sure if these belong to payee or not:
SFSVendorID
FederalID
CharitiesReg

tblPayees
PayeeID        AutoNumber
PayeeTitle     Short Text
PayeeFirst     Short Text
PayeeMiddle    Short Text
PayeeLast      Short Text
PayeeSuffix    Short Text
PayeeFullName  Short Text (db Generated)
PayeeAddress1  Short Text
PayeeAddress2  Short Text
PayeeCity      Short Text
PayeeState     Long - FK to tblStates
PayeeZIP       Numeric, but formatted for ZIP+4

See where we are going here?

I wouldn't even worry about the FY fields, as if you are tracking totals over FYs, that is something for you to do during queries and reports--not in Tables.

Every time you enter a Contract, you enter the data specific to the Contract. Since several contracts can apply to one payee, the payee is in a separate table.

This will take a while to sort all things out, but if you are patient, we can work through this slowly, but thoroughly.

[PhilOfWalton]

That structure looks better.

However, in view of the fact that a contract can be in more than one county, you need a table of Counties and a table of JoinContractCounty.

TblCounties
    CountyID     AutoNumber     Primary Key
    CountyName   Text           Unique  

TblJoinContractCounty
    ContractID          Long      Joint Primary Key
    CountyID            Long      Joint Primary Key
    ?????

There may be other fields which are currently in Twinny's TblContracts. We will see, as more information comes in.

Also I do not like PayeeFullName Short Text (db Generated) in the TblPayees. As he says, it is a calculated field, and very very rarely should they be stored in tables because if for example you change PayeeFirst, and don't change PayeeFullName there is a conflict.

Tut tut Twinny

Phil

[twinnyfo]

Yes. Phil is correct. :-)

Sometimes I abandon good principles out of simple convenience. But. FullName is not necessary.

More flailing blindly.....

[lonerjohn]

In post #9, are you suggesting a table for each type of contract? Attorney would be a type of contract and wouldn't have an AttorneyID.

If the FY value isn't being stored in fields, how would I differentiate between the value of contract FY1415, for example, being $150,000 and the value of FY1516 being $183,000 and FY1617 being $192,000? The contracts are typically 5 year contracts with varying funds each year.

Several contracts can be assigned to each payee, yes. The contract number would differ.

In virtually 99 out of 100% of the situations, the contracts are NY based. Would a tblStates still be needed?

Sorry about the delay, I didn't get the email indicating any comments had been made to this post.

[lonerjohn]

SFSVendorID, FederalID and CharitiesReg are fields associated with the Payee. They're unique numbers that link with various other state and federal agencies.

[PhilOfWalton]

A general point with databases is that where possible, you select pre-existing data, and use a Combo or List box to select that data: because if, for example, you are trying to do an analysis if something by state, and someone has typed in CA as the state and someone else used California or Calforna as the state, your report will not amalgamate the data. Using a dropdown helps guarantee consistency, because California will be the pre-existing data, and CA and Californa will not be in the database.

As mentioned earlier, it is better to use an autonumber rather than an alpha name as the primary key, so use

TblContractTyes
   ContractTypeID     Autonumber     Primary Key
   ContractType       Text   No Duplicates  
(This is where Attorney would appear

The ContractTypeID is held in the TblContracts (again selected from a Combo or List box).

I am suggesting that you have a table of Financial Years

TblFY
    FYID         AutoNumber      PK
    FY           Text     Unique   (FY1516, FY1617 etc)
 

This allows you to add more years as time progresses.

Then another join table to join the contract to the Financial years

TblJoinContractFY
    ContractID     Long    Joint Primary key
    PYID           Long    Joint Primary key
    Price          Currency
 

Again set up the obvious relationships
Sorry this is getting a bit more complicated than you originally thought, but unless you get the underlying tables and relationships correct, you will struggle. For example in your original post, if you had to add FY2627 you would need to redesign your forms and reports

If you are managing to follow this, could you post the updated relationship page, with all the fields showing

Phil

[lonerjohn]

I'll try and go over all of this information this weekend and see what I can come up with. I really appreciate the assistance from both of you.

[twinnyfo]

Sorry I haven’t been able to chime in much. I have been in the midst of some traveling. However, Phil has you on a great track with his table designs and relationships.

Yes, this seems like a bit of “overkill”, but it is really more a matter of proper design. We have found that many times when folks are new to Access/Databases, that the number one challenge is trying to help people get their minds around the basic principles of DB design. This takes time and patience. But, if you stick with it, you will be able to excel in your overall design.

[lonerjohn]

I'm definitely willing to put in the time. Unfortunately, I had some damage to my house from a recent storm and this weekend I finally was able to get the insurance company in to take a look at it so the repairs could start to be made. It's been a rough month.

I guess the part that I'm trying to understand is the generic setup. For example: I understand tblContracts should be its own table because I want to track all of the information pertinent to the individual contracts, which would be unique values. What I'm not understanding (and perhaps this could help push me in the right direction) is when a field should be the foreign key to another table. When should an autonumber field be used as the primary key to a table instead of ContractNum? (using tblContracts as an example.) Why is it a bad idea to have a table that contains all of the data and then split off the fields into linked tables of pertinent information?

[PhilOfWalton]

Sorry to hear about your damage. The number of storms we are getting certainly is increasing with Global Warming.

So where do we start. The following remarks apply to 99% of databases,
but there are exceptions.

A table should contain information on one subject and that subject should stand by itself. So there should be a table on Contract Type, with ContractTypID as the primary key and ContractName as text. As I remarked earlier, there is a hidden part of the table that contains the index, and a long number takes 32 bits while text takes 8 bits per letter, so unless the Alpha key is 4 letters or less, the long number takes less space and is therefor easier for Access to search. Sot the sort of things we get in ContractType will be "Building Woks", "Ship Building", "Legal work" etc. As you see, the table exists in it's own right and doesn't depend on anything else.
See post 14.

The AutoNumber Key is just a long number that is incremented by 1 every time you add (or start to add) a new entry. It can't be altered, and by definition it is unique, so a record is uniquely defined by an AutoNumber

Now your Contract Table will consist of

TblContracts
    ContractID     AutoNumber     Primary Key
    ContractNum    Either text or number. Must set as indexed, 
                   No Duplicates
    ContractTypeID Long           Foreign Key
    PayeeID        Long           Foreign Key
    Priority       ???
    etc.
 

Now in your original post, you stored the ContractType as text and the same with Payee. We will consider just the Payee.
Suppose you already have a number of Contracts for this Payee..
The problem with storing the Payee Name is that when you add a new Contract and enter the same Payee's Name, you may or may not have the name identical to the previously entered name. "Linden B Johnson" is not the same as "Linden B. Johnson"
So in our Payee Table, we have

PayeeID        1234
PayeeName      Linden B.Johnson
 

In our TblContracts, we store 1234 which points to the correct record in the Payee Table. It is picked from a Combo Box.
This is a Foreign Key which points to a record in another (Foreign) Table

So in brief, you can use ContractNum as the primary key, but it is less efficient than using a AutoNumber. Pretty well all my tables use an AutoNumber as the primary key, because you don't have to check that it has been entered and that it is unique.

Your last question on a table containing all the data means that some bits of data will be duplicated in more than one table. This is incredibly bad design because if a piece of data in one table is altered (added, edited or deleted) and not altered in the other table, the database is likely to get corrupted.
Also look up "Linked Tables". This is where the tables are stored in one database (Back End), and the forms, reports, queries etc are stored in another database (Front End). The tables are linked from the Back End to the Front End so that the forms etc can "see" the tables.

Hope this clarifies things a bit.

Phil

[lonerjohn]

"In our TblContracts, we store 1234 which points to the correct record in the Payee Table. It is picked from a Combo Box.
This is a Foreign Key which points to a record in another (Foreign) Table"

This is referring to the combo box that would show both the AutoNum and the PayeeName so the user picks the right one, but it stores the AutoNum, right?

I do typically split the front end and back end into separate databases. But I've used the wizard so that it does all of it for me automatically.

[lonerjohn]

And, will the foreign key in one table always be the primary key in another table? Cause if that's the case, that makes it easier to comprehend.

[PhilOfWalton]

Yes

When, in the relationship tab, you usually enforce referential integrity. This means the primary key in say PayeeID must exist before it can be added to say PayeeID (Foreign key) in the Contract table.

It also means that if that PayeeID is used in any table, that payee can't be deleted (Unless Cascade delete is set to true which very rarely happens). If you really want to delete a payee, you would need to remove him from your Contract Table first, either by choosing a different Payee or deleting the Contract Record

Phil

[lonerjohn]

I edited my tables. I have a primary key for each table that's an autonumber. The foreign keys is what I can't seem to wrap my head around. I have no relationships setup currently. I've created a Word document to show the current table setup along with a brief explanation as to what it's for. I did use short text as default on all the fields. I can change that. The database won't contain more than 500 records and users will be selecting records from a dropdown menu, minimizing the need for most searches, so I'm not as concerned about speed as I am just getting it to work. Let me know if you need additional information.

[twinnyfo]

The foreign key is the index of the foreign table that is related to the data in the main table. You store the index (primary key) and not the value that you want to display.

For example, if you have a list of employees and want to have a table of appraisals, you would only store the EmployeeID (probably an Autonumber) in your tblAppraisals. This way you are not duplicating your data.

[PhilOfWalton]

We're getting there ... slowly.

So please make the following changes

TblContracts - Change ContractAutoNumber to ContractID (Most programmers use "ID" to indicate it is a Key
Make sure that ContractNum is Indexed (No Duplicates)
ContractType to ContractTypeID Long Number
County to CountyID Long Number
PriorityCategory to PriorityCategoryID Long Number

TblCountyDetails
Change CountyAutoNumber to CountyID Autonumber
Make sure that County is Indexed (No Duplicates)
Remove the field ContractNum

Create a new table

TblJoinContractCounty
    ContractID    Long Number   Joint Key
    CountyID      Joint Key

This allows a contract to be associated with as many counties you like and will also enable to answer the question "Show me all the contracts in Texas"

TblCountyList is not required

TblFiscal
Change FYAutoNumber to YearID Autonumber
Add ContractID Long This points back to the contract
Add FY Long This will be the year
Add Cost Currency
Remove all other fields

TblPayees
Change PayeeAutoNumber to PayeeID Autonumber
Almost certainly the last 3 items should be Long Numbers.

TblPriorityCategories
Change ID to PriorityCategoriesID (then we know what we are talking about
Make sure that PriorityCategories is indexed (No duplicates)

So my suggestion is that you make these changes, then open the relationship page, add all the tables and establish a relationship between those fields that now have the same name. The relationship should be "enforce referential integrity" and cascading updates and deletes should be set to no.

When you have done this, please send an image of your relationship pane with all the fields in all the tables visible, and as little empty white space as possible.

Then we can have another look.

Phil