Calculating fields for Access
From: Chris Nebinger (anonymous_at_discussions.microsoft.com)
Date: 05/04/04
- Next message: Chris Nebinger: "Classes vs modules"
- Previous message: ari: "Classes vs modules"
- In reply to: Galin: "Calculating fields for Access"
- Next in thread: Galin: "Calculating fields for Access"
- Reply: Galin: "Calculating fields for Access"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 4 May 2004 09:44:10 -0700
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
>.
>
- Next message: Chris Nebinger: "Classes vs modules"
- Previous message: ari: "Classes vs modules"
- In reply to: Galin: "Calculating fields for Access"
- Next in thread: Galin: "Calculating fields for Access"
- Reply: Galin: "Calculating fields for Access"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
