Use entire table as a record in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to get Access to behave in a way that is similar to Excel in
some respects... Currently, payroll is using excel to post weekly employee
hours. The current list of employees is posted with the hours they worked
for each day during the week on one worksheet, the next week's hours are
posted on the next worksheet and so on. To get an Access application to be
adopted by payroll, I need it to behave in the same way. I want to design a
form which pulls up the current list of emploees with the hours they worked
for that week as one record, with the next week's hours as the next record.
Currently, Access wants to treat each employee and their hours as individual
records. You have to scroll down the list of employees one at a time to find
the weekly hours you want to enter. Obviously, you can design a report that
organizes the data by employee and the weekly hours after they have been
entered, but I want a way to enter this information in a form all at once.
Does anyone know how to get Access to behave in this way?
 
you need minimum two tables:

tblEmployees
EmpID (primary key)
FirstName
LastName
<any other fields that describe an employee.>

tblEmpHours
HourID (pk)
EmpID (foreign key from tblEmployees)
WorkDate
WorkHours

the table design is standard and simple. to do the data entry efficiently in
forms, you can get about as creative as you need to be. exactly how you set
up the data entry form depends on how the user needs to be able to enter the
data. for instance, will the user enter all the hours for all employees for
Monday 2/20/2006, then for Tuesday, etc? or will the user enter all the
hours for one employee for each day of the week, Monday 2/20/2006 through
Friday 2/24/2006, then for the next employee, etc?

in either case above, you can add code to the data entry form to append
records for all employees for each date of the work-week at one time, and
then sort the records according to how the user needs to enter the data,
thus presenting the user with a group of "already existing" records that
s/he can quickly move through in order, entering just the hours - the
employee and date information is already present in the record.

hth
 
Thanks, Tina...

Right now I've got the tables behaving like I want by combining them in a
form in datasheet view. The whole week's worth of hours is displayed all at
once, adn they can be quickly inputted. I even managed to put a calendar pop
up for the date entries. The problem, as I see it, is that for each new
week, each employee must be specified in order to add the new hours. Is
there a way to get the entire roster to display at once for the new week?
 
Aha,

I didn't read your post carefully enough. Sorry. I'll concentrate my
efforts on looking for an example of just such and appending code. I
appreciate the response. Of course, my VBA skills stink. Maybe someone
could walk me through it? Hint, hint...

Jaybird
 
just create an Append query to append all the necessary records to
tblEmpHours. then run the query, from a macro or VBA. then requery the form
that's bound to tblEmpHours. you can set the OrderBy property of the form in
VBA. something along the lines of:

Dim db As DAO.Database, strSQL As String
Set db = CurrentDb
strSQL = db.QueryDefs("AppendQueryName").SQL
db.Execute strSQL, dbFailOnError
Me.Requery
Me.OrderBy = "SomeField, SomeOtherField"
Me.OrderByOn = True

hth
 
Muy facil, Tina! Gracias! However, as you'd expect, the update query wants
to update all of the records in the table, not just the ones from the latest
week. I'm thinking of several solutions. Tell me which sounds more
promising...

1) Set the critera of the append query to soemthing that will limit the
records it updates... Can't think of one that will work right now.
2) Instead of appending the same table, how about updating to a temp table,
and appending to the original table each time the action is run. That way,
only the records from the previous week are modified and added to the
original table.
3) Suggestions I haven't thought of?

Thanks again...

Jaybird
 
Silly me! Of course, using the Make Table Query is going to produce similar
results... It's still referring to the original table for those dates. I
guess I'm going to have to refresh the criteria for the temp table with the
new dates and append that. Currently, I'm thinking of creating a form with a
calendar add in to select the new dates and using that. I'll let you know
how it turns out!

Jaybird
 
Well, actually, it can't be a temp table referring to the original Employee
Hours table because I've appended that to include new dates... Duh! But if
it's a permanent table with dates updateable by a form (with a calendar
control) that refers to the original roster of employees, I think that gets
me close to where I want to be. Wish me luck.

Jaybird
 
good luck :)


Jaybird said:
Well, actually, it can't be a temp table referring to the original Employee
Hours table because I've appended that to include new dates... Duh! But if
it's a permanent table with dates updateable by a form (with a calendar
control) that refers to the original roster of employees, I think that gets
me close to where I want to be. Wish me luck.

Jaybird
Click to expand...
 
For those of you searching through these postings to find the answers to
similar questions, I'll just tell you what I've found out subsequently...
Best way to make this happen is to set up your employee data on one table and
your hourly data on another. Make sure that your hourly data is identified
by employee ID and by date. In your table, use the long form of the date.
You may want to use some aspect of it in the future that you can't predict
now. Tie these two tables together in a query that includes all records from
your employees table and only those records from the hours table where the
joined fields are equal. Don't worry about the result of this query. Save
this query and and use it to create a crosstab query using the crosstab query
wizard. Follow the instructions. Set your interval as daily. To observe
one week at a time, you'll need to set some parameters. In order to ensure
that the columns are in the sequence you desire, you'll need to do some other
things. In order to make your column headings appear as "Monday",
"Tuesday",... etc instead of 3/27/06, 3/28/06,... set the resulting
expression in your column heading, to "expr1: Format([daily_hrs],"dddd").
I'm still working on this, so if you want updates on how to do this, I should
be able to give them to you in the future. Likely as not, nobody will read
this. Sigh... At least I learned something.

Jaybird
 
I read it : )

But I didn't understand it : (

Only joking... I did read it and I think I understand what you're trying to
do.
It's difficult without being able to see the DB...
 
Back
Top