If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
What tables forthis database
I have a excel spreadsheet at work for shipments comming in to the
country for us, and then delivered by a transport company to a said power station. Among some of the 17 column headers, there a Customer Project Ship from Ship to Ship date Customs in date Customs clearance date Delivery date Commercial Value The other column headers are dates of receipt of POD, who signed etc, and hyperlink cells to documents for the said shipment. I want to put all thisinformation into a Access 2007 database and have had sometought on how to do it, with separtate tables for CustomerID, ProjectID, ShipFrom, ShipTo. Are, or do I need any more separate tables for this database? Thanks |
#2
|
|||
|
|||
What tables forthis database
To decide on the best structure, you'll need to identify where the
one-to-many relationships exist within your workflow, e.g.: Could one shipment contain many items that you need to split up and track separately? At the most basic level, I imagine you would want tables like this: Client table: one record for each company you deal with (consignors, consignees, shippers, ...), with a ClientID primary key. ItemType table: one record for each type of item you handle (e.g. turbine, ....), with ItemTypeID primary key. Shipment table: one record for each incoming or outgoing shipment. Fields: - ShipmentID AutoNubmer primary key - ShipDirection Number 1 for inward; -1 for outward - ClientID Number who from (inward) or to (outward) - ShipmentDate Date/time date arrived (inward) or sent (outward) - CarrierID Number who delivered this shipment. ShipmentDetail table: one record for each item in the shipment. - ShipmentDetailID AutoNumber primary key - ShipmentID Number which Shipment record this line applies to - Quantity Number how many of this itme - ItemTypeID relates to ItemType.ItemTypeID - ValueEach Currency how much each item is worth If that's completely foreign, there's a Northwind sample database for Access. Open it, and see how the Orders and OrderDetails tables work, and hopefully the above will make sense. In tracking customs etc, you will need to consider whether there could be further one-to-many relationships here, e.g.: a) Could some items in a shipment be cleared before others (e.g. where some item-types are sensitive while others are not)? If so, the clearances dates go in the Shipment Detail table, because clearance could apply to different items separately. b) Could there be several steps in a clearance journey? For example, to export a sensitive electronic part, might it need to clear US customs before it's allowed out, and then to also clear customs in the target country before it's allowed in? Could there also be other clearances along the way? If yes, you'll need another table for the steps of the journey. Hope that helps you think in terms of the one-to-many relations that will lead you to a good data schema. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Bob H" wrote in message ... I have a excel spreadsheet at work for shipments comming in to the country for us, and then delivered by a transport company to a said power station. Among some of the 17 column headers, there a Customer Project Ship from Ship to Ship date Customs in date Customs clearance date Delivery date Commercial Value The other column headers are dates of receipt of POD, who signed etc, and hyperlink cells to documents for the said shipment. I want to put all thisinformation into a Access 2007 database and have had sometought on how to do it, with separtate tables for CustomerID, ProjectID, ShipFrom, ShipTo. Are, or do I need any more separate tables for this database? Thanks |
Thread Tools | |
Display Modes | |
|
|