Calculating fields for Access

  • Thread starter Thread starter Galin
  • Start date Start date
G

Galin

Hi all,
I am trying to calculate two fields. I have six fields in
query window...

ID, PAY_CODE_1, PAY_CODE_1HOUR, PAY_CODE_2, PAY_CODE_2HOUR

PAY_CODE_1 and PAY_CODE_2 include pay codes such as
REG,OVT,SICK,DBL...ets..

I am trying to SUM the pay codes based on hours. Just to
make it clear I need PAY_CODE_1 summarized by
PAY_CODE_1HOURS and PAY_CODE_2 by PAY_CODE_2HOURS.

I group by PAY_CODE_1 and I use SUM in the Total field for
PAY_CODE_1_HOUR. If I run the query I receive the total
hours.

Now the problem....
There is no problem when I execute the queries
independently. When I try to run one query containing
PAY_CODE_1 and PAY_CODE_2 I don't receive the same results
because in my opinion Access confuses the fields having
some indentical codes(such as REG and OVT on both fields).

I need to have all codes summarized by hours, no matter in
one or two queries. Since I get the results by running two
separate queries is there a way (or code) to add up the
matching fields from both queries plus the ones that are
not matching to have one complete field with all total
codes which later be execute in a report. Or a way to
differentiate the fields so Access can run the SUM..

Sorry for the long post but I can't figure this out..
thank you

galin
 
Well, it looks like your database design is a little off.
Instead of having a Pay_Code_1 and Pay_Code_2 field, you
should just have a Pay_Code field, and then have 2 records
for two entrys.


But, to answer your question, we can use a Union Query to
change the data to a more normalized format:

Select ID,Pay_Code_1 as Pay_Code, Pay_Code_1Hour as
PAY_Code_Hour From TableName
UNION
Select ID,Pay_Code_2 as Pay_Code, Pay_Code_2Hour as
PAY_Code_Hour From TableName


Save that query, and now use that the basis of your SUM
query.


Chris Nebinger
 
Thanks Chris,

I am aware now that my database is a little off. Probably
this is the reason is not behaving the way I want.
I am followint your advice about having one field for
Pay_Code. Can you explain what exactly you mean by saying
two records for two entrys? How can I make Access sum up
the corresponding codes to hours and then sum up the
matching fields...
Thank you
 
Lets assume you have this table:
Table: tblPayCode
ID
PayCode
PayHours


Now, you could enter data:
ID PayCode PayHours
1 REG 5
2 REG 3
3 OVT 1
4 SCK 8
5 SCK 8
6 VAC 8
7 REG 8


Now, in a query,

Select PayCode,Sum(PayHours) As TotalHours
From tblPayCode
Group By PayCode

What you will see is:
REG 16
OVT 1
SCK 16
VAC 8




What I meant about the 2 entries was that you had
Pay_Code1 and Pay_Code2. This would be split into

ID Code
1 REG
2 OVT

Let me know if you need more assistance.


Chris Nebinger
 
Yes, now I know what mean..but..
My PAYCODE_1 corresponds only with PAYCODE_1HOURS. These
are hours representing entire working day(such as 8 hours).
PAYCODE_2 is only for OVT,DBL and somehow REg and is
assigned only between 1-2 hours. If I merge both paycode
fields I am not going to know which pay code what hours
calculates. That's why I wanted to run SUM only on
PAYCODE_1 by PAYCODE_1HOURS , the same with PAYCODE_2 by
PAYCODE_2HOUR and then somehow adding these two fields
(with matching codes) and getting comlete SUM on every
single pay code.

I hope I am clear. I followed your advice but I lost hours
when I run the totals..I assumed after creating the Union
query I had to create a table and then creata aquery and
run SUM
Thanks
galin
 
I think I understand, sort of.....

You are going to need two different queries to sum
PAYCODE_1 and PAYCODE_2. They need to be summed
seperately, then combined in a third query.


Chris Nebinger
 
Hi,
Look in query designer - SQL view - Union to see how to do it easily. The
only criteria is the fields need the same names so in each query you need
rename the fields. Where the field name is edit it to say for instance in
the first query PayCode: Paycode_1 and the in the second query PayCode:
Paycode_2

HTH
Marc
 
Back
Top