Date query

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

Guest

I'm trying to run a query for a table named employees. In this table are 6
different columns that contain dates (01/01/2005). I need to run a query
that will pull from all 6 columns, only the dates from 2004 OR 2005. How do
I do this?

Thanks in advance!
 
6 Fields that contain date value in 1 table. I'm pulling this info from an
ODBC db. These fields represent a pay increase. 6 years is as far as the
table goes back to. So if someone has only had 2 pay increases, then they
would only occupy the first 2 fields. If someone has had 5 pay increases,
then they would only occupy 5 fields & etc... I'm trying to compair the last
2 years (2004 & 2005) pay increases.

Does that make sense?
 
Naiveprogrammer,

Are you abel to import this data into a local table, so it can be
re-structured? You would need to run a series of Append Queries, for
each of the 6 fields in the linked table, in order to get the data more
normalised, which will then make it easier to do your comparisons. It
is difficult to be specific without more details of your data, and what
you are exactly trying to achieve, but I would imagine you might want
all the pay increase data in just one field.
 
I'm trying to run a query for a table named employees. In this table are 6
different columns that contain dates (01/01/2005). I need to run a query
that will pull from all 6 columns, only the dates from 2004 OR 2005. How do
I do this?

Thanks in advance!
Click to expand...

Put a criterion under each field of
= #1/1/2004# AND < #1/1/2006#
Click to expand...

Put these criteria on SEPARATE lines of the query grid to use OR
logic.

The need to do this in six fields makes me queasy about the structure
of your tables - might this actually be a one (employee) to many
(date) relationship?

John W. Vinson[MVP]
 
Thanks for the help! I'm still trying to figure out how exactly to normalize
this db however. The end result is to get all pay raises for last year &
current year for all employees. The problem is field 4 may be the pay raise
for employee A34 since she has been there for 4 years. Yet for employee A32
field 2 is their current pay raise since the employee has been there for only
2 years. Does that make sense?

Thanks!
 
Thanks for the help! I'm still trying to figure out how exactly to normalize
this db however. The end result is to get all pay raises for last year &
current year for all employees. The problem is field 4 may be the pay raise
for employee A34 since she has been there for 4 years. Yet for employee A32
field 2 is their current pay raise since the employee has been there for only
2 years. Does that make sense?
Click to expand...

It makes sense in a business context - but it emphatically does NOT in
a database context.

"Fields are expensive, records are cheap" is an old saying, I wish I
knew whose so I could thank them! You should *not* store multiple
raises in the same record, in fact you should not store them in the
employee table AT ALL.

Instead, you should have TWO tables, in a one to many relationship: an
Employees table, and a PayrollChanges table. The PayrollChanges table
would have (at least) three fields - the employeeID as a link to the
Employees table; the ChangeDate; and the new Salary (you might also
have fields for comments or for the performance review result).

This would let you keep a complete salary adjustment history for each
employee; you could then easily use a query with a date range such as
= DateAdd("yyyy", -2, Date())
Click to expand...

to get all the raises in the past two years.

John W. Vinson[MVP]
 
That makes a lot of sense & is a lot of help as well! Is there an easy way
(i.e. query) to turn fields into records by chance? I'm pulling this info
from an ODBC & there is a LOT of employees.

Thanks!
 
That makes a lot of sense & is a lot of help as well! Is there an easy way
(i.e. query) to turn fields into records by chance? I'm pulling this info
from an ODBC & there is a LOT of employees.
Click to expand...

A "Normalizing Union Query" can do this. Without knowing your
fieldnames this is a WAG but try something like this, in the SQL
window of a new query, using your own table and fieldnames of course:

SELECT EmployeeID, Raise1Date AS TransactionDate, Raise1Amount AS
TransactionAmount
FROM Yourtable
WHERE Raise1Amount IS NOT NULL
UNION ALL
SELECT EmployeeID, Raise2Date AS TransactionDate, Raise2Amount AS
TransactionAmount
FROM Yourtable
WHERE Raise2Amount IS NOT NULL
UNION ALL
SELECT EmployeeID, Raise3Date AS TransactionDate, Raise3Amount AS
TransactionAmount
FROM Yourtable
WHERE Raise3Amount IS NOT NULL
UNION ALL
<etc etc>

Save this query and then base an Append query on it.

John W. Vinson[MVP]
 
John, if i could slip in a quick question on the side here: what does WAG
stand for? tia, tina
 
Back
Top