Results 1 to 3 of 3
  1. #1
    jamesdeuce is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Location
    Houston, TX
    Posts
    22

    Newbie ~ Unsure Best Practices of Linking 1000s of .xls files in Access and Querying

    I have about 1500 spreadsheets that have the same format. The headers are basically a Location, Date, and then the subsequent headers are 1, 2, 3...24 for each hour of the day and below that there is a price at each location (10000 locations) which is different every hour.

    Location Date 1 2 3 .... 24
    Houston 2-16-12 $25 $30 $40 $20
    Dallas 2-16-12 $20 $25 $50 $10
    .
    .
    10000 rows down of data

    For each day there are 2 of these spreadsheets. Why are there two? Because there are 2 sets of prices for each city. One price is established the day before and one price is established day of. Each sheet has the same cities but the prices will be the different for the same given date and hour. The second sheet for 2/16 is seen below for example

    Location Date 1 2 3 .... 24
    Houston 2-16-12 $50 $15 $20 $35
    Dallas 2-16-12 $0 $40 $15 $25
    .
    .
    10000 rows down of data

    My goal is to bring all of these spreadsheets into Access so I can look at spreads between the prices for a specific city on a specific hour.



    I am not sure if it's supposed to be one Access file with 3000 tables in it (1500 days of files times 2 sheets for each day). Will all that even fit (bytes wise) in one Access file? I am not sure what to make the Primary Key whether it's the cities, or the date (02-16-2012), or the date and hour appended together (02-16-2012-01 through 02-16-2012-24) as that is unique from day to day. What should the relationships be? Should I link the mother files from Excel into Access or hardcode them in? Is there an easy way to get 3000 Excel files into Access?

    This will definitely require some math and statistical analysis but for right now I just need some structural help as I am new to databasing and the books I have read don't exactly give an example as such.

    Thank you for reading and my hope is that these are simple questions for someone with some database and Access experience. I guess more importantly, can this all be done in Access? I appreciate it.

    If I wasn't clear or if you have further questions let me know.

    Jimmy

  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,716
    What are you trying to accomplish -- what does this data represent and what would be the "answer/solution"?
    Agreed th,ere is a lot of data, but just numbers until you put it into some context so the rest of us understand "the problem/opportunity".

    Is this some ongoing process?

    You seem quite conversant in "spreadsheet talk". Excel is very different than Access/RDBMS.
    It might be worth the effort to do some research on database principles and design and Normalization.

  3. #3
    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,716
    Further to the PM sent recently here is some material (links) that may be useful.

    There is an example of all the steps involved in designing a database in a series of free videos. The theme/subject is Customer, Order, LineItems and Products.
    The videos are available here. I recommend that you watch them; bookmark the site and go back and review as you need to.

    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

    http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

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

Similar Threads

  1. Linking to csv files
    By alc in forum Queries
    Replies: 1
    Last Post: 02-07-2012, 07:43 PM
  2. Best Practices
    By Sinkerhawk in forum Access
    Replies: 2
    Last Post: 06-30-2011, 11:44 AM
  3. Replies: 0
    Last Post: 03-04-2011, 10:28 AM
  4. Replies: 1
    Last Post: 02-21-2011, 09:55 PM
  5. Linking up two files
    By heidiffg in forum Access
    Replies: 0
    Last Post: 02-14-2011, 09:39 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