Summing in SQL

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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".

Although I don't understand exactly where these magic numbers come from,
I suspect a Design Problem.

One possible solution would be to provide a new table called "Aliases" or
something, which would contain information about which ItemNos must be
coalesced. It would look something like this (note that each field should
have a FK relationship to MyTable.ItemNo).

ItemNo...Alias
21043 21150
21047 21150

You should then be able to create some kind of join on this table and the
other one, and then group on the Aliases.Alias field, rather than the
MyTable.ItemNo field. The catch will be dealing with items that aren't
aliased at all, and therefore have to be represented by themselves.

Create a query like this and save it:

SELECT AcctNo,
IIF(0=(SELECT COUNT(*) FROM Aliases
WHERE Aliases.ItemNo=MyTable.ItemNo
),
ItemNo,
(SELECT ItemNo FROM Aliases WHERE Aliases.ItemNo=MyTable.ItemNo)
) AS EffectiveItemNo,
CostUnits
FROM MyTable

Then create a new query based on that one instead of the original table.

(Please note: I haven't checked to see if this is legal or if it runs
like wet sand) Still, it should get you a bit nearer.

Hope it helps.


Tim F
 
Thank you, Tim.

I will give it a try and let you know.

Rachel

Tim Ferguson said:
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".

Although I don't understand exactly where these magic numbers come from,
I suspect a Design Problem.

One possible solution would be to provide a new table called "Aliases" or
something, which would contain information about which ItemNos must be
coalesced. It would look something like this (note that each field should
have a FK relationship to MyTable.ItemNo).

ItemNo...Alias
21043 21150
21047 21150

You should then be able to create some kind of join on this table and the
other one, and then group on the Aliases.Alias field, rather than the
MyTable.ItemNo field. The catch will be dealing with items that aren't
aliased at all, and therefore have to be represented by themselves.

Create a query like this and save it:

SELECT AcctNo,
IIF(0=(SELECT COUNT(*) FROM Aliases
WHERE Aliases.ItemNo=MyTable.ItemNo
),
ItemNo,
(SELECT ItemNo FROM Aliases WHERE Aliases.ItemNo=MyTable.ItemNo)
) AS EffectiveItemNo,
CostUnits
FROM MyTable

Then create a new query based on that one instead of the original table.

(Please note: I haven't checked to see if this is legal or if it runs
like wet sand) Still, it should get you a bit nearer.

Hope it helps.


Tim F
 
Although I don't understand exactly where these magic numbers come from,
I suspect a Design Problem.

Not necessarily. Perhaps you are not familiar with working in an ERP
environment. We have the exact same problem here. In our situation, we have
Activity Numbers and Tasks. The data loaded from the ERP system has it's own
rules on Tasks. For our purposes, some task designations have to be changed.
And, to make matters more interesting, in some cases, the Task has to change
only for specific employees.

Here is the update query that applies the changes:

UPDATE tblTaskRedirect INNER JOIN BillcurrMonth3345 ON
(tblTaskRedirect.FROM_TASK = BillcurrMonth3345.Task) AND
(tblTaskRedirect.ACTIVITY = BillcurrMonth3345.Activity) SET
BillcurrMonth3345.Task = [TO_TASK]
WHERE (((tblTaskRedirect.REDIRECT_TASK)=True) AND
((BillcurrMonth3345.Activity) Is Not Null) AND
((IIf(IsNull([tbltaskredirect].[Employeeid]),-1,[tbltaskredirect].[employeeid]=[billcurrmonth3345].[employeeid]))=True));

So, I have, similar to what you proposed, a table that does the translation:
ACTIVITY (pk)
FROM_TASK
TO_TASK
EmployeeID
REDIRECT_TASK (Y/N) (So it can be turned on or off as necessary)





Tim Ferguson said:
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".

Although I don't understand exactly where these magic numbers come from,
I suspect a Design Problem.

One possible solution would be to provide a new table called "Aliases" or
something, which would contain information about which ItemNos must be
coalesced. It would look something like this (note that each field should
have a FK relationship to MyTable.ItemNo).

ItemNo...Alias
21043 21150
21047 21150

You should then be able to create some kind of join on this table and the
other one, and then group on the Aliases.Alias field, rather than the
MyTable.ItemNo field. The catch will be dealing with items that aren't
aliased at all, and therefore have to be represented by themselves.

Create a query like this and save it:

SELECT AcctNo,
IIF(0=(SELECT COUNT(*) FROM Aliases
WHERE Aliases.ItemNo=MyTable.ItemNo
),
ItemNo,
(SELECT ItemNo FROM Aliases WHERE Aliases.ItemNo=MyTable.ItemNo)
) AS EffectiveItemNo,
CostUnits
FROM MyTable

Then create a new query based on that one instead of the original table.

(Please note: I haven't checked to see if this is legal or if it runs
like wet sand) Still, it should get you a bit nearer.

Hope it helps.


Tim F
 
Tim,

You are a lifesaver. Not only was it easy to do, now I don't have to hard
code the combined ITEMNOs into the code and if there are any changes to them,
the users can access the Alias table and make the changes themselves.

Thank you so much. I'm embarrassed I didn't figure that out myself.

Rachel

Tim Ferguson said:
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".

Although I don't understand exactly where these magic numbers come from,
I suspect a Design Problem.

One possible solution would be to provide a new table called "Aliases" or
something, which would contain information about which ItemNos must be
coalesced. It would look something like this (note that each field should
have a FK relationship to MyTable.ItemNo).

ItemNo...Alias
21043 21150
21047 21150

You should then be able to create some kind of join on this table and the
other one, and then group on the Aliases.Alias field, rather than the
MyTable.ItemNo field. The catch will be dealing with items that aren't
aliased at all, and therefore have to be represented by themselves.

Create a query like this and save it:

SELECT AcctNo,
IIF(0=(SELECT COUNT(*) FROM Aliases
WHERE Aliases.ItemNo=MyTable.ItemNo
),
ItemNo,
(SELECT ItemNo FROM Aliases WHERE Aliases.ItemNo=MyTable.ItemNo)
) AS EffectiveItemNo,
CostUnits
FROM MyTable

Then create a new query based on that one instead of the original table.

(Please note: I haven't checked to see if this is legal or if it runs
like wet sand) Still, it should get you a bit nearer.

Hope it helps.


Tim F
 
Back
Top