Results 1 to 9 of 9
  1. #1
    JenHolz is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    4

    Date of entry vs Dates of Data, Data uploads from Excel and How data is stored

    Hello Community!



    I hope you can lend some advice. I am COMPLETELY new to working in access, so please forgive me if the answers should be obvious (they aren't to me). Although I am familiar with principles from reading through design basics, I don't have enough experience to know if I am on the right track in my design or not. I hope you can help me work through what to do next.

    I have a report to generate monthly (on the first of each month) where I work. The data is for the stock value on the first of the month for each brand, plus the monthly turnover and average margin for the month just passed for each brand. (eg - stock value on 1 Jan 2018, monthly turnover and average margin for December 2017). Also, I would like to be able to import all of this data in one go with an Excel spreadsheet.


    I am getting hung up on a few points:

    Should I store the data by the entry date (i.e. first of the month) given how I want to import the data, or is this bad design in the long run? My current design draft (obvs incomplete at this point) -assumes this is ok. If indeed it is okay, then how do you then sort/display/query data for the previous month if they are all in the same table? I have a separate date table which I had hope would be able to distinguish between 1st of month and previous month. However I am not confident in designing it this way, ie how to utilize the turnover/avg margin date column from it when it comes to the main table.

    Would it be better to store data in separate tables by the date of the data (rather than by entry date), and if so, how would you append the imported data (by columns from a single excel worksheet) into separate tables in access.

    The end game here is to be able to pull historical data by brand (by month/year etc) and include charts with overlapping line graphs. The existing report is in excel at the moment and needs an upgrade/streamlining due to sheer amount of data involved.

    A few notes relating to my design pdf:
    *brand id used to ensure no duplication - there are just shy of 1000 brands, some with similar names
    *turnover and margin date col =is manually entered date in chart, formatted as mmmm yyyy, aso opposed to being a formatted version of data entry date
    *Value type = value or not value (to designate no data from lack of data, such as when a brand is discontinued)

    All suggestions (and patience with my newness) appreciated.
    Thanks
    Attached Files Attached Files

  2. #2
    keviny04 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Apr 2015
    Posts
    128
    Sounds like you want to put dates in two different places just to make your job more convenient. But database design frowns upon duplicate data entries. Hence, store all your dates in one place, and you obtain whatever date-related info with other means, such as functions and criteria. To get the first date of a month, use the criterion Day([date])=1 in queries. To get data for month N, use the criterion Month([date])=N in queries. This comes under "normalization" of tables in database design, which is basically that you don't duplicate data unless they are actually different data.

  3. #3
    JenHolz is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    4
    Thanks Keviny04. So is my dates table idea correct then? I thought by using the unique id to link both dates (stock value date and prev months' data month), and then using that unique ID (and other fields) to form part of the composite primary key in the main storage table, that the rules of normalisation would be satisfied? So say unique id 1 would have a field which is 1 March 2018, and the next field would be April 2018. The unique date ID 1 would then be attached to the brand named and value type for eacy to become the CPK. Just realized you can't see the CPK in the PDF, so it does look like I want to repeat data, but that is not the case.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

    What do you mean by 'distinguish between 1st of month and previous month'? You retrieve subsets of data by applying filter criteria. Criteria can be supplied by user input or code.

    Your ReportingDates table is rather unusual approach but should work although is not really needed. The previous or following month date can be calculated.

    You said formatted as "mmmm yyyy" - are you saving an actual date and applying format or is this really a text string? Really should be an actual data value. However, if it is a string it can be converted to a date with calculation like:

    CDate(1 & " " & [Report Date and Stock Valuation])

    Calculate the previous or following month date:

    DateAdd("m", 1, CDate(1 & " " & [Report Date and Stock Valuation]))

    Conversely, an actual date can be formatted to display as MMMM YYYY with calculation like Format([Report Date], "MMMM YYYY") or use textbox Format property.
    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.

  5. #5
    JenHolz is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    4
    Thank you June7. I appreciate your response, however my knowledge and understanding is limited, so its not sinking in yet. To answer your questions:

    'distinguish between 1st of month and previous month' - I have attached two examples (fingers crossed the attachment is visible to you), which reflects what I cannot seem to get my head around. I hope that you can maybe understand from that where I am getting stuck in the table design.

    re formatted mmmm yyyy, that is an actual date with an applied format. I do not yet understand your reference to strings, so I have to go back and read/study - I am learning as I go.

    thank you again and in advance.
    Attached Files Attached Files

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't understand what you are trying to build. That last image shows date values in the fields, not financial info as I would expect by the names of the fields.
    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.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by June7 View Post
    I don't understand what you are trying to build. That last image shows date values in the fields, not financial info as I would expect by the names of the fields.
    I believe the OP is indicating the dates or date ranges those fields apply to
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    JenHolz is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    4
    Hi June7 and ridders52

    Dates will not go in those fields - they will be financial data (turnover/margin/stock value). I think ridders52 picked up on what I was trying to explain (thanks). Positionally and for the purposes of storage, I am trying to figure out where to put the data. The two possibilities (and where I am stuck) is what is shown in the second attachment.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So the two examples reflect the options you are considering for data storage?

    You say option 1 'mirrors the date and format of our system exported data'. What is 'our system'?

    If data on separate rows must be output as single record and/or used together in calculation, this can require a nested subquery. Review http://allenbrowne.com/subquery-01.html#AnotherRecord. Also http://allenbrowne.com/func-concat.html.

    Nested queries or queries calling complex custom function can perform slowly with very large datasets.

    I suggest you save the data in structure that facilitates your reporting requirements and thoroughly document the model.
    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.

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

Similar Threads

  1. Replies: 9
    Last Post: 03-01-2017, 10:00 AM
  2. Replies: 1
    Last Post: 07-24-2015, 07:06 AM
  3. Replies: 7
    Last Post: 03-05-2015, 07:34 AM
  4. Data Entry based on dates
    By islewis in forum Forms
    Replies: 3
    Last Post: 03-07-2014, 02:58 PM
  5. Replies: 1
    Last Post: 11-15-2012, 09:16 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