Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Posts
    12

    Unanswered: Rental Database Table Setup

    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:

    tblItems
    ItemCode(PK)
    Category
    Description
    RentalRate
    UnitPrice(not too important)

    tblOrders
    TicketID(AutoNumber, PK)
    ShipFromJobsiteID(FK)
    ShipToJobsiteID(FK)
    Date
    TicketType
    Notes

    tblOrderDetails
    TicketID(FK)
    Category
    Description
    Quantity

    tblShipFromJobsite
    JobsiteID(PK)
    JobsiteName
    Contact
    BillingShippingAddress
    City
    StateorProvince
    ZIPCode
    PhoneNumber

    tblShipToJobsite
    JobsiteID(PK)
    JobsiteName
    Contact
    BillingShippingAddress
    City
    StateorProvince
    ZIPCode
    PhoneNumber

  2. #2
    Join Date
    Mar 2006
    Posts
    73
    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.

  3. #3
    Join Date
    Jun 2006
    Posts
    12
    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).

  4. #4
    Join Date
    Jun 2006
    Posts
    13
    You could create a Jobsite table:

    tblJobsite
    JobsiteID(PK)
    JobsiteName
    Contact
    BillingShippingAddress
    City
    StateorProvince
    ZIPCode
    PhoneNumber

    Then your tblShipToJobsite would have:
    ShipToID(PK)
    JobsiteID(relationship to tblJobsite)

    and tblShipFromJobsite would have:
    ShipFromID(PK)
    JobsiteID(relationship to tblJobsite)

    This will prevent duplicate data (e.g., if you ship to/from the same job sites routinely, you won't be entering their address and other info over and over)

    Also to Smythe's comment, if ItemCode isn't important, why do you have it? Where in your Orders and ShipTo tables are you recording what the actual item is that is being ordered/you are shipping?
    thx,
    mlt

  5. #5
    Join Date
    Jun 2006
    Posts
    12
    tuozzo,

    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.

  6. #6
    Join Date
    Jun 2006
    Posts
    12
    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.

  7. #7
    Join Date
    Jun 2006
    Posts
    13
    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!
    thx,
    mlt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •