relations many to many (?)

[realspido]

I need to generate a database for time sheets which I have to prepare weekly
I need to have for each record containing information about "week ending
date", sub list with employees. I tried to make relation one-to-many between
two tables (one with employees, second with dates) but it's not what I need.
So what to do to have same list of employees in each "date record"?
Form would look like:

Date: .......
Name:......

day area hours
Mon area1 ...
Mon area2 ...
Tue area3 ...
Tue area4 ...
....
....
And same for next employees
The problem is I want to make it simple for people who will make time
sheets, so I need to have a list of all employees for every week ending date,
so you wouldn't have to type 50 names every week (same names)
Another problem is that every day is splitet on two areas one employee
works, and there must be 7 week days on one form to make weekly reports.
I hope it's clear what I'm trying to explain.
Please for help.
I've done it in excel but it takes too much memory and after few weeks is
too slow...
Thank you

 

[Tim Ferguson]

I need to generate a database for time sheets which I have to prepare
weekly I need to have for each record containing information about
"week ending date", sub list with employees. I tried to make relation
one-to-many between two tables (one with employees, second with dates)

From this description, it sounds as though you have entities like this:

Employees( EmpNo, FName, LName, etc etc)
primary key EmpNo

HoursWorked( EmpNo, AreaCode, WorkDate, HoursCompleted, etc)
primary key (EmpNo, AreaCode, WorkDate)
foreign key EmpNo references Employees

It would be simple to create a query to list each employee record over a
week:

SELECT Employees.EmpNo,
LName,
FName,
DayName(WorkDate) AS DayOfWeek,
AreaCode,
HoursCompleted

FROM Employees LEFT JOIN HoursWorked
ON Employees.EmpNo = HoursWorked.EmpNo

WHERE WorkDate >= #2005-11-07#
AND WorkDate < #2005-11-14#

ORDER BY Employees.EmpNo, WorkDate, AreaCode

You can make a report around this query, with suitable headers and
footers to add up the totals etc. Job done..

day area hours
Mon area1 ...
Mon area2 ...
Tue area3 ...
Tue area4 ...
...
...

The problem is I want to make it simple for people who will make time
sheets,

Designing a good UI is a speciality in itself. You can make a simple one,
which may be a poor match for your users, with Access UI objects alone;
or you can make a sophisticated one that your users will find easy using
extra coding and tools. Up to you....

I've done it in excel

Just what I was about to suggest, actually!

but it takes too much memory and after few weeks is too slow...

Don't really understand this bit. You have to make a decision about how
much historical information you need, and how you want to store it. There
is no reason your Excel spreadsheet shouldn't store its old data in a mdb
database. What reporting tools do you need, for example?

Hope that helps


Tim F

 

[realspido]

Thanks, but it's not really what I wanted to get;(
The main problem is to make a form, where you'd put new "week ending date"
and you'd have sub form where all employee's names would be appearing
automatically.
Imagine a bookwith many chapters. Each chapter looks actually same: contains
pages with same names on its headers, the onlyone difference is the text on
it.
Now: chapter is a "week ending date", pages are employees, and on each page
is a table which is a time sheet for whole week.
Now if I'll add new chapter (new date), so I want to have all pages printed
exactly like in previous chapters: same names, same tables. And all you have
to do is fill in the table on each page.
So for me it looks like a form (date) with subform (empl) and this subform
contains another subform (time sheet). Am I correct?
Now my question is how to get all chapters with its pages printed
automatically?
Please help, I'm desperate...

 

[Tim Ferguson]

The main problem is to make a form, where you'd put new "week ending
date" and you'd have sub form where all employee's names would be
appearing automatically.

I am not really sure what you are asking for. You have an idea of your
table design (this is, after all, a a TablesDBDesign group), and an idea
of how you want your user interface to go. What is it that is going
wrong?

My encouragement to go back to Excel was not flippant: it's my first
instinct for anything that looks like row-and-column entry. You can use
the built-in Data Validation rules. You can store the data in a mdb
database, so that you can use Access to produce summary reports etc etc
and manage the security. You will need a little bit of VBA to run it, but
you are going to need that anyway whether in Access or any another front
end.

There is no reason you can't create an unbound form in Access, with some
combo boxes etc for selecting the Employee and the WeekNumber, and then a
large number of labels and text boxes to collect the actual working
hours. You would need to manage the reading and writing from the tables,
but it's not too hard. How good are your coding skills? As indicated
earlier, solutions in Access can be sophisticated for the developer and
helpful for the user, or simple for the developer and harder for the
user.

Hope that helps


Tim F

 

[John Vinson]

Thanks, but it's not really what I wanted to get;(
The main problem is to make a form, where you'd put new "week ending date"
and you'd have sub form where all employee's names would be appearing
automatically.
Imagine a bookwith many chapters. Each chapter looks actually same: contains
pages with same names on its headers, the onlyone difference is the text on
it.
Now: chapter is a "week ending date", pages are employees, and on each page
is a table which is a time sheet for whole week.
Now if I'll add new chapter (new date), so I want to have all pages printed
exactly like in previous chapters: same names, same tables. And all you have
to do is fill in the table on each page.
So for me it looks like a form (date) with subform (empl) and this subform
contains another subform (time sheet). Am I correct?
Now my question is how to get all chapters with its pages printed
automatically?
Please help, I'm desperate...

You're apparently misunderstanding how relational databases work.

A database is not a Document. Forms and reports are not at all like
"chapters", and are not stored anywhere.

You should have a table - *one* table - of employees, with each
employee occupying one record. This table would be the ONLY place
where the employee's name and biographical data is stored.

You would have another table of timesheet data. This would have fields
for the employeeID (as a link to the Employee table), the date, and
fields for hours worked and so on.

You might (or might not need) a table of workdates.

You can use Forms to *pull together the existing data* in these tables
for display. For example, you might have a Form based on the Workdates
table - or just with a textbox into which you can enter the date - and
a Subform based on the timesheet table. This subform would have
controls which display the employee name (perhaps a combo box) but
this display would be drawn from the Employees table.

You can also create a Report, for printing to paper, in just the same
way. The Report would be based - not on the form - but on a Query
pulling data from the tables, assembling it, laying it out on the page
and printing it. The query would have criteria limiting which "pages"
you want to print, say by date range, or by department, or both, or
whatever is required.

John W. Vinson[MVP]