Data manipulation

  • Thread starter Thread starter Dianne
  • Start date Start date
D

Dianne

I have an Excel Spreadsheet that is a report from a larger system. The report
is a summart that puts an employee's name with a schedule and a grand total.
The problem is that the employees's name is not filled in next to the
schedule nor the total. See below. Is there a way to automatically populate
the fields with the name going through the records in order so that the name
is next to the schedule? Right now this is being done manually on thousands
of records.

Employee Start Date Time
Doe, John
8/31/2008 0
8/30/2008 0
8/29/2008 5.97
8/28/2008 6.82
8/27/2008 6.78
8/26/2008 6.82
8/25/2008 6.7
8/24/2008 0
Total: 33.08
Doe, Jane
8/31/2008 0
8/30/2008 0
8/29/2008 3.18
8/28/2008 6.52
8/27/2008 6.57
8/26/2008 7
8/25/2008 0
8/24/2008 0
Total: 23.27
 
One more note. I would prefer to do this in Access as this is imported into
an Access database for report purposes.
 
My assumption is that you want to put the data into a table - I don't know if
you need all the detail or just each name and total time. I don't know of a
simple solution - someone else may. The easiest thing to do may be to change
the process that creates the Excel spreadsheet.
Here's what I would do, given that spreadsheet data. I'd do a
column-delimited import into a table - this assumes that your report will
always appear in the same format. The table would have fields for name, date
and time, and be used as a temporary table. If you need all the details, a
routine like this would work:

DIm rstIn as ADODB.recordset
DIm rstOut as ADODB.recordset
dim prevName as string

Set rstIn = new ADODB.recordset
rstIn.open "tblWithImportedData", currentproject.connection, _
acforwardonly,adLockOptimistic
Set rstOut = new ADODB.recordset
rst.open "tblData", currentproject.connection, _
adOpenDynamic, adLockOptimistic

while not rstIn.eof
if not isnull(rstIn!Name) then
currentName = rstIn!Name
else
if isdate(rstIn!StartDate) then
rstOut.AddNew
rstOut!Name = currentName
rstOut!StartDate = rstIn!StartDate
rstOut!EmpTime = rstIn!EmpTime
rstout.Update
end if
end if
rstIn.movenext
wend
rstOut.close
rstIn.close
set rstin = nothing
set rstOut = nothing

This would give you a table with one row for every date and time for each
employee name. If you just wanted the name and total time and don't care
about all the details it would be a bit different. With the table I
described, you could then create a report based on that table and summarize
the times in the report by name. The only danger here is if you have more
than one person with the exact same name. You might want to create an ID as
you read the data, setting it to 0 initially and incrementing it by 1 each
time you come across a new name. Then write that ID along with the other data
when adding a record, and use that ID in your report's summarization. Hope
this doesn't seem to complicated, but like I said I don't know of any easy
way to do that, unless you do it while the data is still in Excel.
 
I forgot, if you used that method, you need to define the fields in the
temporary table as text fields. Then, when assigning the values, convert them
to the appropriate data type, e.g. rstOut!StartDate =
CDate(rstIn!StartDateTime).
 
Back
Top