Beginner needs help on table structure

[superfly5203]

I made a database and I guess I was getting a head of myself and when I started to design a query, some flaws became apparent. I'll give some background onto what my database does. My problem sounds a lot like a poster from yesterday who wanted to know why having one huge table is bad. Right now all my data is in one table that is about 75 fields long.

My database tracks contract changes, a change is when some additional work is added to a contract. All changes have a specific "change number", which my PK. There is some basic information that all changes have such as a title or description of the change, what contract the change will affect, what office requested the change, when was the change approved, and the names of the people who process the change. All of those are combo boxes, except the title field.

After this information is entered, i have 5 checkboxes which will determine the "type" of change: overlimit (over a dollar threshold), underlimit (under a threshold), UCA (high priority change), PIO (buying certain type of equipment). I also have another checkbox for if there was additional information submitted after a proposal was received.

After the appropriate check boxes are checked, my form will make the corresponding fields visible for data to be entered. Depending on the type of change selected, there will be fields for a cost of the change, the profit on the change and those two added together make up the price. There are four categories for those dollars, the amount which was proposed (what the contractor wanted for their change), the objective (what we wanted to pay), a max (the most we would pay), and negotiated (what we are going to pay). There are also dates for the seven steps each change goes through on its way to being completed. I have baseline date fields (if the change moved at a predetermined schedule) and actual dates (the dates that really happened).

So all of this data goes into one field right now. I see there are a few places that could be other tables, like the contracts, or the people who worked on the change, or the office who approved it. My question is, how can I set that up with multiple tables? For example, now my first few fields look like this: change number, change title, contract, date change approved, approving office, procurement person, technical person, finance person, then my checkboxes, then my dollars, then my dates.

If I had two tables, I don't know how I could tie these different tables together. I don't see how my database relates to many of the examples, the customer and orders, on this site.

Thanks for any help.

 

[Rabbie]

In general Access tables should be long and thin not short and wide.

I would advise you to google database normalization and then check your design. In my experience if you have a properly normalised DB then it is much easier to write meaningful queries and to add extra functions later.

Access has been designed to implement relational databases and that implies the use of many tables linked together.

 

[JHB]

...
After this information is entered, i have 5 checkboxes which will determine the "type" of change: overlimit (over a dollar threshold), underlimit (under a threshold), UCA (high priority change), PIO (buying certain type of equipment).

Do you've 5 fields in you table, (one for each check box) or only one field?
If 5 fields, then you should create a "sub" table.
A good identifier for when a sub-table is necessary, is when you have empty fields in your table. Of course, there are exceptions!

...
If I had two tables, I don't know how I could tie these different tables together.

You tie them together using your "change number".

 

[jdraw]

Related post for info
http://www.access-programmers.co.uk/forums/showthread.php?t=286050

 

[superfly5203]

Do you've 5 fields in you table, (one for each check box) or only one field?
If 5 fields, then you should create a "sub" table.
A good identifier for when a sub-table is necessary, is when you have empty fields in your table. Of course, there are exceptions!

You tie them together using your "change number".

I do have 5 fields, one for each check box, i'll do some research into a sub table.

Thanks for your help, and I'll let you know if this helps, which i'm sure it will!

 

[Minty]

Another really good way to "spot" if you should have another table is if the natural field title is highly specific to a task, when other similar tasks might appear.

As already suggested - In your example your change types are a classic case. The fact you referred to them as a type would tend to indicate you should have them in as sub table. Change type 1, 2 3 4 etc. When you suddenly need another one you would have to add a field and redesign your forms and reports. if you stored the change types separately in a table you would simply add another record to your change type table.

 

[superfly5203]

I set up some more tables, one for each change type, and all the possible combinations of the changes. So now, I assume that I can make one "change entry form" or something that based on the check boxes it will store the record in the correct table? So i'll still have one table that has all of the changes in it, but only the basic info (change number, contract, title, office, and people who worked the change), or should i lose that table completely and only use tables for the specific change type?

 

[Minty]

No I think you might be heading off in slightly the wrong direction. This is only a suggestion - but you maybe see how this "vertical" approach is more flexible?

TblChangeHeader

ChangeID - Autonumber - Primary Key (PK)
ContractNo - possibly another ID from a contract table?
DateEntered
Etc - Things that don't alter and only occur once per change request.

tblChangeProgress
StatusID - Autonumber - PK
ChangeID - Foreign Key (FK) from tblChangeHeader
StatusDate - DateTime
ChangedBy - FK - EmployeeID from your employees table
ChangeType - FK From your new changetype table
StatusNotes - Free text for anything else you need

tblChangeType
ChangeTypeID (PK)
ChangeDesc - Text description
ChangeGroup - maybe to help group reports together or restrict certain changes to certain employees .

You can then have as many changes/progress record per change header as you need.

 

[superfly5203]

I set up two tables, one that has all my change info, and another like you suggested as the change type. I set it up just like you suggested and this has made a world of difference. This also solved my problem I had in the thread that jdraw linked, as now I can point to a certain "typeID" and it pulls what I needed.

I do have another question related to calculated table fields. I know those seem to be frowned upon, but they seem to be working.