MSAccess 2000 Newbie Question...

[tomorrowsman]

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

 

[KARL DEWEY]

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]

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