By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,251 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Combine two tabels to one. (Regular time and Overtime)

P: n/a
I have two tables with 5000 entries on them. One is based for regular time
with several variables example (employee name, date,time in and out, code,
customer, building) I have another table that works with overtime but has
the same feilds within the table. I want to be able to run a report that
combines the regular time and overtime for the warehouse and seperated by
each customer. I have tried join tables but I keep ending with, (if it
information on the overtime table, matches regular table to overtime
information will populate on all regular time lines for the match.) ANY
IDEAS? I am getting frustrated with it any I'm coming up on a deadline. (I
currently have the two seperate reports, but I cant get them two one.....)
HELP.HELP.HELP

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Create a UNION query to combine the data from 2 tables.

Access can't show UNION queries in design view, so you will need to work in
SQL View.

1. Create 2 identical queries - one into each table.

2. Switch them to SQL View (View menu).

3. Remove the semicolon at the end of the first query, and replace it with
the words:
UNION ALL

4. Copy the text of the 2nd query to clipboard (Ctrl+C) and paste it into
the first one after the UNION ALL.

You will end up with something that looks like this:
SELECT ID, EmployeeID, Hours, PayRate
FROM Table1
UNION ALL
SELECT ID, EmployeeID, Hours, PayRate
FROM Table2
ORDER BY EmployeeID;

Long term, it might be best to combine these 2 tables into one. Just add a
yes/no field called (say) IsOvertime so you can tell the difference.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jamie Pittman via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:dd******************************@AccessMonste r.com...
I have two tables with 5000 entries on them. One is based for regular time
with several variables example (employee name, date,time in and out, code,
customer, building) I have another table that works with overtime but has
the same feilds within the table. I want to be able to run a report that
combines the regular time and overtime for the warehouse and seperated by
each customer. I have tried join tables but I keep ending with, (if it
information on the overtime table, matches regular table to overtime
information will populate on all regular time lines for the match.) ANY
IDEAS? I am getting frustrated with it any I'm coming up on a deadline.
(I
currently have the two seperate reports, but I cant get them two one.....)
HELP.HELP.HELP

Nov 13 '05 #2

P: n/a
Jamie Pittman via AccessMonster.com wrote:
I have two tables with 5000 entries on them. One is based for regular time
with several variables example (employee name, date,time in and out, code,
customer, building) I have another table that works with overtime but has
the same feilds within the table. I want to be able to run a report that
combines the regular time and overtime for the warehouse and seperated by
each customer. I have tried join tables but I keep ending with, (if it
information on the overtime table, matches regular table to overtime
information will populate on all regular time lines for the match.) ANY
IDEAS? I am getting frustrated with it any I'm coming up on a deadline. (I
currently have the two seperate reports, but I cant get them two one.....)
HELP.HELP.HELP


There's a way to do this, but first you have to listen to me lecture. 8)

First of all, the two tables should absolutely positively NOT be two
tables. They should be one table as they deal with ONE database entity,
employee time.

Do you have the employee and customer names in the tables themselves?
Another bad mistake. These are two separate entities which should each
have their own table. Otherwise, you are going to find 50 to a 100
different spellings of a simple customer name like "K & D Pratt". No
kidding, I've dealt with a database as badly designed as the one you are
describing and that simple 11 character customer name had fifty four
(54) different and separate spellings.

The PK (autonumber) from the customer and employee table should be what
is in your main time/labour table. Queries between the three tables
will get you what you want.

Further, you can have another table which deals with time type and have
the PK (an autonumber) of that table referenced in the labour table as well.

OK, I've done my lecture. 8) Did you read and understand it? What,
you didn't read it? Go back to the top and start again!!!! 80

Now, how to deal with the abomination you've described? If the fields
(what you call "variables") are the same, look for help on "union
queries". This will combine the two tables together but not using the
joins you've described. Even if the fields are not quite the same, you
can still do what you want.

I know and understand the pucker factor you're working toward with
respect to your deadline. 8) If you still can't make sense of this,
write back to the group.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #3

P: n/a
I do understand to make them one table, but the issue I keep coming up with
is, when the overtime is entered I need to caculate that fied with a
seperate rate. If I use a (yes or no)box I then have trouble with null
values in the caculations. When I made the first database, I made drop
select list to use the customers, buildings, and employees, so I wouldn't
run into the problem with mis-spellings. What do you think I should
proceed with next?????

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #4

P: n/a
Jamie, the usual way to solve this is to record both the hourly rate and the
overtime rate for each employee. Then lookup these values at the time when
the record is added, and store the value (because one day the pay rates
might change).

This example assumes that there can only ever be 2 rates of pay for an
employee. (It needs another layer if there are other rates such as
time-and-a-half, double-time, etc.)

In your Employee table, store the $/hr the person earns for normal and for
overtime work. The Employee table will have these fields:
EmployeeID AutoNumber primary key
Surname Text
FirstName Text
HourlyRate Currency
OvertimeRate Currency

In the Wage table, store these fields:
WorkDate DateTime the date worked
EmployeeID Number foreign key to Employee.EmployeeID
IsOverTime Yes/No
HourlyRate Currency
Hours Number (Double) How many hours

You will use a combo for selecting employee.
It's RowSource property will be a query statement like this:
SELECT EmployeeID, HourlyRate, OvertimeRate
FROM Employee ORDER BY Surname, FirstName;

Now use the AfterUpdate event procedure of the combo so that it assigns the
pay rate:
Private Sub EmployeeID_AfterUpdate()
With Me.EmployeeID
If Not IsNull(.Value) Then
If Me.IsOverTime.Value Then
Me.HourlyRate = Me.Column(1)
Else
Me.HourlyRate = Me.Column(2)
End If
End With
End With
End Sub

In the AfterUpdate event of the IsOvertime check box, call the same code:
Private Sub IsOverTime_AfterUpdate()
Call EmployeeID_AfterUpdate
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jamie Pittman via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message
news:6f******************************@AccessMonste r.com...
I do understand to make them one table, but the issue I keep coming up with
is, when the overtime is entered I need to caculate that fied with a
seperate rate. If I use a (yes or no)box I then have trouble with null
values in the caculations. When I made the first database, I made drop
select list to use the customers, buildings, and employees, so I wouldn't
run into the problem with mis-spellings. What do you think I should
proceed with next?????

Nov 13 '05 #5

P: n/a
Allen Browne wrote:
Jamie, the usual way to solve this is to record both the hourly rate and the
overtime rate for each employee. Then lookup these values at the time when
the record is added, and store the value (because one day the pay rates
might change).


Or, if one's overtime rates are based on a multiplier for the particular
time type, have a single pay rate on the employee record and a time type
table with a multiplier.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.