Table Layout

[aas4mis]

I'm having a hard time getting started and am open to suggestions for the layout of my table. I have an excell spreadsheet with the fields "container[text], dock#[number], vendor name[text], team members[text, may be multiple members], date in[date container arrived], and shift worked[text, 1st, 2nd, 3rd, weekend]". I can get a generic table laid out but I want to be able to search by container, vendor, and/or user for a specific date, for this I am at a loss. Primarily when it comes to splitting the table and assigning relationships to match my search criteria. I've been working on this for a while now and keep reaching a dead end. Any good starting points would be appreciated.
Thanks.

[jeffstl]

I'm having a hard time getting started and am open to suggestions for the layout of my table. I have an excell spreadsheet with the fields "container[text], dock#[number], vendor name[text], team members[text, may be multiple members], date in[date container arrived], and shift worked[text, 1st, 2nd, 3rd, weekend]". I can get a generic table laid out but I want to be able to search by container, vendor, and/or user for a specific date, for this I am at a loss. Primarily when it comes to splitting the table and assigning relationships to match my search criteria. I've been working on this for a while now and keep reaching a dead end. Any good starting points would be appreciated.
Thanks.

Well if you want to do your tables right it sounds like you will need to split out team members for certain, and possbly vendor depending on how often a vendor name may change in the future.

I would set up a main table:

MainID (autonumber)
Container
Dock
VendorCode
Date
Shift

A second table that holds the team members will use the MainID to link it to that record. This allows you to have as many team member associated with that main record as you want.

TeamMemberID (autonumber)
MainID
TeamMemberName

Then optional, a 3rd table to store vendors and look them up (in case thier name changes in the future this will maintain historical data) This may also apply to your Dock field too. (something similiar)

VendorID (autonumber)
VendorCode
VendorName


Lastly, as far as searching, not sure where you are at with SQL but thats what you will need to pull data from these tables. You can build auto-querys with access though and save them, then refer to them in code pretty easily.

Just an example of getting say all team members that worked on a specific date and a specific container with the proper vendor also looked up. Keep in mind this is just an example and may not be what your looking for but something to start with. Try some of this out and post the specific problems you have implementing it and we can help more.

 
Select * from MainTable, TeamMemberTable,VendorTable where MainTable.Container = '" & MyVariable & "' and MainTable.Date = #" & MyDate & "# and MainTable.VendorCode = VendorTable.VendorCode and MainTable.MainID = TeamMemberTable.MainID
 
 

[Stewart Ross]

...I have an excell spreadsheet with the fields "container[text], dock#[number], vendor name[text], team members[text, may be multiple members], date in[date container arrived], and shift worked[text, 1st, 2nd, 3rd, weekend]". I can get a generic table laid out but I want to be able to search by container, vendor, and/or user for a specific date, for this I am at a loss. ...

Excel is a great spreadsheet but a very poor database. Its freeform nature encourages the storing of redundant data that has had no thought put into it at all.

There are at least five main entities that should all be separated out in that Excel sheet at present: Vendor, Container, Team Member, Dock and Shift. Look for the main real-world objects which are of interest (and indeed you listed all of the ones I mentioned yourself!). There are relationships between those entities. I'm guessing at these here, and you may know better: a vendor can supply many containers, but for each container there is only one vendor, so there is a 1-Many relationship there. A container can be parked in one of many docks - and each dock can have many containers. As this is a Many-Many relationship it needs to be broken down into two 1-Many relationships via the use of a sixth entity - a Dock Container List link table in this case. Similarly for Team Member and Shift, which is also Many-Many. And these are just the obvious ones...

There is a good article on database table design and normalisation in the HowTo section of this forum, at http://www.thescripts.com/forum/thread585228.html.

Good luck with the project.

-Stewart

[aas4mis]

My apologies for the late reply, work has picked up and I'm only pushing access db's on my free time. Thanks for all the great info, sure to be a good starting point for this db. I've done a few db's with relationships but for some reason this one has had me chasing my tail. I really don't think the logic is my problem, I'm just not sure where I want this db to go. I usually start with a pencil and paper drawing out my form and go from there, I'm just having a hard time visualizing this one. Anyways, thanks again for taking the time to point me in the right direction.