Home |
Search |
Today's Posts |
#1
Posted to comp.databases.ms-access,microsoft.public.access,microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.access.gettingstarted
|
|||
|
|||
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 |
#2
Posted to microsoft.public.access.gettingstarted,microsoft.public.excel.programming,microsoft.public.access,microsoft.public.excel
|
|||
|
|||
MSAccess 2000 Newbie Question...
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. " 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 |
#3
Posted to comp.databases.ms-access,microsoft.public.access,microsoft.public.access.gettingstarted,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
MSAccess 2000 Newbie Question...
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) |
#4
Posted to comp.databases.ms-access,microsoft.public.access,microsoft.public.access.gettingstarted,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
MSAccess 2000 Newbie Question...
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 |
#5
Posted to comp.databases.ms-access,microsoft.public.access,microsoft.public.access.gettingstarted,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
MSAccess 2000 Newbie Question...
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Real Newbie newbie question | New Users to Excel | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
Newbie Question | Excel Programming | |||
Newbie question! | Excel Programming | |||
newbie question | Excel Programming |