Add fields or records to table? 1

[BrianLe]

I am working on designing a database for tracking inventory. When an item is ordered, there will be an "anticipated delivery date", as well as other information to be stored later on such as received date, removed date, etc. The item and it's "anticipated delivery date" will be added to an Access table via a form. Late on, the anticipated delivery date may change an unknown number of times until the item is finally received. I would like to know the best way to store in the table the new anticipated delivery date(s) and retain the original and all the subsequent anticipated delivery dates. Should I add fields such as "Anticipated Delivery Date (1)","Anticipated Delivery Date (2) , etc. to the table so that all the information for a particular item is in one row, or should I add a new record for the item and put the subsequent anticipated delivery dates in separate rows in a single field "Anticipated Delivery Date"? Thanks

 

[dhookom]

IMHO, adding multiple fields would be a huge mistake. You are better creating a table of Transaction Dates. Each date for each item would create a record in this table. There would be a date type field to store codes for Anticipated Delivery, Received Date, ...

Build normalized applications for greater flexibility in the future.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BrianLe]

I read up on normalized tables and I see the light. Thanks. I'm now working on tables like the following:
tblDatecode
DateID Date# Date Desc
1 1 Antic Load Date
2 2 Antic Load Time

tblTrain
TrainID Train# SourceID Source Name
121 PAT91(1) 2 Alpha
122 PAT92(1) 1 Baker

tblShedule
TrainID Train# DateID Date# Date Desc DateTime
121 PAT91(1) 1 1 Antic Load Date 7/7/07
121 PAT91(1) 2 2 Antic Load Time 12:45
121 PAT91(1) 1 1 Antic Load Date 7/8/07
121 PAT91(1) 2 2 Antic Load Time 16:00
Am I on the right track?

 

[dhookom]

It looks like you have un-necessary fiedls in tblSchedule. If you have the TrainID, you don't need the Train#. The same is true for DateID and Date# fields in tblSchedule.

I would also question the fields SourceID and SourceName in tblTrain.

You should also find and use a naming convention that doesn't allow symbols like "#" or spaces.

You can store Date and Time values in the same field. I would have an Antic Date Time value
[tt]
tblDatecode
DateID DateTitle
1 Load
2 Delivery
3 Received
[/tt]
I would also consider pulling the a code for Actual or Anticipated in tblSchedule which would "modify" the DateID.




Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BrianLe]

I think I've done as suggested. Now have tables:

tblTrain
TrainId
TrainNumber (typical name used)
SourceID
NumberOfCars

tblSource
SourceID
SourceName (Mine name)

tblDatecode
DateID
DateTitle (Load,Arrival,Empty,Removal)

tblDatetype
DatetypeID
Datetype (Anticipated, Actual)

tblSchedule
TrainID
DatetypeID
DateID
DateTime

How's that? Thanks

 

[dhookom]

That looks much better from my point of view.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]