Results 1 to 6 of 6
  1. #1
    gurusix is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    3

    Db Design for Effeciency

    I have an excel spreadsheet that is unpopulated(no data). I want to turn this excel sheet into a database. The excel sheet highlights all the information I request with equipment requisitions. My question is: Must I turn each field/column in the excel sheet (there are 30 fields/column btw) into a database Table? Or do I make one simple Table with all the fields in the excel table as fields in the one Access Table?



    Additionally, which ever route is recommend from the above question, Could I then use a Access Form to input new requisitions? Overall all the data that would be input to my excel sheet I track as a single requisition. I give it a numerical designation.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Access is not Excel, it is a completely different animal.

    but to answer your question, it depends on your data but technically, neither.

    example data for invoices in excel might look like this

    customername...customeraddress...invoicenumber...i nvoicedate...productdescription...quantity...price ...value

    this would be stored in access in a number of tables (how many depends on requirement) - something like

    tblCustomers
    customerPK
    customername
    customeraddress

    tblProducts

    ProductPK
    productdescription
    productprice

    tblInvoiceHeader
    InvoiceHeaderPK
    customerFK
    invoicenumber
    invoicedate

    tblInvoiceLines
    InvoiceLinePK
    InvoiceHeaderFK
    ProductFK
    quantity
    price

    note value would not be stored because it can be calculated when required

    These tables may well contain other fields (e.g. customer telephone number) or there may be additional tables - e.g. if customers can have several addresses you would store the addresses in another table. The 'objective' is to store data only once without duplication - this is called normalisation.

    Similarly, products might have a variety of prices based on quantity or some other factor, so prices would be stored in a separate table from tblProducts.

    You will also note there are additional fields (...PK, ..FK) PK =PrimaryKey and is a number unique to that record, FK= ForeignKey or FamilyKey and provides the link back to the relevant PK

    You will note that price is actually stored twice - once in tblProducts and again in tblInvoiceLines. Although this breaks the 'store only once' rule, it is necessary - if the price changes in tblProducts, you wouldn't want all your invoices to change.

    All the above tables would be joined using a query fit for purpose - you want to know total sales? you just need tblInvoiceLines. Sales by year/month? - join tblInvoiceLines to tblInvoiceHeader. Most products sold? - join tblInvoiceLines to tblProducts. You want a view like your spreadsheet? - join all tables together
    Could I then use a Access Form to input new requisitions?
    yes - but be aware access cannot update an excel spreadsheet through a form - you would need to export the data to a new spreadsheet

  3. #3
    gurusix is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    3
    Thanks Ajax, so it sounds like my first order of business is to go ahead and build all the Tables I require. My organization wants to get away from the excel sheet and I am on the hook to make it happen. Once I build the Tables can I run the Db by you for a sanity check on my work?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Can do, but table design on it's own is not enough. For a proper sanity check, you will also need to explain your business and the rules/processes required - a roadmap if you like and what you want the db to do. e.g. requisitions - what happens if the item required is not in stock and is/is not on order?

    Also recommend that before jumping in and creating tables in Access, design on paper first - then put different scenarios to it and see if it meets the requirements - if not, change it. This will save you days in having to rebuild when something doesn't work

    You may well find that Access can provide a template for your basic structure.

    Also, learn some basics:

    Access stores data 'tall and thin', Excel is 'short and wide'
    if you start designing a table with fields called date1, date2, date3 etc - you are designing it wrong
    Avoid spaces in field and table names (use an underscore if you must) - users will not see the tables and fields anyway and they can be labelled as whatever you want (see the caption property)
    only use alphanumeric characters in field and table names and don't start them with a number
    avoid reserved words - favourite errors are date, name, description, time and order - using them will cause issues - here is a link for most of them
    https://support.office.com/en-us/art...7-da237c63eabe
    use proper terminology for field, control, form, report etc (not cells and sheets!) - this will also help when googling for solutions/ideas or communicating with responders on this and other forums
    avoid using lookups in table field design (they slow the db and when you've forgotten you did this, waste days of your time trying to figure out why something isn't working) - see this link http://access.mvps.org/access/lookupfields.htm
    ditto for multivalue fields - looks nice but at the cost of losing some control over how the db performs - also has a potential hit on performance.

    Sorry, bit of a list but most of it is straight forward and easy habit to get into

  5. #5
    gurusix is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    3
    Relationships for (U)SO_Test_Database_v1.4.pdf


    Ajax,
    We need a better way to track and manage data by Requisition Number(XXXX)Requisitions comes to us in the format of an excel spreadsheet. (see attached XXXX EquipandRequisition.xls)
    On the attached spreadsheet the User/Customer provides all fields in Orange, each line item provides detail on what the User/Customer wants.
    Once this spreadsheet is submitted and is approved and is given a Requisition number/name: XXXX
    My section will initiate Procurement Actions to fulfill the requisition, currently all items are purchased, we do not have now but in next 6 months we will have a standing inventory of items to pull from.
    Throughout our process my section will update the spreadsheet, to include filling in the Green columns by line item as info becomes available through the procurement process.

    What does my section want from Access:

    *Stop using excel as a means to track items in our procurement process
    *Streamline our requisition process and provide better awareness to leadership
    *User/Customer will submit Requisition via our web portal, via web access with Access Form
    * User/Customer requisition Form should encompass Orange fields in attached spreadsheet, to include drop down menu selections for example, Size, whether user needs a 7 or an XL (see attached XXXX EquipandRequisition.xls)
    *A Form my section can access to update User/Customer request by line item as that information is received.
    *Need to be able to generate Queries easily to gathering information in Gray area of the spreadsheet
    *Generate summary reports with selected fields for leadership


    I have attached the relationship report from Relationships for (U)SO_Test_Database_v1.4.pdfEquipment RequisitionSpreadsheet.zipRelationships for (U)SO_Test_Database_v1.4.pdfmy working Db as well. I did inherit a few of the tables but I have already combed through and deleted tables I thought were unnecessary, while adding 2 additional tables. One of which being (U)Tbl_User_REQs, which is where I envision populating this Table via and Access Form, and my section continually updating via another Access Form. This Table essentially mirrors column in the attached XXXX EquipandRequisition.xls spreadsheet.

    Equipment RequisitionSpreadsheet.zip

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I've taken a brief look at your relationships and have three comments - if my understanding is wrong please correct me!:

    1. you have many tables which look the same - all the tbl_Mat.... ones. This may be valid but I would expect them to be in one table, that table having an additional column for 'type' i.e. CDISR, CBRNE, Ammunition etc. Suggest revisit your understanding of normalisation - the way you are structured at the moment you cannot just select from a materials table, first you need to decide which table to look in.

    2. More importantly you are linking REQID to the ID's of the other tables which does not look right. If I have read it right, tbl_REQ is the 'header' information (same for all lines of the requisition) and there should be a tbl_REQ_Lines which would look something like

    tblREQLines

    REQLinesPK
    REQFK - links back to REQID
    ManufacturerPK - links to ID in tblManufacturer
    etc (not sure about the rest, depends on 1 above)

    also review the example I provided originally - requisitions and invoices are structured pretty much the same.

    3. I would also expect to see a relationship between manufacturer and materials - not all manufacturers produce all materials. And whilst we are at it, presumably not all vendors supply from all manufacturers.

    The other thing to consider is can a material be supplied by different manufacturers? it may have a different reference/code but is it otherwise the same. I know nothing of military supplies and this is probably not a great analogy but if you go into a shop to buy baked beans - there may be 6 varieties of beans from different manufacturers. You may have a personal preference, but at the end of the day, they are baked beans. If you preference isn't there you have a choice, buy another manufacturers or go hungry.

    I can see your dropdowns for sizes, colour etc and since these are lookups you could have different lookup tables, one for each column. They do not need a uniqueID column as such - just set the description as a primary key. Alternatively all they could all go into fewer tables with an extra column to indicate type (i.e. the column header) - e.g. one table for all colours, one for all sizes etc

    One thing to consider is sizes are expressed as strings and strings sort differently to numbers so

    2S
    9-XS
    10-XS

    will actually sort as

    10-XS
    2S
    9-XS

    (there is a workaround - sort by VAL(size) - when you get to it)

    Next - if the orange bit is completed by the user customer - wouldn't they be specifying what they want first in terms of colour/size etc then looking at manufacturers and finally vendors? i.e. columns are not in a user friendly order. And would they be selecting vendor as well?

    Finally, you say users will be placing orders on the system and will be authorised though the system. If so, you a) need a table for authorised users (user/customer or authoriser) for names, logins authorisation levels etc and b)some means of knowing when an order is authorised and by whom - probably stored in tbl_REQ

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

Similar Threads

  1. Design vs Article (=Design+Materials) problem
    By emihir0 in forum Database Design
    Replies: 14
    Last Post: 04-20-2015, 03:03 PM
  2. Design Help!
    By JGrots in forum Database Design
    Replies: 5
    Last Post: 04-02-2013, 01:46 PM
  3. Help with Design
    By RachelBedi in forum Access
    Replies: 2
    Last Post: 01-24-2013, 02:44 PM
  4. Table Design -- want to avoid a design that creates blank fields
    By snowboarder234 in forum Database Design
    Replies: 4
    Last Post: 10-08-2012, 10:13 AM
  5. Design help
    By jacko311 in forum Database Design
    Replies: 0
    Last Post: 11-12-2009, 05:57 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