Contract number generation

[James]

OK, I am in the process of building a database to take the place of an Excel
spreadsheet that just is not adequate anymore. I am storing information
about multiple contracts for compliance with certain accounting rules. I
have done some database design, but it was mostly in school so the answers
were readily available. I have a few questions:

1. What is the best way to begin? I have the spreadsheet and I have
imported it. Currently I have a table that resembles the spreadsheet.
2. I have a structure for the Contract ID that I would like to automate. I
want a regionID – DivisionID – Project – Contract. It would looks something
like:

CA-250-0001-001

The reason for the project-division is that you can have one project with
multiple contracts. I don’t need to track any info on the project, just the
contract. I may have one contract set up this week and then have others
added on later. The way it worked in Excel was to add a line just underneath
the last one with a similar name. Is this structure possible?

I am sure I’ll have additional questions as I go along. Thanks for the help.

 

[BruceM]

If one project can have multiple contracts, there is what is called a
one-to-many relationship between projects and contracts. In Access you would
handle that with a Projects table and a Contracts table. A good general rule
for a table is that you should be able to describe its purpose in a single
sentence without using the word "and" (unless its something like name and
address, which are both part of personal information).
When you view an Access table the columns are fields, and the rows are
records. The records are not in order in a table. You can sort them for
convenience if you like, but they are not analogous to rows in a spreadsheet.
A table is just a bucket of information. Its order is irrelevant. Also,
unlike a spreadsheet, the fields for a particular record stay together, no
matter how you sort or arrange the records.
Considering just two tables, here is a suggested basic structure:

tblProject
ProjectID (autonumber) [this is your primary key field, or PK]
Other information specific to the Project, using as many fields as necessary

tblContract
ContractID (autonumber) [PK field]
ProjectID (number)
Date (Date/Time)
Other fields specific to the contract

When designing a table you need to specify the data type in table design
view. The word in parentheses is the data type for that field. In table
design view, right click next to the PK fields and specify Primary Key. If
you try to close the table without specifying a PK you will be prompted.

Go to Tools > Relationships and add the two tables. Drag ProjectID from one
table onto ProjectID in the other table. In the dialog that pops up, click
Enforce Referential Integrity. You have established a one-to-many
relationship, which is at the heart of relational database structure. Close
the realtionship window, saving when prompted.

In the database window (the thing that shows up when you open Access) click
tblProject. Find and click the autoform button on the toolbar. This will
build a quick form that should include both related tables. Add a sample
Project, then a few sample Contracts. Click the small arrow at the bottom of
the window to make a new Project, and again add some sample contracts. Now
open tblProjects. Note the ProjectID numbers (it doesn't matter what they
are). Click the + sign, and you will see related records from tblContracts.
You can also view these records by opening tblContracts directly. Note that
ProjectID is the same in the related records from tblContracts as in the main
record from tblProjects. Those related records in tblContracts are
associated with one and only one record from tblProjects.

This will get you started. Forget about adding actual data for now, just
get used to how a relational database works. Importing data and setting up
the custom ID number can come a little later.