G
Guest
I'm not sure if I'm in the correct group here or if I should go to Access
Queries. Here's my problem. I am trying to create a query in Access using
SQL because I just can't figure out if it can be done using a regular Access
Query. I am very, very new to SQL. (Teaching myself)
I have records with the following fields:
AcctNo
ItemNo
Cost_Units
I need to sum the [Cost_Units] by [ItemNo] within each [AcctNo]. That I
have no problem with. But when the [ItemNo] is equal to a certain value, the
sum needs to be combined with another ItemNo's sum as well but needs to keep
the [ItemNo] value of one particular [ItemNo].
For example:
This is the easy one I can handle.
AcctNo = 1051652
ItemNo = 21006
Cost_Units = 100
AcctNo = 1051652
ItemNo = 21006
Cost_Units = 50
Result
AcctNo = 1051652
ItemNo = 21006
SumOfCost_Units = 150
Exception:
When the [ItemNo] = "21043", "21047", or "21150" then the [Cost_Units] for
all of these must be summed together and the [ItemNo] must appear as "21150".
AcctNo = 1051652
ItemNo = 21043
Cost_Units = 100
AcctNo = 1051652
ItemNo = 21047
Cost_Units = 50
AcctNo = 1051652
ItemNo = 21150
Cost_Units = 25
Result
AcctNo = 1051652
ItemNo = 21150
SumOfCost_Units = 175
Please , please help me with this.
Thank you,
Rachel
Queries. Here's my problem. I am trying to create a query in Access using
SQL because I just can't figure out if it can be done using a regular Access
Query. I am very, very new to SQL. (Teaching myself)
I have records with the following fields:
AcctNo
ItemNo
Cost_Units
I need to sum the [Cost_Units] by [ItemNo] within each [AcctNo]. That I
have no problem with. But when the [ItemNo] is equal to a certain value, the
sum needs to be combined with another ItemNo's sum as well but needs to keep
the [ItemNo] value of one particular [ItemNo].
For example:
This is the easy one I can handle.
AcctNo = 1051652
ItemNo = 21006
Cost_Units = 100
AcctNo = 1051652
ItemNo = 21006
Cost_Units = 50
Result
AcctNo = 1051652
ItemNo = 21006
SumOfCost_Units = 150
Exception:
When the [ItemNo] = "21043", "21047", or "21150" then the [Cost_Units] for
all of these must be summed together and the [ItemNo] must appear as "21150".
AcctNo = 1051652
ItemNo = 21043
Cost_Units = 100
AcctNo = 1051652
ItemNo = 21047
Cost_Units = 50
AcctNo = 1051652
ItemNo = 21150
Cost_Units = 25
Result
AcctNo = 1051652
ItemNo = 21150
SumOfCost_Units = 175
Please , please help me with this.
Thank you,
Rachel