How to sum on a range of numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I sum on a range of account numbers - by selecting say the first 3 in
a range of 6 digit number

eg
110200
190200
190300
210100

would like the value to be summed up as

110***
190***
210***

Thanks
Danny
 
I don't see any field/column that you want to sum??? Do you think you could
include this in your sample and result?
 
Hi Duane

The filed names are

Account No:
Amount £
Description

The account number is in a 6 digit format

Danny
 
So, you are making me assume that you want to sum the Amount field and not
display any details, just the sums....

SELECT Left([AccountNo], 3) & "***" as Act , Sum(Amount) as AmtSum
FROM TableWithNoName
GROUP BY Left([AccountNo], 3) & "***"
ORDER BY Left([AccountNo], 3) & "***";
 
I would like the account grouping and the total for that grouping displayed as
would like the value to be summed up as
My table NAME IS "All Data" hwere would I put the statement that you listed
below ?

In the SQL - data definition !

Thanks
Regards
Danny



Duane Hookom said:
So, you are making me assume that you want to sum the Amount field and not
display any details, just the sums....

SELECT Left([AccountNo], 3) & "***" as Act , Sum(Amount) as AmtSum
FROM TableWithNoName
GROUP BY Left([AccountNo], 3) & "***"
ORDER BY Left([AccountNo], 3) & "***";

--
Duane Hookom
MS Access MVP
--

Danny said:
Hi Duane

The filed names are

Account No:
Amount £
Description

The account number is in a 6 digit format

Danny
Click to expand...
Click to expand...
 
This is the SQL view of a query:

SELECT Left([AccountNo], 3) & "***" as Act , Sum(Amount) as AmtSum
FROM [All Data]
GROUP BY Left([AccountNo], 3) & "***"
ORDER BY Left([AccountNo], 3) & "***";



--
Duane Hookom
MS Access MVP


Danny said:
I would like the account grouping and the total for that grouping displayed
as
would like the value to be summed up as
My table NAME IS "All Data" hwere would I put the statement that you
listed
below ?

In the SQL - data definition !

Thanks
Regards
Danny



Duane Hookom said:
So, you are making me assume that you want to sum the Amount field and
not
display any details, just the sums....

SELECT Left([AccountNo], 3) & "***" as Act , Sum(Amount) as AmtSum
FROM TableWithNoName
GROUP BY Left([AccountNo], 3) & "***"
ORDER BY Left([AccountNo], 3) & "***";

--
Duane Hookom
MS Access MVP
--

Danny said:
Hi Duane

The filed names are

Account No:
Amount £
Description

The account number is in a 6 digit format

Danny

:

I don't see any field/column that you want to sum??? Do you think you
could
include this in your sample and result?

--
Duane Hookom
MS Access MVP
--

How do I sum on a range of account numbers - by selecting say the
first
3
in
a range of 6 digit number

eg
110200
190200
190300
210100


Thanks
Danny
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane

Got the following message when I tried to use te sql

<Message> in query expression <expression>. (Error 3075)
The expression you typed is not valid for the reason indicated in the
message. Make sure you have typed field names and punctuation correctly, and
then try the operation again.



Duane Hookom said:
This is the SQL view of a query:

SELECT Left([AccountNo], 3) & "***" as Act , Sum(Amount) as AmtSum
FROM [All Data]
GROUP BY Left([AccountNo], 3) & "***"
ORDER BY Left([AccountNo], 3) & "***";



--
Duane Hookom
MS Access MVP


Danny said:
I would like the account grouping and the total for that grouping displayed
as
would like the value to be summed up as
110***
190***
210***
Click to expand...

My table NAME IS "All Data" hwere would I put the statement that you
listed
below ?

In the SQL - data definition !

Thanks
Regards
Danny



Duane Hookom said:
So, you are making me assume that you want to sum the Amount field and
not
display any details, just the sums....

SELECT Left([AccountNo], 3) & "***" as Act , Sum(Amount) as AmtSum
FROM TableWithNoName
GROUP BY Left([AccountNo], 3) & "***"
ORDER BY Left([AccountNo], 3) & "***";

--
Duane Hookom
MS Access MVP
--

Hi Duane

The filed names are

Account No:
Amount £
Description

The account number is in a 6 digit format

Danny

:

I don't see any field/column that you want to sum??? Do you think you
could
include this in your sample and result?

--
Duane Hookom
MS Access MVP
--

How do I sum on a range of account numbers - by selecting say the
first
3
in
a range of 6 digit number

eg
110200
190200
190300
210100


Thanks
Danny
Click to expand...
Click to expand...
Click to expand...
 
I can't see your table and field names and data types. If you want further
assistance, please provide this information.

--
Duane Hookom
MS Access MVP


Danny said:
Hi Duane

Got the following message when I tried to use te sql

<Message> in query expression <expression>. (Error 3075)
The expression you typed is not valid for the reason indicated in the
message. Make sure you have typed field names and punctuation correctly,
and
then try the operation again.



Duane Hookom said:
This is the SQL view of a query:

SELECT Left([AccountNo], 3) & "***" as Act , Sum(Amount) as AmtSum
FROM [All Data]
GROUP BY Left([AccountNo], 3) & "***"
ORDER BY Left([AccountNo], 3) & "***";



--
Duane Hookom
MS Access MVP


Danny said:
I would like the account grouping and the total for that grouping
displayed
as
would like the value to be summed up as

110***
190***
210***

My table NAME IS "All Data" hwere would I put the statement that you
listed
below ?

In the SQL - data definition !

Thanks
Regards
Danny



:

So, you are making me assume that you want to sum the Amount field and
not
display any details, just the sums....

SELECT Left([AccountNo], 3) & "***" as Act , Sum(Amount) as AmtSum
FROM TableWithNoName
GROUP BY Left([AccountNo], 3) & "***"
ORDER BY Left([AccountNo], 3) & "***";

--
Duane Hookom
MS Access MVP
--

Hi Duane

The filed names are

Account No:
Amount £
Description

The account number is in a 6 digit format

Danny

:

I don't see any field/column that you want to sum??? Do you think
you
could
include this in your sample and result?

--
Duane Hookom
MS Access MVP
--

How do I sum on a range of account numbers - by selecting say the
first
3
in
a range of 6 digit number

eg
110200
190200
190300
210100


Thanks
Danny
Click to expand...
Click to expand...
Click to expand...
 
Back
Top