Results 1 to 9 of 9
  1. #1
    dmanonice is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2020
    Posts
    10

    Question Database Design Help

    Hello Everyone,



    I am trying to figure out the best way to go about designing my database for easy updating and to get my query to return what I want it to. Here is what I am dealing with.

    Data Set 1 Forecasting

    This data set only needs to be uploaded once when the forecasting is completed. I was thinking of using this as my base table. It has 7 columns "Company" "Date" "Link" "Network" "Product" "Forecast" & "Surge".

    Data Set 2 Actuals

    This data set gets updated daily and shares the first 5 columns with Data Set 1 and has a 6th column as "Actuals"

    My original though was to upload them both into Access in individual tables and create a query to join the information. While this works, it results in a large amount of duplicate data (5 columns) and the query results with missing information (see problem 2 below). So my new though is to import Data Set 1 and then use an append query to update the "Actuals" into what would now become a master table.

    There are 2 problems I am facing.

    Problem 1 I do not know how to structure both the import of data from excel to an append query. I am familiar with importing external data to new tables, or adding to the bottom of existing tables, but I have no idea where to begin with matching 5 columns of data and adding what would become an 8th column for actuals.

    Problem 2 There will be times where the Data Set 2 will have data that Data Set 1 does not ad this will then need to create a row for the data with the "Forecast" and "Surge" values at 0. There will also need to be times were Data Set 1 will have data and Data Set 2 will not, thus the new "Actuals" will need to be 0.

    I am thinking this will take multiple steps to accomplish but any insight you all have would be greatly appreciated.

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    I would interpret Data Set 1 as being the Details of the Company.

    Then Data set 2 would be the actual data you would import which is related to Companies.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Yes, quite possibly multiple steps will be involved.

    Hopefully, all 5 common columns are not needed for establishing relationship between datasets. What is the unique identifier that links data? Unless you can depend on exact same spelling of Company throughout Excel, Company name will be a poor identifier. Same for Product names.

    For a relational database structure, possibly need at least 3 tables:

    Companies

    Products

    CompanyProductForecastActual

    No idea how Network, Link, Surge impact relationships. More 'lookup' tables and fields in master data?

    Possibly need to use FindUnmatched query to locate new companies and/or products and add them to Companies and Products tables.

    Otherwise, just repeat company and product names and have one big 'flat file' table.

    Explore concept of UPSERT action query. Review https://stackoverflow.com/questions/...g-in-ms-access
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    dmanonice is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2020
    Posts
    10
    Data consistency is not a problem, the companies and products are coded in 3 letter codes and are used in both forecasting and by the software for the actuals. Other than my inputting the company name (the downloads are from different software for each company) there is no manual intervention.

    It is possible that all 5 columns will need to be used to match the data, as I will need to match the company to the link to the product to the network on each day.

    There are 2 companies
    There are about 200 links
    There are 3 products
    There are 2 networks

    So basically both companies in the same day will transport all 3 products on the 200 links on both networks. Each link in each network has its own forecast, surge and actual volumes.

    so when I get the actuals for the day I need to add this information to the forecast to compare and evaluate. Excel won't do it in a pivot table as there are too many duplicates and I can do it with multiple tables in access but I am missing 5% of the data, where it appears in one table but not the other.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you tell us in simple terms what your business processes are?

    That is, given Company X and Software Y can you describe how network, forecast and surge apply/relate?

    Just need some context to understand your requirement and offer focused assistance.

  6. #6
    dmanonice is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2020
    Posts
    10
    I will do my best

    The two networks relate to departure times, we have 2 banks so network 1 would depart between 3-7am and network 2 between 3-10pm. Our volumes fluctuate day by day and so each link has a volume forecasted for each network. The company relationship is parent/child so company 1 owns company 2 but for transportation purposes we operate as one company but report separately. (it's strange I'm not getting into it!)

    So we create a forecast for each link and then there is a percentage above the forecast for each link that must also be carried which is called the surge. These two numbers live in the same table already as they come from the same source and form the first data set.

    I go in each day and download the actual volumes loaded for the day from each company's software. They have been set up to generate reports in the same manner so they can be combined into 1 excel table which I can then put into access.

    what I am looking to achieve is having 1 table that has the following outputs (bold is common to both sets of data, Italics is data set 1 only and underlined data set 2 only)

    COMPANY DATE LINK NETWORK PRODUCT FORECAST SURGE ACTUAL
    AAA. 2021/01/01 YYYZZZ 1 01A 150 180 175

    normally I would just stay in excel and copy paste, but there are 1,200+ inputs a day and some days the links are forecasted but don't load anything and sometimes the forecast omitted a link and we have no forecast but we carried volume so its just not practical.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Be aware that DATE is a reserved word in Access and a built in function and shouldn't be used for object names. Plus it is not very descriptive: DATE of what?? "Forecast"? "Surge"? Date data was added or updated?

  8. #8
    dmanonice is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2020
    Posts
    10
    Quote Originally Posted by ssanfu View Post
    Be aware that DATE is a reserved word in Access and a built in function and shouldn't be used for object names. Plus it is not very descriptive: DATE of what?? "Forecast"? "Surge"? Date data was added or updated?
    that is an easy enough change DATE will become Departure.

  9. #9
    CarlettoFed is online now Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Just create a single table, which for example you call tblDatas, with the eight fields
    COMPANY DATE LINK NETWORK PRODUCT FORECAST SURGE ACTUAL


    to manage the data of excel file 1 you must: connect the excel file via the link to a temporary table, for example tblTemp
    • append in the tblDatas the records of the tblTemp that compared do not exist in the tblDatas and writing the value 0 in the ACTUAL field.
    • delete the tblTemp table.

    to manage the data of the excel file 2 you must:
    • connect the excel file via the link to a temporary table, for example tblTemp update in the tblDatas the records of the tblTemp that compared appear to exist in the tblDatas and writing in the ACTUAL field of the tblDatas the value of the ACTUAL field of the tblTemp
    • append in the tblDatas the records of the tblTemp that compared do not exist in the tblDatas by writing the value 0 in the FORECAST SURGE fields
    • delete the tblTemp table.

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

Similar Threads

  1. Replies: 9
    Last Post: 06-23-2018, 10:25 PM
  2. Replies: 18
    Last Post: 12-04-2017, 05:25 PM
  3. Replies: 3
    Last Post: 01-13-2017, 03:52 PM
  4. Replies: 2
    Last Post: 01-28-2013, 04:42 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