appending information to existing record

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

Guest

I am trying to create a new table of all pay increase information for an
individual in one record so that I can find out how much each person made
during a specific time frame using the Between #[Date1]# and #[Date2], etc.
The original pay rate information has a separate record for each pay increase
date and amount for each individual. This is not something that can be
easily done individual by individual since I am working with over 5,000
individuals.

I have attempted to use an append query and an update query but each query
creates a new record for the data. Anyone have any other ideas?
 
Hi Elaine,

Am i reading your post correctly if you want to add records to a new table
combined of various fields from different tables? In that case you could use
a maketable query. If I misread please correct me.
 
Unfortunately, the information is all in one table, with each pay increase
having it’s own record.
EmpID EFFDate PayRate
5555 1/1/2004 $7.92
5555 6/1/2004 $8.44
5555 6/1/2005 $9.44
7777 4/3/2006 $10.00
7777 10/3/2006 $10.50
Etc, etc, etc…

My challenge is to convert them into one record per employee:
EmpID EFFDate Pay EFFDate2 Pay2 EFFDate3 Pay3 EFFDate4
Pay4
5555 1/1/2004 $7.92 6/1/2004 $8.44 6/1/2005 $9.44
7777 4/3/2006 $10.00 10/3/2006 $10.50
1234 9/21/2003 $6.81 2/21/2004 $7.81 2/21/2005 $9.31 2/21/2006
$10.81

I have already created the table to add them to, but any time I try an
APPEND query it adds one record per EFFDate so I end up with the same layout
as the first list. I keep getting the same results with an UPDATE query but
I have never used an UPDATE query before so it is quite possible I am not
putting something in correctly.

Thanks in advance for your help!


Maurice said:
Hi Elaine,

Am i reading your post correctly if you want to add records to a new table
combined of various fields from different tables? In that case you could use
a maketable query. If I misread please correct me.
--
Maurice Ausum


Elaine O said:
I am trying to create a new table of all pay increase information for an
individual in one record so that I can find out how much each person made
during a specific time frame using the Between #[Date1]# and #[Date2], etc.
The original pay rate information has a separate record for each pay increase
date and amount for each individual. This is not something that can be
easily done individual by individual since I am working with over 5,000
individuals.

I have attempted to use an append query and an update query but each query
creates a new record for the data. Anyone have any other ideas?
Click to expand...
Click to expand...
 
Why would you want it to be one record? You would have table maintenance
problems as time goes on as you have to keep adding columns as people stay
longer.

You could just stick this into the SQL view of the query window, and it
should do what you want it to do:

SELECT Sheet1.EmpID, Sheet1.EFFDate, Sheet1.PayRate
FROM Sheet1
WHERE (((Sheet1.EFFDate)>[Begin Date] And (Sheet1.EFFDate)<[End Date]));


**sheet 1 = the table of the sample payinfo you had above
- - -

If you are going to create a form out of the data, you could create a
one-to-many relationship, and pull it as below.

So, you have:

tbl_Employees

PK, Emp Name
5555, John Doe
7777, Jane Doe

tbl_PayInfo

PayID, Date, Dollar
1, 5555 1/1/2004 $7.92
2, 5555 6/1/2004 $8.44
3, 5555 6/1/2005 $9.44
4, 7777 4/3/2006 $10.00
5, 7777 10/3/2006 $10.50

tbl_Relate
5555, 1
5555, 2
5555, 3
7777, 4
7777, 5

So, by joining those relationships, you can use tbl_employee on the main
form, and tbl_pay as the subform, and so on and so forth.
 
BTW, my mistake,

You don't need the middle tbl_relate unless you are doing a many-to-many
relationship.
 
Hi Elaine,

To answer your original question:
I have attempted to use an append query and an update query but each query
creates a new record for the data. Anyone have any other ideas?
Click to expand...

Using an append query would add new records.

If you really want to make them consolidate into one record, you are going
to have to use an update query, and rank the paydates so you don't get them
out of order.
 
Hi Dan -

Thank you for your responses. I have been tied up with Help Desk issues
this afternoon so I have not been able to try out your idea. The information
I am working with is historical only and is being used for a lawsuit so that
no more pay raises will be added.

I will have to try out your suggestion tomorrow and I will let you know.
Again, thank you!!
 
Back
Top