Calculating fields for Access

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




-----Original Message-----
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
.

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


-----Original Message-----
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




-----Original Message-----
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
.

.

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



-----Original Message-----
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


-----Original Message-----
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




-----Original Message-----
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
.

.

.

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

-----Original Message-----
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



-----Original Message-----
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


-----Original Message-----
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




-----Original Message-----
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
.

.

.

.

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


-----Original Message-----
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

-----Original Message-----
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



-----Original Message-----
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


-----Original Message-----
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




-----Original Message-----
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
.

.

.

.

.

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
"Chris Nebinger" wrote in message
...
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


0 -----Original Message-----
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

-----Original Message-----
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



-----Original Message-----
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


-----Original Message-----
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




-----Original Message-----
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
.

.

.

.

.