Query

[FreddyBotz]

Hello to all! I have a database containing 40,000+ lines of data that gets updated monthly. Here is an example of what the results look like once I copy and paste them into Excel:

Acc # COMP UNITS BUDGET


SP186412a AB COMP 465 $56,258
SP186412c AB COMP 465 $69,569
SP186412r AB COMP 465 $11,254

SP186413a CD COMP 413 $69,214
SP186413c CD COMP 413 $98,558
SP186413r CD COMP 413 $55,256

SP186414a EF COMP 414 $44,478
SP186414c EF COMP 414 $44,478
SP186414r EF COMP 414 $44,478

2584 $493,543

The problem lies within the account numbers. I only need the units column to sum the acc't #'s that end in an "a" or "r". However, I need each line item corresponding to the appropriate account # to sum.

In a perfect world, I would like the results to appear as follows:

Acc # COMP UNITS BUDGET

SP186412 AB COMP 930 $137,081

SP186413 CD COMP 826 $223,028

SP186414 EF COMP 828 $133,434

Here is an excel function that I've been working on which appears to work but not with the desired results:

SUM((RIGHT(A3:A13,1)={"a","r"})*(C3:C13))

I've been obtaining the required results manually. I really hope there is a more efficient way of completing this task.

Regards

FB

 

[Tim1]

Hiya,

You can do this as a SELECT query from Access.

You will need something like:

SELECT SUBSTRING(acc_num_field,1,8),
comp_field,
units = SUM(units_field),
budget = SUM(budget_field)
FROM table
GROUP BY SUBSTRING(acc_num_field,1,8),
comp_field

HTH

Tim

 

[Aivars]

Hi!

Create query like this:

SELECT Left([AccountNo],Len([AccountNo])-1) AS AccNoGroup, MyTable.COMP, Sum(MyTable.UNITS) AS SumOfUNITS, Sum(MyTable.BUDGET) AS SumOfBUDGET
FROM MyTable
GROUP BY Left([AccountNo],Len([AccountNo])-1), MyTable.COMP;

Aivars

 

[FreddyBotz]

Thanks guys for answering my question. I have one little problem though. I have no idea where to put those formulas. Excel yes.....Access NO! I'm trying to learn. So would one of you mind walking me through this one. That would definitely be the step in the right direction for me. I would greatly appreciate it.

Thanks Again

FB