Do you know how to work with 'named-ranges' in Excel? If so, then you
can use the File->Get External Data->Import option to pull it in...
What you have to do, won't be real easy, but won't be too terribly
difficult either... (I may not offer the best solution, so check back
to see what others may contribute)
By the way, once you get started, it is very important to keep the
rows together as they are, for getting the relationships right in the
end will strictly depend on it!!
1. I'd make a named ranged for employee names and import that into a
table of employees and let access create the table with a primary key.
I'd make sure the employees are listed for ALL days, even if new,
it'll be important for the relating... also enter dummy data next to
their name(s).
2. Create named ranges for EACH DAY'S data ( the work, overtime,
slots1-4, and the others columns, together in a range) SEPARATELY, and
import them separately into separate tables with the date in the table
name (this will become handy when updating/appending the table(s)
later...) [This will create the same numbers as the employees table so
that they can be related :-)]
DO NOT CREATE PRIMARY KEYS!
3. Now, each table will have to edited to add two fields: an empID
field with it's type (momentarily) set to autonumber and a date field.
As I think about it now, but it's effort either way, you could create
a table in design mode that has all the fields you need. Then, before
you start importing the daily data, copy the table and paste it back
into the table list with a name having a different day. Paste all you
need, changing the date in the name until you have all of what you
need, and then start item #2 -- I'll leave this thought up to you.
4. each table should now have counts. Now the data tables will need
editing again to change the ID's type from autonumber to just number.
5. when #4 is complete, each table will need an update query run to
get the date (in the table name) populated into the date field.
6. pick a table of your choosing, and you can now append all the
date-named tables into one! (When done, I'd rename the combined table
it to 'timedata' or whatever is appropriate)
7. edit the newly (renamed & combined table) and add another field,
called tsID <(t)ime(s)heet(ID)> or whatever ID you'd like and set the
type to autonumber. This will give an ID for each entry.
8. you can now RELATE the employees table to this 'timesheet' table:
use the relations window and drag the primary key of the employee's
table to the empID of the 'timesheet' table.
9. This will basically get all your data into access. I don't know how
many 'slot'-types that you have, but you can make a table for those
too so that there is one table to choose the 'slot' selections.
Yes, you could use some macros and/or code to assist in some of these
tasks, but I don't have the time to get into all that at the moment.
E-mail my address above directly, if you need more information or
clarification.
-Paul
po*******@yahoo.com.sg (poohnie08) wrote in message news:<8a**************************@posting.google. com>...
i have a excel spreadsheet showing staff name, date,work hour, ot
hour, slot1, slot2, slot3, slot4 and others). The "()" will keep
repeating from day 1 until end of month.
eg in excel spreadsheet,
================================================== ============================
A1 |A2 A3 A4 A5 A6 A7 A8 |A9
A10 A11
| 01/02/04 |02/02/04
StaffName |Work Hr OT Hr Slot1 Slot2 Slot3 Slot4 Others |Work Hr OT
Hr Slot1
================================================== =============================
Mary May 8 2 T109 T109 T105 WS WS 8 2
T108
Gary Smith 8 2 T111 T19 T155 T109 WS 8 2
T108
John Lenn 8 0 WS WS WS WS WS 8 0
WS
Mary May 8 3 T109 T109 T109 T109 WS 8 3
T108
...the excel columns will continue as long as until end of the month.
My question is, In ms access, how do i create such table with single
staff name with multiple date with time slot and import from excel ?
linda
