Advice appreciated

[olorin]

I have a Dbase set up and ready to roll, but I haven't actually created the Dbase yet with Access.
I would very much like your opinion on my table layout and relationships.
You can view the design here
I am hoping to track shipments in and out, plus current stocks of items using Queries and Reports etc.
I have read a lot on Normalisation and realise that there a lot of fields in the "movement" table, although a number of them are "yes/no" fields to help with the queries.
Any advice is most welcome.
and as always, your time is much appreciated.

 

[neileg]

Assuming I understand your intentions.
You could:
- Combine Collection Date and Time into one date/time field
- Same with Delivery date and time

Can you have FullLoad and CoLoad at the same time? If not, then when it is a CoLoad then CoLoadWith will not be a null making FullLoad and CoLoad redundant.
If the Ship Note has been done, you'll have the note number populated making ShipNoteDone redundant.

 

[olorin]

Thanx for your time in replying.
It is possible to have a CoLoad that isn't full and also a full load that isn't a CoLoad. The "CoLoadWith" field I had included, so that I could see which customer ("Organization") the load was sharing with. The Yes/No field for "CoLoad" was so that I could find it easy in a query, and also notify the haulier that there was a double-drop on the trailer. (I email the loads booked to the respective Hauliers). Maybe I am missing something.
I don't follow how I could combine the collection date and time.
Some loads are to be collected on certain dates and times, all of which vary. from early morning to late evening. Also delivery times vary a lot too.

 

[neileg]

Thanx for your time in replying.
It is possible to have a CoLoad that isn't full and also a full load that isn't a CoLoad. The "CoLoadWith" field I had included, so that I could see which customer ("Organization") the load was sharing with. The Yes/No field for "CoLoad" was so that I could find it easy in a query, and also notify the haulier that there was a double-drop on the trailer. (I email the loads booked to the respective Hauliers). Maybe I am missing something.

I understand the Full/CoLoad situation. The point I was making about CoLoadWith is that if this field is always populated when you have a CoLoad that you can tell that you have a CoLoad by testing this field using Is Not Null. So you don't need the yes/no.

I don't follow how I could combine the collection date and time.
Some loads are to be collected on certain dates and times, all of which vary. from early morning to late evening. Also delivery times vary a lot too.

The Access datatype date/time store the date and the time as one value. (Actually it's a decimal number with the integer part being the date and the fractional part giving you the time). So you only need one field to store both the date and the time.

 

[olorin]

Ahhhh.
That explains a lot!
I can now do away with the yes/no fields for coload ans shipnotedone etc.
And with the date/time field, I suppose I can put that field onto a form or report twice, and just format the fields to show date and the other time?

 

[neileg]

And with the date/time field, I suppose I can put that field onto a form or report twice, and just format the fields to show date and the other time?

It's your report, you can do anything you like! But yes, if you want date and time in separate parts of the report you can use formating as you suggest.