If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Calculating fields for Access
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 . |
#3
|
|||
|
|||
Calculating fields for Access
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 . . |
#4
|
|||
|
|||
Calculating fields for Access
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 . . . |
#5
|
|||
|
|||
Calculating fields for Access
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 . . . . |
#6
|
|||
|
|||
Calculating fields for Access
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 . . . . . |
#7
|
|||
|
|||
Calculating fields for Access
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 . . . . . |
Thread Tools | |
Display Modes | |
|
|