MSAccess 2000 Newbie Question...

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

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.


"[email protected]" 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
>
>

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)

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

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.