Hi all, (please excuse the crosspost as I'm trying to reach as many people
as possible)
I am somewhat familiar with Access 2000, but my latest project has me
stumped. So, I defer to you experts.
I've been asked to create a Daily Log sheet to be distributed to some of our
clerks.
For each day, the clerk is to log tasks worked on for the day, (i.e worked
on the johnson account).
The clerk may work on a particular task for multiple days.
So far pretty easy, here's the kicker:
We want to create a query which reports the tasks worked on for that month.
So, if the clerk worked on the johnson account for 6 days, and the smith
account for 4 days
the query should only show on instance of johnson and one instance of smith.
Here's what I got so far:
3 Tables:
Daily Tasks
Weekdays
FedHolidays
Layout of Daily Tasks (Sorry if these columns don't line up evenly)
Task ID Date WeekdayID FedHolidayID Task1 Task2
Task3 Task4
1 1/1/2004 Thursday New Years
2 1/2/2004 Friday
Johnson Smith Filing Bush
3 1/3/2004 Saturday
Clinton
4 1/4/2004 Sunday
5 1/5/2004 Monday
Meeting Reports Smith Johnson
~
19 1/19/2004 Monday MLK Jr Day Bush
The WeekdayID and FedHolidayID are lookup values.
Question 1:
How can I create a query that will list the following tasks in one list, for
January:
Johnson
Smith
Filing
Bush
Clinton
Meeting
Reports
Question 2:
I want to count the number of weekends worked (i.e. Sat 1/2/04 & Mon
1/19/2004)
Question 3:
How would you handle sick days?
Question 4:
Because this project is still in the design phase, any design input would be
appreciated.
Don't really know how to use vba, unless someone can write the code for me,
I'd like to use the "design view" for this.
Thanx and regards,
"T" 2 2408
Dear User:
The table design from which you started isn't adequate. If you want
to be able to query tables effectively and efficiently, they must
first be normalized. When you show columns like 'Task 1', 'Task 2',
etc. this is a pretty clear sign that it is not normalized.
I would suggest something like this:
ClerkTask
TaskID
Clerk
TaskDateTimeStart
TaskDateTimeEnd
Make a separate table of Holidays, just recording the date and holiday
name.
If you need to number the tasks in the day as Task 1, Task 2, etc.
then add a TaskNumber to the ClerkTask table above. However, you'll
always be able to assign a number, ranking these by TimeStart and get
such a number.
Your report will just need to filter the above for a period of time
and then you can report DISTINCT to omit duplicates. Also, you could
aggregate the hours over Task and Clerk and you'll get hours worked
without duplicating Task or Clerk. The query results you want are
VERY EASY if the tables are designed correctly first.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Thu, 30 Oct 2003 15:39:44 -0500, "Test User" <te**@user.com> wrote: Hi all, (please excuse the crosspost as I'm trying to reach as many people as possible)
I am somewhat familiar with Access 2000, but my latest project has me stumped. So, I defer to you experts. I've been asked to create a Daily Log sheet to be distributed to some of our clerks.
For each day, the clerk is to log tasks worked on for the day, (i.e worked on the johnson account). The clerk may work on a particular task for multiple days.
So far pretty easy, here's the kicker:
We want to create a query which reports the tasks worked on for that month. So, if the clerk worked on the johnson account for 6 days, and the smith account for 4 days the query should only show on instance of johnson and one instance of smith.
Here's what I got so far:
3 Tables: Daily Tasks Weekdays FedHolidays
Layout of Daily Tasks (Sorry if these columns don't line up evenly)
Task ID Date WeekdayID FedHolidayID Task1 Task2 Task3 Task4 1 1/1/2004 Thursday New Years 2 1/2/2004 Friday Johnson Smith Filing Bush 3 1/3/2004 Saturday Clinton 4 1/4/2004 Sunday 5 1/5/2004 Monday Meeting Reports Smith Johnson ~ 19 1/19/2004 Monday MLK Jr Day Bush
The WeekdayID and FedHolidayID are lookup values.
Question 1: How can I create a query that will list the following tasks in one list, for January:
Johnson Smith Filing Bush Clinton Meeting Reports
Question 2: I want to count the number of weekends worked (i.e. Sat 1/2/04 & Mon 1/19/2004)
Question 3: How would you handle sick days?
Question 4: Because this project is still in the design phase, any design input would be appreciated.
Don't really know how to use vba, unless someone can write the code for me, I'd like to use the "design view" for this.
Thanx and regards, "T"
Tom,
Thanx for you suggestions, I've dropped the task1, task2....columns and
replaced them with taskid
Thanx,
"T"
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:bc********************************@4ax.com... Dear User:
The table design from which you started isn't adequate. If you want to be able to query tables effectively and efficiently, they must first be normalized. When you show columns like 'Task 1', 'Task 2', etc. this is a pretty clear sign that it is not normalized.
I would suggest something like this:
ClerkTask TaskID Clerk TaskDateTimeStart TaskDateTimeEnd
Make a separate table of Holidays, just recording the date and holiday name.
If you need to number the tasks in the day as Task 1, Task 2, etc. then add a TaskNumber to the ClerkTask table above. However, you'll always be able to assign a number, ranking these by TimeStart and get such a number.
Your report will just need to filter the above for a period of time and then you can report DISTINCT to omit duplicates. Also, you could aggregate the hours over Task and Clerk and you'll get hours worked without duplicating Task or Clerk. The query results you want are VERY EASY if the tables are designed correctly first.
Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts
On Thu, 30 Oct 2003 15:39:44 -0500, "Test User" <te**@user.com> wrote:
Hi all, (please excuse the crosspost as I'm trying to reach as many
peopleas possible)
I am somewhat familiar with Access 2000, but my latest project has me stumped. So, I defer to you experts. I've been asked to create a Daily Log sheet to be distributed to some of
ourclerks.
For each day, the clerk is to log tasks worked on for the day, (i.e
workedon the johnson account). The clerk may work on a particular task for multiple days.
So far pretty easy, here's the kicker:
We want to create a query which reports the tasks worked on for that
month.So, if the clerk worked on the johnson account for 6 days, and the smith account for 4 days the query should only show on instance of johnson and one instance of
smith. Here's what I got so far:
3 Tables: Daily Tasks Weekdays FedHolidays
Layout of Daily Tasks (Sorry if these columns don't line up evenly)
Task ID Date WeekdayID FedHolidayID Task1
Task2Task3 Task4 1 1/1/2004 Thursday New Years 2 1/2/2004 Friday Johnson Smith Filing Bush 3 1/3/2004 Saturday Clinton 4 1/4/2004 Sunday 5 1/5/2004 Monday Meeting Reports Smith Johnson ~ 19 1/19/2004 Monday MLK Jr Day Bush
The WeekdayID and FedHolidayID are lookup values.
Question 1: How can I create a query that will list the following tasks in one list,
forJanuary:
Johnson Smith Filing Bush Clinton Meeting Reports
Question 2: I want to count the number of weekends worked (i.e. Sat 1/2/04 & Mon 1/19/2004)
Question 3: How would you handle sick days?
Question 4: Because this project is still in the design phase, any design input would
beappreciated.
Don't really know how to use vba, unless someone can write the code for
me,I'd like to use the "design view" for this.
Thanx and regards, "T" This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Don Vaillancourt |
last post by:
Hello all,
Over the years as I design more database schemas the more I come up with
patterns in database design. The more patterns I recognize the more I
want to try to design some kind of...
|
by: sk |
last post by:
I have an applicaton in which I collect data for different parameters
for a set of devices. The data are entered into a single table, each
set of name, value pairs time-stamped and associated with...
|
by: rodchar |
last post by:
Hey all,
I'm trying to understand Master/Detail concepts in VB.NET. If I do a data
adapter fill for both customer and orders from Northwind where should that
dataset live?
What client is...
|
by: tshad |
last post by:
Many (if not most) have said that code-behind is best if working in teams -
which does seem logical.
How do you deal with the flow of the work?
I have someone who is good at designing, but...
|
by: roN |
last post by:
Hi,
I'm creating a Website with divs and i do have some troubles, to make it
looking the same way in Firefox and IE (tested with IE7). I checked it with
the e3c validator and it says: "
This...
|
by: JoeC |
last post by:
I have a question about designing objects and programming. What is the
best way to design objects? Create objects debug them and later if you
need some new features just use inhereitance. Often...
|
by: |
last post by:
I have a question about spawning and displaying subordinate list controls
within a list control. I'm also interested in feedback about the design of
my search application. Lots of code is at the...
|
by: neelsmail |
last post by:
Hi,
I have been working on C++ for some time now, and I think I have a
flair for design (which just might be only my imagination over-
stretched.. :) ). So, I tried to find a design...
|
by: indrawati.yahya |
last post by:
In a recent job interview, the interviewer asked me how I'd design
classes for the following problem: let's consider a hypothetical
firewall, which filters network packets by either IP address,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |