Couple of questions that I've asked on other similar sites, but want other opinions.
My employer does concrete formwork, meaning we form the concrete for high rise building (hospitals, apartments, parking decks, office buildings, etc.). We are trying to update our method of keeping track of what items we have on our storage yard (what is shipped and returned to and from our several Jobsites). I'm trying to find the best way to setup an Access database to do this. We want to calculate monthly rental prices for certain items that we deem as rental. We want to be able to print out shipping tickets. We want to print reports that show what's on a jobsite and how many. We want to print out a report that shows every item that has been shipped out of the yard minus what's been returned to equal what's out on rent to give us an estimate on the total amount we have of our items (and maybe even to add what we have on the yard to that amount).
So this is a simple, not too complicated, Rental/inventory database. I've got a somewhat finished product. However, I need to know if my tables are cool. I have:
UnitPrice(not too important)
from what I can see you're gonna have problems with relationships or atleast thats what it seems to me..as there's really nothing I can see that can be a relationship to ItemCode..unless I'm just not seeing it..
Just double check the relationships between the tables and make sure that they all match up. Otherwise looks like you've got all the data ya need to have.
ItemCode isn't terribly important. I guess to solve a potential problem (which I haven't had one yet) is to have ItemCode in the OrderDetails table. Then in my form that I use to enter shipping orders, I can just hide the ItemCode. I use cascading combo boxes with the category and description. I select the category and the description filters for those items in that category. Currently I have the ItemCode and Category in the tblItems linked to the Category in the tblOrderDetails (both relationships are "Indeterminate").
My biggest worry is that I sort of have the same info in the tblShipToJobsite and tblShipFromJobsite. I did this in order to have a place in my Order Form to distinguish what location items were shipped shipped to and from. Sometimes Jobsites will ship some items to another jobsite, and it's a transfer. I failed to mention another table which just has the TicketType info (TicketTypeID, and TicketType).
To answer your last question: the main relationship between the tblOrderDetails and tblItems was with the Category. In the Orders table, the subdatasheet is the OrderDetails table. Then in the ShipToJobsites table the subdatasheet is the Orders table, then sub under that is the OrderDetails. The ItemCode I kept in because I think the Accounting Manager needs to know the ItemCodes. Does that answer your question? Everything has been working properly with no upsets or hiccups.
Also, regarding your table setup suggestion, I don't have to enter addresses over and over. My order form just focuses on the JobsiteID and JobsiteName. Basically, I store the addresses for knowledge purposes and for another part of the database which does invoices to those jobsites when they purchase power tools. I sort of have two databases in one because they are pretty much related. We keep track of the rental equipment to and from the jobsites and bill them for tools they purchase. The tools table is rather small and everything is working together. The invoice side of the database is connect to the rental by way of the ShipToJobsites.
Sounds like you have what you need, then. I made the suggestion in response to your concern about having the same info in the two tables. That shouldn't be a problem as long as you're not storing duplicate data unnecessarily. Good luck with your project!