Results 1 to 4 of 4
  1. #1
    jeffd is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Location
    Dallas,TX
    Posts
    2

    Retail Sales Database Setup


    Good morning! This is my first post and I'm a bit stumped with Access. I have several large excel data files. They contain sales history from the past five years. The rows are essentially: SKU, Item Description, Store Location, Increment of Product (either weight or single unit), Total Units sold, Total Sales and Period within the year. I don't know how to breakdown the files into relevant tables that would have a relationship.

    I know the SKU is the most important because I want to be able to run queries showing what where the total sales/units sold over the life of the item, potentially broken down by period. Any help would be greatly appreciated!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly is the purpose of this proposed database?
    If you have the info in excel spreadsheets, what do you expect to gain by putting that data into Access?
    We need to understand what you are trying to accomplish before we can offer focused advice/options.

    Good luck.

  3. #3
    jeffd is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Location
    Dallas,TX
    Posts
    2
    The purpose of the database, to me, is to consolidate redundancy within the excel files. They are quite large, I can max out a single sheet with this data and there are hundreds of them. Maybe access isn't the way to go but I know that excel can't handle the limits of data that I have currently. The sheets all maintain identical headers so my initial thought was creating new folders i.e Product SKU=> Location=> Sales History (completely a guess in the dark).

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The rows are essentially: SKU, Item Description, Store Location, Increment of Product (either weight or single unit), Total Units sold, Total Sales and Period within the year. I don't know how to breakdown the files into relevant tables that would have a relationship.
    The sheets all maintain identical headers so
    Given the column headers, I see 3 tables:
    tblProducts - SKU, Item Description
    tblStores - Store Location
    tblSales - Increment of Product (either weight or single unit), Total Units sold, Total Sales and Period within the year

    The actual field names would depend on the Excel column headers. Each of the tables would have an autonumber field as the PK field.

    The way I see it, the steps would be something like:
    1) import all of the worksheets from all of the workbooks into Access (tblSales).
    2) add two new fields to tblSales: "ProductsID_FK" (type number/long) and "StoresID_FK" (type number/long)

    3) create table "tblProducts" with fields "Products_PK" (type Autonumber), SKU (type Text) and "ItemDesc" (type Text)
    4) create table "tblStores" with fields "Stores_PK" (type Autonumber), StoreLocation (type Text)

    5) with tblSales, use an append query to append the SKU's (Unique values) and descriptions into the "tblProducts" table.
    6) with tblSales, use an append query to append the "Store Location" (unique values) into the "tblStores" table

    7) using an update query, update tblSales.ProductsID_FK with the tblProducts.ProductsID_PK value
    8) using an update query, update tblSales.StoresID_FK with the tblStores.StoresID_PK value

    9) now in tblSales, delete the fields "SKU", "Item Description" and "Store Location"

    10) in all three tables, I would edit the field names to remove any spaces, punctuation and/or special characters (exception is the underscore)


    From what you have provided, this is how I proceed...... modify as you see fit


    Good luck.....

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

Similar Threads

  1. Replies: 2
    Last Post: 01-16-2016, 09:50 PM
  2. Replies: 0
    Last Post: 12-13-2012, 03:18 AM
  3. New Database Setup
    By sirwalterjones in forum Access
    Replies: 3
    Last Post: 12-14-2011, 08:38 PM
  4. Replies: 5
    Last Post: 06-30-2011, 02:24 AM
  5. retail and wholsale in a ms access 2007 database
    By damie in forum Database Design
    Replies: 7
    Last Post: 12-16-2010, 12:29 PM

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