Prev: IIf statement to show A, B, C
Next: Object or class does not support set of events
From: John W. Vinson on 23 Apr 2010 14:06
On Fri, 23 Apr 2010 10:39:02 -0700, dar <dar(a)discussions.microsoft.com> wrote:

>John i forgot the other fields
>
>Table: Employee
>LName
>FName
>Hire Date
>Office Staff 30 days for eligibilty from hire date
>Transport 30 days for eligibility from hire date
>Manager 30 days for eligibility from hire date
>Maint 30 days fro eligibilty from hire date
>Clerk 90 days for eligibility from hire date

This isn't making sense. Does each employee have a Office Staff field, a
Transport field, a Manager field??? What is contained in this field - a date?
If an employee is a clerk, why does she need a Transport field?

--

John W. Vinson [MVP]
From: dar on 23 Apr 2010 14:53
I have created a field labeled; HireDate30 and HireDate90 and would like to
calculate the eligibility date based off of the HireDate30 and HireDate90. I
think that would make is simpler than trying to calculate it off of the
titles, right?

Thank you

"John W. Vinson" wrote:

> On Fri, 16 Apr 2010 09:32:01 -0700, dar <dar(a)discussions.microsoft.com> wrote:
>
> >Yes, managers are 30 days from hire date, clerks are 90 days from hire date.
> >
>
> That's a business rule.
>
> It's not a database rule.
>
> I can see how you would enforce that in your office, but since you have chosen
> not to post any information about your tables, I cannot tell how you would
> enforce it in a database.
>
> I'd love to be able to help, but I can't unless you tell me what's in your
> database!
> --
>
> John W. Vinson [MVP]
> .
>
From: John W. Vinson on 23 Apr 2010 15:29
On Fri, 23 Apr 2010 11:53:01 -0700, dar <dar(a)discussions.microsoft.com> wrote:

>I have created a field labeled; HireDate30 and HireDate90 and would like to
>calculate the eligibility date based off of the HireDate30 and HireDate90. I
>think that would make is simpler than trying to calculate it off of the
>titles, right?

Again:

What is the datatype of hiredate30 and hiredate90?
Does every employee have a Hiredate30 and also a Hiredate90?
If so why?

I really think you may be misunderstanding how tables work. They're not
spreadsheets! A Table represents a particular type of Entity - real-life
person, thing or event. Each Field in the table contains the value of some
specific Attribute of that entity - the person's FirstName, their LastName,
their HireDate, their PositionID and so on. If you have several mutually
exclusive attributes (i.e. if someone has a Hiredate30 then their Hiredate90
must be blank), your table structure is wrong.

I don't understand your business model, but if you'll allow me to grope in the
dark with a possible idea... consider the following tables:

Employees
EmployeeID <autonumber primary key>
LName
FName
HireDate
PositionID

Positions
PositionID <autonumber primary key>
Position <e.g. Clerk, Manager, High Muckamuck>
DaysToEligibility

You could then create a query joining these two tables and calculate the
eligible date. The SQL view of the query (copy and paste it into a new query's
SQL view) would be

SELECT LName, FName, DateAdd("d", [DaysToEligibility], [HireDate]) AS
DateEligible
FROM Employees INNER JOIN Positions
ON Employees.PositionID = Positions.PositionID;

You can put criteria on the DateEligible calculated field if that's what
you're trying to do.
--

John W. Vinson [MVP]
From: dar on 23 Apr 2010 16:08
On my last post I changed it so that I would not have to create each title.
I have created the following feilds; HireDate30 and HireDate90

"John W. Vinson" wrote:

> On Fri, 23 Apr 2010 10:39:02 -0700, dar <dar(a)discussions.microsoft.com> wrote:
>
> >John i forgot the other fields
> >
> >Table: Employee
> >LName
> >FName
> >Hire Date
> >Office Staff 30 days for eligibilty from hire date
> >Transport 30 days for eligibility from hire date
> >Manager 30 days for eligibility from hire date
> >Maint 30 days fro eligibilty from hire date
> >Clerk 90 days for eligibility from hire date
>
> This isn't making sense. Does each employee have a Office Staff field, a
> Transport field, a Manager field??? What is contained in this field - a date?
> If an employee is a clerk, why does she need a Transport field?
>
> --
>
> John W. Vinson [MVP]
> .
>
From: dar on 23 Apr 2010 16:14
I have either an employee who will qualify in 30 and other in 90 days. When
the data entry person enters the information she will either enter their hire
date in the HireDate30 or the HireDate90. Based off of the Hire date info i
want it to calculate in the BenifitsEligibilityDate.

"John W. Vinson" wrote:

> On Fri, 23 Apr 2010 11:53:01 -0700, dar <dar(a)discussions.microsoft.com> wrote:
>
> >I have created a field labeled; HireDate30 and HireDate90 and would like to
> >calculate the eligibility date based off of the HireDate30 and HireDate90. I
> >think that would make is simpler than trying to calculate it off of the
> >titles, right?
>
> Again:
>
> What is the datatype of hiredate30 and hiredate90?
> Does every employee have a Hiredate30 and also a Hiredate90?
> If so why?
>
> I really think you may be misunderstanding how tables work. They're not
> spreadsheets! A Table represents a particular type of Entity - real-life
> person, thing or event. Each Field in the table contains the value of some
> specific Attribute of that entity - the person's FirstName, their LastName,
> their HireDate, their PositionID and so on. If you have several mutually
> exclusive attributes (i.e. if someone has a Hiredate30 then their Hiredate90
> must be blank), your table structure is wrong.
>
> I don't understand your business model, but if you'll allow me to grope in the
> dark with a possible idea... consider the following tables:
>
> Employees
> EmployeeID <autonumber primary key>
> LName
> FName
> HireDate
> PositionID
>
> Positions
> PositionID <autonumber primary key>
> Position <e.g. Clerk, Manager, High Muckamuck>
> DaysToEligibility
>
> You could then create a query joining these two tables and calculate the
> eligible date. The SQL view of the query (copy and paste it into a new query's
> SQL view) would be
>
> SELECT LName, FName, DateAdd("d", [DaysToEligibility], [HireDate]) AS
> DateEligible
> FROM Employees INNER JOIN Positions
> ON Employees.PositionID = Positions.PositionID;
>
> You can put criteria on the DateEligible calculated field if that's what
> you're trying to do.
> --
>
> John W. Vinson [MVP]
> .
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: IIf statement to show A, B, C
Next: Object or class does not support set of events