Results 1 to 6 of 6
  1. #1
    wakerider017 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Posts
    3

    Question Assets Database Design Help!!

    I've recently inherited an excel spreadsheet with multiple tabs keeping track of multiple different models of phones / tablets / wifi cards issued to different users in different offices across the country. In a seperate spreadsheet we are keeping track of how many car/wall chargers we are sending out. And in a third spreadsheet we are recording when devices are returned to us.
    There is a high turn over rate in our industry so I will constantly be swapping out devices. I need a better way to keep track of the Assets we are sending out and the assets being returned to me. I'm a little rusty with Access as I haven't used it in a few years, but I'm willing to brush up on it a little bit to creat a more refined solution.
    Obviously I will need a table for:

    Employee Info:

    -ID*
    -FirstName
    -LastName
    -Email
    -OfficeLocation
    -Notes

    I will also need a way to keep track of the actual assets. Should the car and wall chargers have there own table? What would be the best way to keep inventory in order to record returned devices and keep track of items that have been shipped out?

    Device Table:
    -ID*
    -Category (Phone,Tablet,WiFiCard,WallCharger,CarCharger)
    -IMEI
    -SIM (This can change)
    -Make (LG,Samsung,Apple)
    -Model (Galaxy,Spectrum,iPhone)
    -PhoneNum (This can change)
    -Carrier (Verizon, AT&T, T-Mobile)
    -Price (Cost of deivce if lost or broken)
    -Condition (New,Good,Fair,Poor)
    -AquiredDate
    -LineStatus (Active, Suspended)
    -Office Location
    -Notes

    Order Table:
    -ID*
    -DateShipped
    -TrackingNum
    -FirstName
    -LastName
    -OfficeLocation
    -Category (Phone,Tablet,WiFiCard,WallCharger,CarCharger)
    -IMEI
    -SIM (This can change)
    -Make (LG,Samsung,Apple)
    -Model (Galaxy,Spectrum,iPhone)
    -PhoneNum (This can change)
    -Carrier (Verizon, AT&T, T-Mobile)
    -LineStatus (Active, Suspended)
    -Notes



    I'm not sure if I am on track or way off track.
    What do you guys think?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    It appears that you will need some Lookup Tables to facilitate consistency with Category; Make; Model; Carrier...
    If Offices are in different physical Locations, you'll need a Location table.

    Here's a link that will help you with table design, Normalization and creating relationships.
    http://www.rogersaccesslibrary.com/T...lationship.zip
    Go through it to understand what he's doing, then try it with you own data. I recommend you write down the business facts (similar to his starting paragraph) and work through the tutorial.
    There are some free data models at http://www.databaseanswers.org/data_...all_models.htm
    that may help.
    Good luck.

    And remember Excel and Access are very different. Your biggest challenge may be to forget enough spreadsheet in order to understand relational database.

  3. #3
    wakerider017 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Posts
    3
    Thanks!

    That was a big help with getting me started! Here is what I have so far:

    Click image for larger version. 

Name:	relation.png 
Views:	20 
Size:	56.2 KB 
ID:	12569


    How does everything look? All of the relations were formed when I used the lookup wizard... Is that okay?

    Also I created an 'OrderItem' and 'Order' table. The Order table is supposed to contain 1 to many OrderItem records. (Multiple items in an order). Does it look correct?



    ----------------------------------------



    I'm not sure how all this should look on a form for easy data entry....


    Basically I want to be able to edit:

    EmployeeT: FirstName, LastName, Email, OfficeName, Notes
    StatusT: TrackingNum
    OrderT: Satus, OrderDate, Notes
    OrderItemT: OrderQuantity, Notes
    DeviceT: Category, IMEI, SIM, Make, Model, PhoneNum, Carrier, AquiredDate, Condition, LineActive, Price, Notes

    The part I am most confused about is that some order will have multiple items in them and I'm not sure how to accomplish doing this on a form..



    Hopefully I am on the right track and please do not hesitate to criticize my work. Thanks!!!

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Some issues. When you say Lookup Wizard are you referring to a Lookup field in a table design? Or something else such as building a separate table to be used to look up other attributes? Please describe this in more detail.

    Take a look at this IT Assets model http://www.databaseanswers.org/data_...sets/index.htm
    and notice how the AssetID and EmployeeID are used in table EmployeeAssets to relate back to the Asset table and the Employee table. The value of the Primary key in each of those tables is placed in the EmployeeAssets table as a Foreign key. This identifies the relationship between the tables. Always use the keys to establish the relation eg use EmployeeID, not EmployeeName.

    Your relationships ("lines") between tables should indicate some cardinality. That's an indication of how many A's are/maybe related to B's. eg One Order may contain 1 or Many OrderItems.

    Here are some videos that should help put more of this into context.
    These are quite good for learning by Watching/Listening rather than reading.

    http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

    http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

    http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

    http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

    http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

    http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

    http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

    http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming/cardinality

    Complete set of tutorials on Acc2010.
    https://www.youtube.com/playlist?lis...FoilxbUY0yUqZP

    Good luck.

  5. #5
    wakerider017 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Posts
    3
    What I did is create separate tables such as OfficeT to store all the pertinent info.

    After that was complete, I created the table EmployeeT. I created a field named OfficeName in the EmployeeT table. I then changed the data type (Where you can select text,memo,number,etc.) to Lookup Wizard. From the Lookup Wizard I added both the OfficeID and OfficeName from the OfficeT table. I clicked to hide the PK and clicked finish. Now in the EmployeeT table datasheet view I am able to select from a dropdown list of office locations.

    Pretty much all my relations have been setup using this method. Is this incorrect?

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    My suggestion is to remove all Lookups from the table. This "feature" introduced by M$oft has not been a hit with developers.
    Here's why http://access.mvps.org/access/lookupfields.htm
    Instead, the traditional approach is to build another table. Using your EmployeeT and OfficeT as example, you build these in design view as separate tables. EmployeeT has EmployeeID as PK, OfficeT has OfficeID as PK - and usually these will be autonumbers (unique identifiers for records in these tables). You add a field to EmployeeT called - OfficeId data type Long Integer. This field will serve as the Foreign key in the relationship between EmployeeT and OfficeT.

    Use this sort of set up for all of your lookup tables.
    Your naming convention looks good -- no embedded spaces in field/table names.

    Once you get your tables designed, I'd recommend you establish referential integrity by explicitly defining relationships. This will allow Assess/JET/ACE to prevent orphans.

    Seems you may have been watching some Richard Rost (599CD) videos with your T suffix on tables. Richard does good stuff.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-28-2013, 04:42 PM
  2. Access database design to check assets in and out
    By ginachicclett in forum Database Design
    Replies: 2
    Last Post: 08-19-2012, 05:45 PM
  3. Queries for determining unused amounts of assets/cash
    By Long Tom Coffin in forum Queries
    Replies: 8
    Last Post: 07-05-2012, 01:38 PM
  4. WorthIt fixed assets
    By andres179 in forum Access
    Replies: 1
    Last Post: 05-29-2012, 11:09 PM
  5. Database Design for Specification Database
    By khwaja in forum Database Design
    Replies: 2
    Last Post: 11-24-2011, 03:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums