473,224 Members | 1,345 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

how to create a table with 1 staffid wtih multiple time slots daily until end of month ?

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
Nov 12 '05 #1
1 3316
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

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: J West | last post by:
Warning: Error while executing this query:CREATE TABLE "purchaseorder" ( "PurchaseOrderID" int(10) unsigned NOT NULL auto_increment, "PurchaseCost" double unsigned zerofill NOT NULL default...
8
by: rong.guo | last post by:
Greetings! I am now doing one type of analysis every month, and wanted to creat table names in a more efficient way. Here is what happens now, everytime I do the analysis, I will create a...
9
by: expect | last post by:
Hello, Trying to get this MySql create table command to work, no luck. create sequence serial; CREATE TABLE outbound ( source char(100) default '', destination char(100) default '', sport...
2
by: db2group88 | last post by:
hi, we install db2 udb v8.1 on windows 64bit, in our application, we have sql execute "create table .... not logged initially", but from the operating system event viewer showing error stating that...
1
by: xcellent | last post by:
Hi Need some help with appointment calendar Whats the best method of displaying time slots and linking data to a particular time slot (Ms Access) Thanks
24
by: Dan2kx | last post by:
Hello to all that read and thank you to all that post Background: I have been tasked to create a holiday database for the employees in my lab, (so expect many more posts) im stuck at the first...
4
by: arnaudk | last post by:
I am trying to come up with a class design to deal with asynchronous data to be stored and analyzed over multiple time frames and could really use some design input. This is a rather long question...
0
by: Steve | last post by:
The approach in this thread looks wrong to me considering your example. Your example shows you have random time periods between 6AM and 6 PM for appointments. Appointment 1 is 1-1/2 hour,...
2
by: zufie | last post by:
I am learning SQL using MS Access until I get SQL Server. I am trying to create a table that includes time and date columns. I receive the error: Syntax error in field definition. Here is my...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.