What is the correct setup for my tables (1 Viewer)

[scottappleford]

I am using access 97 and looking to start a database for archiving and I require the following fields:

Contract number with specific info e.g dates etc.

Then relate this to boxes 1-10 although not all the boxes will be used, so I do not need to have 10 box fields. In these boxes each item is ordered by item again 1-10 items can be in the boxes.

Appreciate some help

thanks

 

[dynamictiger]

From what you have said you may have 10 boxes, with up to 10 items containing contract details. Does this mean you can have 10 contracts in any one box? What exactly are you trying to do?

 

[scottappleford]

THanks for the reply

One record can contain

1 contract number (primary key) with information e.g. surveyor responsible, date archived etc, so that can be one table.

Then I can have any number of boxes e.g 10 boxes for one contract , 4 for another contract, it depends on the size of the contract.

Then in each box which relates back to a contract number there could be anything from 1-0 items in the box e.g. payment info, contract details. The items in the box will be simple description, so if some says they need payment info for contract 610630, I can locate it quickly.

Or an invoice comes in, I can do a quick count of how many boxes we have archived, that sort of thing.

I hope this helps and I appreciate your help. I am just coming to grips with acces but seem to get this normalisation a bit muddled.

scott

 

[shay]

Sorry to be a bit slow but when you say box do you mean a physical archive box? Didn't completely understand your post.

Would add though that everything to do with the contract should be in the Contract table. You seem to be implying that the contract details should reside with info about the boxes. You may need a separate table to deal with payments if, for example, the number of payments depend on how long the boxes are archived.

If you are creating a db to store info about archive boxes the following design may help:

ContractInfo tbl
ContractNo (PK)
Surveyor
DateArchived
ContractDetails
etc...

Boxes tbl
BoxID (autonumber?)
ContractNo (linked to ContractNo in ContractInfo tbl, one to many)

Payments tbl
ContactNo (linked to ContractNo in ContractInfo tbl, one to many)
PaymentID
PaymentDate
PaymentAmount
etc...

If I've completely misunderstood the problem ignore me!

shay

 

[scottappleford]

thanks for the help, still need assistance

is it wise to have an auto number field as I would like to number the boxes 1 to however many there are for each contract.

Tbl 1
Contract number (pk)
Contract details etc...

Tbl 2
Contract number
Box number (left as text so I can number each one, starting at one for each contract)

Tbl 3 This is where i am stuck, as I need each box to have up to ten items in it.

e.g.
Contract number
Box no
Item number
Description

How do i link these up and will that work. I am not concerned with actual payments that is just one of the many items that will be described in the boxes.

So one record might look like:

Contract no+relating info with 6 boxes box 1-5 having 10 different kinds of information but box 5 having only 7 kinds of info as it is the last box but not filled up.

Another may b:

contract no+relating info with 10 boxes 1-9 having different knind of info but box 10 having been partly filled with 3 kinds of info.

I know that contract no and info can be broken into its own table, but how can I seperate the rest and what joind do I make to make the db normalised.

Hope that explains it better and once again I appreciate your help.

Thanks

scott

 

[shay]

Why not ...

Tbl 1
Contract number (pk)
Contract details etc...

Tbl 2
Contract number
Box number (Why not use numeric - you can still start from 1 for each contract)
Item number (Runs from 1 up to 10 for each box within a contract)
Description

The two tables would then be linked on contract number.

shay

 

[scottappleford]

hi again

That sort of works, but when I enter data I am entering box 1 ten times as there 10 items to a box, do I need a third table?

If so how do I make sure it links back to contract number?

tbl 1
contract number (pk)
contract details....

tbl2
contract number
box number (pk)

tbl 3
box number
item number
description

 

[shay]

Hi

Not been in the office since Wednesday lunchtime so couldn't reply earlier.

That sort of works, but when I enter data I am entering box 1 ten times as there 10 items to a box, do I need a third table?

There is no problem with entering the box number 10 times as the number is just an identifier. If you were entering textual information for each box instead, then I would think a third table is needed.

As you've described your problem, I think having 3 tables is an unnecessary complication.

shay

 

[scottappleford]

hi again

Ok, that works, thanks, wait for it haha!

However, forgive my lack of knowledge but is the idea of a db to reduce data entry? If I had 3 tbls then i would onlyh need to enter the box number once. I have come up with the following what do you think as I still have one problem.

tbl 1 tbl2 tbl3

contract no (pk)1 many Contract no (pk)1 many Contract no(pk)
conract details.... Box no (pk)1 many box no(pk)
item no (pk)
contents

The only problem is when I enter contract no in tbl 1 it updates tbl 2 and 3, however when I enter box number in tbl 2 it does not update tbl 3, why is this, because if it updated the db would work fine, as it will prevent duplicates and data entry, ie. what my understanding of a normalized db is.

Once again I really appreciate your help.

thanks

scott