Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

MSAccess 2000 Newbie Question...

Discussion in 'Information Technology' started by tomorrowsman@gmail.com, Jul 28, 2006.

  1. tomorrowsman@gmail.com

    tomorrowsman@gmail.com
    Expand Collapse
    Guest

    Hello everyone,

    I have almost never touched Access before, and I think I have to use it
    for what I would like to do; it is this:

    I have weekly pay data .csv files with 14 fields, including employee ID
    number, pay code, shift, pay rate, etc. The .csv files are named by
    pay period end date (e.g., 20060507; 20060604). Using a date range, I
    would like to be able to compile reports about overtime hours worked
    and paid, or hours by shift, etc. This kind of thing is easy enough
    for me in Excel on a week by week basis, but I get stumped at how to
    compile either annual data, or selected range data (for instance, if I
    was able to pivot just the pay periods in Q3, for that quarter's data).

    I did two things before breaking down for advice: I have one Excel
    workbook with all of the .csv files as worksheets; and I built my first
    ever Access database, with each of the .csv files as imported tables.
    (Oh, importantly, the 14 fields are all consistently named in each .csv
    file.)

    The amount of data would crush Excel's 65k row limit pretty quickly;
    so, I went to Access...but I'm unsure how to get the info out of it
    now. I would think I should be able to use Excel as a 'front end,' and
    I would just need to tell the pivot table what to total, etc. I feel
    like it's simple, but I'm not getting it; Access relationships across
    50 tables are baffling me....

    Can anyone offer me any advice as to how to query this data without
    trying to wrap my head around SQL? Or should I do just that?

    Cheers,
    Chris
     
  2. Loading...

    Similar Threads Forum Date
    Manmeet Kaur 2000 Joins Sikh Philosophy Network! New SPN'ers Sep 26, 2016
    Islam Number of Moques increase by 74% in USA after 2000! Interfaith Dialogues Mar 2, 2012
    1984 Hyde Park 2000 Rally in Memory of Bluestar History of Sikhism Jun 13, 2009
    Sikh News Fox 2000 bags rights for 'The Jury' (ANI via Yahoo! India News) Breaking News Oct 3, 2007
    Sikh News Punjab to get Rs 2000 crore from Rural Health Mission (New Kerala) Breaking News Aug 18, 2007

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    You need to create a table with the 14 fields plus another field for the
    date. Use a datatype of DateTime. Append the 50 tables one at a time.

    You will need to create a column in your append query to add the date if it
    is not included in the 14 fields. In a blank dolumn in the Field row enter --
    MyExcelDate: #05/07/2006# for you 20060507 file. Change to MyExcelDate:
    #06/04/2006# for 20060604 file.


    "tomorrowsman@gmail.com" wrote:

    > Hello everyone,
    >
    > I have almost never touched Access before, and I think I have to use it
    > for what I would like to do; it is this:
    >
    > I have weekly pay data .csv files with 14 fields, including employee ID
    > number, pay code, shift, pay rate, etc. The .csv files are named by
    > pay period end date (e.g., 20060507; 20060604). Using a date range, I
    > would like to be able to compile reports about overtime hours worked
    > and paid, or hours by shift, etc. This kind of thing is easy enough
    > for me in Excel on a week by week basis, but I get stumped at how to
    > compile either annual data, or selected range data (for instance, if I
    > was able to pivot just the pay periods in Q3, for that quarter's data).
    >
    > I did two things before breaking down for advice: I have one Excel
    > workbook with all of the .csv files as worksheets; and I built my first
    > ever Access database, with each of the .csv files as imported tables.
    > (Oh, importantly, the 14 fields are all consistently named in each .csv
    > file.)
    >
    > The amount of data would crush Excel's 65k row limit pretty quickly;
    > so, I went to Access...but I'm unsure how to get the info out of it
    > now. I would think I should be able to use Excel as a 'front end,' and
    > I would just need to tell the pivot table what to total, etc. I feel
    > like it's simple, but I'm not getting it; Access relationships across
    > 50 tables are baffling me....
    >
    > Can anyone offer me any advice as to how to query this data without
    > trying to wrap my head around SQL? Or should I do just that?
    >
    > Cheers,
    > Chris
    >
    >
     
  4. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    I can't speak to using Excel as a front end to Access, since I don't do it,
    but I can tell you one thing you should do with your data: Don't make 50
    tables.

    Make 1 table, using the columns you currently have. Add one column. Call
    it what you like, but use it to indicate somehow which file or spreadhsheet
    tab the rows came from. As you import this data (see question below) be
    sure that your append query (you are appending all rows to the same table)
    sets the value of the "OriginalSource" field (or whatever you call it.)

    After you have your data in one table, you can use queries to select it out
    into one set of all existing data or subsets of whichever rows you like.

    At this point, you have started to treat your spreadsheet information as
    data.

    Question: Is this an operation you must do repeatedly, or is it a one-time
    import, after which you enter data in your new Access table. (Table,
    singular, one table)
     
  5. tomorrowsman@gmail.com

    tomorrowsman@gmail.com
    Expand Collapse
    Guest

    Rick,

    I would need to update the table with a new set of data via the .csv
    file once a week for the fiscal year; so, by the time I'm done, I have
    added 52 sets of data; then I would start over with next year's file.

    My process now generates the .csv file each Monday; then I would need
    to add it to the database and refresh the totals (wherever they
    are...Access pivot table or report?).

    Thanks; I'm going to carefully go over what you posted, see if my
    Access-less brain can figure it out....

    Cheers,
    Chris
     
  6. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    If you're importing weekly, you can add another column to your one Access
    data table, something along the line of DateCreated. YOu can set that date
    in your append queries the same as you set the "OriginalSource" text.

    You'll need some mechanism for identifying the current data set for when you
    want to select data. You could have a parameter table with a row containing
    the current data set's DateCreated, or you can use grouping query to select
    Max(DateCreated), although this is less reliable. If you store the date to
    use for current data and refer to that, you don't get hit in the face is
    someone is running a report (expecting what they think of as "current data")
    while you are in the middle of creating what is about to become the next
    current data.
     

Share This Page