Tabe Date Range Help?

A

aldunford

I need to set up a table that has a range for Dates. How would I do this
without entering a transaction for each year?
Example

Field Name is Time in Service
Range needs to be
0 - 3
3 - 6
6 - 9
9 - 14
14 - 999
 
V

vbasean

I need to set up a table that has a range for Dates. How would I do this
without entering a transaction for each year?
Example

Field Name is Time in Service
Range needs to be
0 - 3
3 - 6
6 - 9
9 - 14
14 - 999
Click to expand...

Would this work?
a field that's text and it's row source
"0-3","3 - 6","6 - 9","9 - 14","14 - 999"
 
A

aldunford

Thanks I set up the table as shown. However I really can't understand how he
explains the query.

This is what I need it to do. I want the query to look at the employees
Service Years and based on that Return the Rate using the Range

From To Rate
0 2.99 7.5
3 5.99 9.5

So If I am at 4 years it will return 9.5
 
A

Allen Browne

Tom is using subqueries.
Here's an introduction to that topic:
http://allenbrowne.com/subquery-01.html

It's worth the effort. Once you get the query with the subquery showing what
you need, you can use it in another query where the number of years is
between the 2 values, or use DLookup() in contexts other than a query.
 
D

Dale Fye

Not knowing your table structure, I'll take a stab.

SELECT tblEmployees.*, tblServiceRanges.Rate
FROM tblEmployees, tblServiceRanges
WHERE DATEDIFF("yyyy", tblEmployees.StartDate, Date())
= tblServiceRanges.From
Click to expand...
AND DATEDIFF("yyyy", tblEmployees.StartDate, Date())
< tblServiceRanges.To

With this structure you can change your FROM/To pairs to (0,3), (3,6)...,
like you have in your original post.

This query (no JOIN clause) is a Cartesian JOIN, with means that without the
WHERE clause, it would include each record from tblEmployees matched up with
each record from tblServiceRanges. By adding the WHERE clause, you restrict
the result set to so that each record from tblEmployees will only match up
with one of the records in tblServiceRanges.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
A

aldunford

I think what you wrote would work so I tried it using my structure

SELECT [tblEmp Hire Info].*, tblNEAccruals.Rate
FROM [tblEmp Hire Info], tblNEAccruals
WHERE DATEDIFF("yyyy", [tblEmp Hire Info].Isac Original Hire Date, Date())
=tblNEAccruals.From
Click to expand...
AND DATEDIFF("yyyy", [tblEmp Hire Info].Isac Original Hire Date, Date())
<tblNEAccruals.To


However when I run it I get this Syntax error (missing operator) in query
expression 'DATEDIFF("yyyy",[tblEmp Hire Info].Isac Original Hire Date,
Date()))>=tblNEAccruals.From AND DateDiff("yyyy",[tblEmp Hire Info].Isac
Original Hire Date, Date())<tblNEAccruals.To'.


What am I missing or doing wrong?
 
A

aldunford

I've been playing with it and I tried the sql again. This is what I have.

SELECT [tblEmp Hire Info].*, tblNEAccruals.[Monthly Rate]
FROM [tblEmp Hire Info], tblNEAccruals
WHERE (((DateDiff("yyyy",[tblEmp Hire Info].[Isac Original Hire
Date],Date()))>=[tblNEAccruals].[From] And (DateDiff("yyyy",[tblEmp Hire
Info].[Isac Original Hire Date],Date()))<[tblNEAccruals].[To]));

Thanks A million!!!!
 
J

John W. Vinson

Thanks I set up the table as shown. However I really can't understand how he
explains the query.

This is what I need it to do. I want the query to look at the employees
Service Years and based on that Return the Rate using the Range

From To Rate
0 2.99 7.5
3 5.99 9.5

So If I am at 4 years it will return 9.5
Click to expand...

You can include the range table in your query with *no* join line; put a
criterion on From of

<= [Service Years]

and a criterion on To lf
= [Service Years]
Click to expand...

If (as you should be!) you're calculating the service years with an expression
on the service start date, use that calculation in the criterion.
 
A

aldunford

This is what I used. Where would I place your critiera? I'm Trying to find
the accrual rate based off of Service Years & Hours. At this time I using
Org. Hire Date to find this. I need to actual use Service Years & Hours ie.
12yrs + 150hrs to find it but I don't know how to go about that as once the
hours hit 1957.50 it needs to add a year ie. 12 yrs 1957.50 then becomes 13
yrs and each month there is an accrual added to it of Workdays in a month *
7.5 (haven't figured out that yet)

SELECT [tblEmp Hire Info].*, tblNEAccruals.[Monthly Rate], [tblEmployee
Demographic Info].[Current Employee], [tblEmp Hire Info].[Hiring Status]
FROM tblNEAccruals, [tblEmployee Demographic Info] INNER JOIN [tblEmp Hire
Info] ON [tblEmployee Demographic Info].[SS# ID] = [tblEmp Hire Info].[SS# ID]
WHERE ((([tblEmployee Demographic Info].[Current Employee])=Yes) AND
(([tblEmp Hire Info].[Hiring Status])="NE") AND ((DateDiff("yyyy",[tblEmp
Hire Info].[Isac Original Hire Date],Date()))>=[tblNEAccruals].[From] And
(DateDiff("yyyy",[tblEmp Hire Info].[Isac Original Hire
Date],Date()))<[tblNEAccruals].[To]));

John W. Vinson said:
Thanks I set up the table as shown. However I really can't understand how he
explains the query.

This is what I need it to do. I want the query to look at the employees
Service Years and based on that Return the Rate using the Range

From To Rate
0 2.99 7.5
3 5.99 9.5

So If I am at 4 years it will return 9.5
Click to expand...

You can include the range table in your query with *no* join line; put a
criterion on From of

<= [Service Years]

and a criterion on To lf
= [Service Years]
Click to expand...

If (as you should be!) you're calculating the service years with an expression
on the service start date, use that calculation in the criterion.
Click to expand...
 
A

aldunford

Just when you think you have the home run you get throw a curve ball!

Okay this works great if considering by YEAR. However I need it to look at
the month too. So If I have an employee that wouldn't hit 6 years until
October/2008 I want him to show 9.5 until October then 11.5 in October
 
Top