Normalization/table set up

[kstarnes]

I'm not a programmer and know very little about setting up access databases other than the very basics...however...due to my politics background, I'm setting them up for the local government....just my little disclaimer about why I'm asking stupid questions.

I am creating a database for multiple users who are of two sorts. The users deal with either the setting up of a contract, or with the payment of a contract. Both of these groups need to be able to access different information about the same contracts. What seems most logical to me is to create two tables in the same database, one for the contract, and one for the payment information and have both update when one or the other is worked on.

My basic set up for now is this:
Table One: contract information
Table Two: Payment information
Link Key: Serial Number from contracts
These are my questions:

1) is there a way to set this up so that the only way someone from the payments group can create a linked record is if a record with the same serial number already exists in the contracts table?
2)Is there a way I can set it up so that when someone in the payments group starts to input their information, they can find the serial number they want, and have certain fields show up? For example: I want them to be able to search for a serial number and have the contract name show up with all of the previously entered payment information, then have a new field each time they go into it for their next payment (if that makes sense).
3) When linking, hopefully in such a way as to do the above, do I need to have the same field name in both tables, or...? I'm a bit confused as to how this works as initially i created a field in both tables that would have the same serial number and tried to link those..and it didn't work.

Sorry if this is so confused...I'm sort of learning as I go along.

 

[David Eagar]

Can one contract have many payments?

 

[Simon_MT]

I would create different Forms for different Functions simply department has different requirements. To start with I would not worry too much about building Chinese Walls just get the Functionality right. Simple Design without all the detail

Table Client
Client PK

Table Contract
Contract PK
Client

Table Payments Header
Batch No
Date

Table Payments Details
Client
Contract

Although putting the Client on the Payments is not normalised but you need to qualify the Contract Combi-box so I left it there!

Hanldling of information regarding the Contract, you can either (using Tabs) show Contract Details on one Tab and Payments on another or you can go in via the Client and see Details on one Tab - Contracts on another and Payments on a further Tab.

The linking you refer to is part of being a Relational Database. The Contract records stays intact the other Tables get populated with the Contract referentially.

Simon

 

[kstarnes]

Thank you both!

David-yes...and I will need to be able to see the history of all of the payments.

Simon,
If I'm understanding your suggestion correctly, it sounds as if you are saying I should set up lots of tables with only one or two pieces of information and then have the forms set up for each group...which sounds great, but I have a few questions about how to make that work.

1) How should I link all of the tables? If either of the groups has information to update how can they find the correct record to update without flipping through all of them? If they type in the correct serial number (which I think I need to have be the link key) for an existing record in the form will it automatically update the correct record if I have it set up not to accept duplicates of that number? If that is how all of the tables are going to talk to each other, do i need to have this piece of information in each table? If not, how does the database know which records connect to each other?

2) I'm a bit curious as to how this would work-if I have two seperate forms with multiple tables, is there a way to not allow a user to input information into the payments form unless certain fields in the contract form are already filled in? I know that I can require information in the table when I set it up, but if the form doesn't have that line in it, how will that affect the input?

3) In the end, I need all of the tables to be linked in such a way that I can create reports showing all of the information about the contract and payments on the same page, how do I need to connect the tables to do this as well? I think tabs would be quite useful- could I set up a form that would have each of the other forms on tabs so that managers etc. could see what was being input from both places at once, or would it simply be easier to have them create a report?

4) What exactly do you mean by this? 'Although putting the Client on the Payments is not normalised but you need to qualify the Contract Combi-box so I left it there!'

5) My boss has specified certain fields that she wants and changes her mind somewhat frequently so I'd like to make sure I understand how this works in case I get something working and she wants me to re-do it. Do you mean that if I duplicate information that is already in a table its not normalize, but that I have to do it in this case to keep the records in the Contract table lined up with the records in the payment details table?

Thanks to anyone willing to help.

 

[David Eagar]

Search for 1 to many relationships and have a good, hard think about what is your primary table

In my first serious Access db, it came quite a shock to me that our company, that was spending the time & money to build the database for our benefit, came about 4th or 5th in the table structure hierachy

What is MOST important to you to get YOUR valuable information appears to me to be the company that has the initial contract (they could have many)

I think they are the ONE, figure out the links from there how that 1 filters through the MANY to get what you are after

Primary Key (PK) and Foreign Key (FK) is your answer

Don't panic, it will only do your brain in for the next 3-4 YEARS

 

[Simon_MT]

You have tools are your deposal. One are Queries that allow "views" to be created of multiple tables and you link the tables together with common data like the contract. if Would look something like:

SELECT ... 
FROM Contracts INNER JOIN Clients ON Contracts.[ClientID] = Clients.[ClientID]

Payments Entry you enter the Cliernt via a combi-box and qualify the Contract again via a combibox and select only those Contracts relating to that Client.

Your Contracts should have a Status that allows you to manage the Contract.

"P" Provisional
"A" Active
"H" Inactive

You can process payments only against an Active Contract, that is a little too rigid but it illustrates the using Statuses.

I would suggest that you first get to know Queries, yes you Duplicate information but this is the essence a relational database. Screens are equally as important as Reports, neither are restricted to a single table, I would be tempted to run all screens and reports using Queries.

So far as change are concerned you can't change Tables with Active Users using your data tables. Providing you don't delete fields or change a fields name, once you have change the structure you can bring the rest of your application into line. This is assuming a Front End / Back End deployment.

I happy to be more expansive, it you need help.

Simon

 

[kstarnes]

Thank you, to you both. Fortunately its a temporary job.
I have tried it both with three tables and a series of little tables (that got out of hand fast with trying to define the relationships). I think part of my problem was that I was expecting the relationship to serve as a validation, so figuring out some validation codes should help with that (hopefully).

There are only two pieces of information on this table that will be unique for each record, the serial number and the name of the contract scheme, so as far as my thinking goes those are the only things that work as a primary key/primary table. After that, all of the information more or less realates to that table in the same way, so I have only broken it down into two other tables, for the time being. Each of those tables now relates to my primary table based on the serial number as the primary key; I will use validation codes to ensure that schemes are not duplicated under multiple serial numbers. The rest of the information is fairly fluid anyway-its just a matter of making sure it stays tied to the correct serial number/scheme name.

Does this make sense? I'm not sure I quite understand validation codes correctly- can I have a field validate based on if the value is found in my primary table? Unfortunately writing them is beyond me so I will have to refer that to someone else and can't test it.