minus numbers porblem

  • Thread starter Thread starter Lesley
  • Start date Start date
L

Lesley

My database has 32 number items (scottish local authority areas) within it
and when I do a count in the query I created, 30 of the 32 display as a minus
number. I have compared the properties of the minus ones to the ones which
are not displaying minus and they seem to be identical in everyway. I don't
want any of them to display minus.

I am using Access 2000.

Can you help me?
 
You haven't really given enough information for us to give you, with absolute
certainty, the right answer.

What shows as minus? Is it the ID number of the field? Is it the value of
the field? Where does it show this? Is it in the table, on a form, on a
report? How does data get entered? What is the datatype of the field in
question?
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.
 
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Lesley

There's a very good chance (though you haven't explicitly described this)
that your table is set up like a spreadsheet! Let me ask, are those "32
number items" each an individual field, set up as a Yes/No data type?

This would be one way you could add up the fields and get a negative number
(since Access stores "True" as -1).

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hi Jeff

The 32 fields are individually set up and are number fields but you should
know that they were originally YES/NO fields. I changed them to number
fields to see what would happen. It is the results of the query I set up
where the sum totals of each field are displayed. I think that when they
were changed to number fields it was at that point that 2 of the neg numbers
became positive.

Query: the query has sum in the total row and they are all set as General
numbers (I can see this in properties). The 2 fields which are positive
numbers appear to be identical to the other 30 neg in properties.

I have pasted the SQL view below of the query - the 2 fields with positive
numbers are Scottish Borders and Aberdeen City

SELECT DISTINCTROW Sum([Contacts Main Table].NorthLanarkshire) AS
SumOfNorthLanarkshire, Sum([Contacts Main Table].OrkneyIslands) AS
SumOfOrkneyIslands, Sum([Contacts Main Table].[Perth&Kinross]) AS
[SumOfPerth&Kinross], Sum([Contacts Main Table].Renfrewshire) AS
SumOfRenfrewshire, Sum([Contacts Main Table].ScottishBorders) AS
SumOfScottishBorders, Sum([Contacts Main Table].ShetlandIsles) AS
SumOfShetlandIsles, Sum([Contacts Main Table].SouthAyrshire) AS
SumOfSouthAyrshire, Sum([Contacts Main Table].SouthLanarkshire) AS
SumOfSouthLanarkshire, Sum([Contacts Main Table].Stirling) AS SumOfStirling,
Sum([Contacts Main Table].WestDunbartonshire) AS SumOfWestDunbartonshire,
Sum([Contacts Main Table].WestLothian) AS SumOfWestLothian, Sum([Contacts
Main Table].AberdeenCity) AS [Sum Of AberdeenCity], Sum([Contacts Main
Table].Aberdeenshire) AS [Sum Of Aberdeenshire], Sum([Contacts Main
Table].Angus) AS [Sum Of Angus], Sum([Contacts Main Table].[Argyll&Bute]) AS
[Sum Of Argyll&Bute], Sum([Contacts Main Table].Clackmannanshire) AS [Sum Of
Clackmannanshire], Sum([Contacts Main Table].[Dumfries&Galloway]) AS [Sum Of
Dumfries&Galloway], Sum([Contacts Main Table].DundeeCity) AS [Sum Of
DundeeCity], Sum([Contacts Main Table].EastAyrshire) AS [Sum Of
EastAyrshire], Sum([Contacts Main Table].EastDunbartonshire) AS [Sum Of
EastDunbartonshire], Sum([Contacts Main Table].EastLothian) AS [Sum Of
EastLothian], Sum([Contacts Main Table].EastRenfrewshire) AS [Sum Of
EastRenfrewshire], Sum([Contacts Main Table].EdinburghCity) AS [Sum Of
EdinburghCity], Sum([Contacts Main Table].EileanSiar) AS [Sum Of EileanSiar],
Sum([Contacts Main Table].Falkirk) AS [Sum Of Falkirk], Sum([Contacts Main
Table].Fife) AS [Sum Of Fife], Sum([Contacts Main Table].GlasgowCity) AS [Sum
Of GlasgowCity], Sum([Contacts Main Table].Highlands) AS [Sum Of Highlands],
Sum([Contacts Main Table].Inverclyde) AS [Sum Of Inverclyde], Sum([Contacts
Main Table].Midlothian) AS [Sum Of Midlothian], Sum([Contacts Main
Table].Moray) AS [Sum Of Moray], Sum([Contacts Main Table].NorthAyrshire) AS
SumOfNorthAyrshire
FROM [Contacts Main Table];

Thank you very much for your time - as we are a small charity we really
cannot afford an engineer to do this!!!

Kind regards
lesley

--
Many thanks
Lesley


Jeff Boyce said:
Lesley

There's a very good chance (though you haven't explicitly described this)
that your table is set up like a spreadsheet! Let me ask, are those "32
number items" each an individual field, set up as a Yes/No data type?

This would be one way you could add up the fields and get a negative number
(since Access stores "True" as -1).

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
I see a couple of possible solutions.
1) Wrap your sums in the Abs function to turn the sum to a positive number
ABS( Sum([Contacts Main Table].NorthLanarkshire)) AS
SumOfNorthLanarkshire

2) Update the fields in the table using the abs function
UPDATE [Contacts Main Table]
SET [NorthLanarkShire] = Abs([NorthLanarkShire])
, OrkneyIslands = Abs([OrkneyIslands])
, [Perth&Kinross] = Abs([Perth&Kinross])
....

It appears that your table design is incorrect and could cause you further
problems since you are storing data as field titles.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Lesley said:
Hi Jeff

The 32 fields are individually set up and are number fields but you should
know that they were originally YES/NO fields. I changed them to number
fields to see what would happen. It is the results of the query I set up
where the sum totals of each field are displayed. I think that when they
were changed to number fields it was at that point that 2 of the neg
numbers
became positive.

Query: the query has sum in the total row and they are all set as General
numbers (I can see this in properties). The 2 fields which are positive
numbers appear to be identical to the other 30 neg in properties.

I have pasted the SQL view below of the query - the 2 fields with positive
numbers are Scottish Borders and Aberdeen City

SELECT DISTINCTROW Sum([Contacts Main Table].NorthLanarkshire) AS
SumOfNorthLanarkshire, Sum([Contacts Main Table].OrkneyIslands) AS
SumOfOrkneyIslands, Sum([Contacts Main Table].[Perth&Kinross]) AS
[SumOfPerth&Kinross], Sum([Contacts Main Table].Renfrewshire) AS
SumOfRenfrewshire, Sum([Contacts Main Table].ScottishBorders) AS
SumOfScottishBorders, Sum([Contacts Main Table].ShetlandIsles) AS
SumOfShetlandIsles, Sum([Contacts Main Table].SouthAyrshire) AS
SumOfSouthAyrshire, Sum([Contacts Main Table].SouthLanarkshire) AS
SumOfSouthLanarkshire, Sum([Contacts Main Table].Stirling) AS
SumOfStirling,
Sum([Contacts Main Table].WestDunbartonshire) AS SumOfWestDunbartonshire,
Sum([Contacts Main Table].WestLothian) AS SumOfWestLothian, Sum([Contacts
Main Table].AberdeenCity) AS [Sum Of AberdeenCity], Sum([Contacts Main
Table].Aberdeenshire) AS [Sum Of Aberdeenshire], Sum([Contacts Main
Table].Angus) AS [Sum Of Angus], Sum([Contacts Main Table].[Argyll&Bute])
AS
[Sum Of Argyll&Bute], Sum([Contacts Main Table].Clackmannanshire) AS [Sum
Of
Clackmannanshire], Sum([Contacts Main Table].[Dumfries&Galloway]) AS [Sum
Of
Dumfries&Galloway], Sum([Contacts Main Table].DundeeCity) AS [Sum Of
DundeeCity], Sum([Contacts Main Table].EastAyrshire) AS [Sum Of
EastAyrshire], Sum([Contacts Main Table].EastDunbartonshire) AS [Sum Of
EastDunbartonshire], Sum([Contacts Main Table].EastLothian) AS [Sum Of
EastLothian], Sum([Contacts Main Table].EastRenfrewshire) AS [Sum Of
EastRenfrewshire], Sum([Contacts Main Table].EdinburghCity) AS [Sum Of
EdinburghCity], Sum([Contacts Main Table].EileanSiar) AS [Sum Of
EileanSiar],
Sum([Contacts Main Table].Falkirk) AS [Sum Of Falkirk], Sum([Contacts Main
Table].Fife) AS [Sum Of Fife], Sum([Contacts Main Table].GlasgowCity) AS
[Sum
Of GlasgowCity], Sum([Contacts Main Table].Highlands) AS [Sum Of
Highlands],
Sum([Contacts Main Table].Inverclyde) AS [Sum Of Inverclyde],
Sum([Contacts
Main Table].Midlothian) AS [Sum Of Midlothian], Sum([Contacts Main
Table].Moray) AS [Sum Of Moray], Sum([Contacts Main Table].NorthAyrshire)
AS
SumOfNorthAyrshire
FROM [Contacts Main Table];

Thank you very much for your time - as we are a small charity we really
cannot afford an engineer to do this!!!

Kind regards
lesley
Click to expand...
 
This has worked thank you very much!!!

Best wishes
--
Many thanks
Lesley


John Spencer said:
I see a couple of possible solutions.
1) Wrap your sums in the Abs function to turn the sum to a positive number
ABS( Sum([Contacts Main Table].NorthLanarkshire)) AS
SumOfNorthLanarkshire

2) Update the fields in the table using the abs function
UPDATE [Contacts Main Table]
SET [NorthLanarkShire] = Abs([NorthLanarkShire])
, OrkneyIslands = Abs([OrkneyIslands])
, [Perth&Kinross] = Abs([Perth&Kinross])
....

It appears that your table design is incorrect and could cause you further
problems since you are storing data as field titles.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Lesley said:
Hi Jeff

The 32 fields are individually set up and are number fields but you should
know that they were originally YES/NO fields. I changed them to number
fields to see what would happen. It is the results of the query I set up
where the sum totals of each field are displayed. I think that when they
were changed to number fields it was at that point that 2 of the neg
numbers
became positive.

Query: the query has sum in the total row and they are all set as General
numbers (I can see this in properties). The 2 fields which are positive
numbers appear to be identical to the other 30 neg in properties.

I have pasted the SQL view below of the query - the 2 fields with positive
numbers are Scottish Borders and Aberdeen City

SELECT DISTINCTROW Sum([Contacts Main Table].NorthLanarkshire) AS
SumOfNorthLanarkshire, Sum([Contacts Main Table].OrkneyIslands) AS
SumOfOrkneyIslands, Sum([Contacts Main Table].[Perth&Kinross]) AS
[SumOfPerth&Kinross], Sum([Contacts Main Table].Renfrewshire) AS
SumOfRenfrewshire, Sum([Contacts Main Table].ScottishBorders) AS
SumOfScottishBorders, Sum([Contacts Main Table].ShetlandIsles) AS
SumOfShetlandIsles, Sum([Contacts Main Table].SouthAyrshire) AS
SumOfSouthAyrshire, Sum([Contacts Main Table].SouthLanarkshire) AS
SumOfSouthLanarkshire, Sum([Contacts Main Table].Stirling) AS
SumOfStirling,
Sum([Contacts Main Table].WestDunbartonshire) AS SumOfWestDunbartonshire,
Sum([Contacts Main Table].WestLothian) AS SumOfWestLothian, Sum([Contacts
Main Table].AberdeenCity) AS [Sum Of AberdeenCity], Sum([Contacts Main
Table].Aberdeenshire) AS [Sum Of Aberdeenshire], Sum([Contacts Main
Table].Angus) AS [Sum Of Angus], Sum([Contacts Main Table].[Argyll&Bute])
AS
[Sum Of Argyll&Bute], Sum([Contacts Main Table].Clackmannanshire) AS [Sum
Of
Clackmannanshire], Sum([Contacts Main Table].[Dumfries&Galloway]) AS [Sum
Of
Dumfries&Galloway], Sum([Contacts Main Table].DundeeCity) AS [Sum Of
DundeeCity], Sum([Contacts Main Table].EastAyrshire) AS [Sum Of
EastAyrshire], Sum([Contacts Main Table].EastDunbartonshire) AS [Sum Of
EastDunbartonshire], Sum([Contacts Main Table].EastLothian) AS [Sum Of
EastLothian], Sum([Contacts Main Table].EastRenfrewshire) AS [Sum Of
EastRenfrewshire], Sum([Contacts Main Table].EdinburghCity) AS [Sum Of
EdinburghCity], Sum([Contacts Main Table].EileanSiar) AS [Sum Of
EileanSiar],
Sum([Contacts Main Table].Falkirk) AS [Sum Of Falkirk], Sum([Contacts Main
Table].Fife) AS [Sum Of Fife], Sum([Contacts Main Table].GlasgowCity) AS
[Sum
Of GlasgowCity], Sum([Contacts Main Table].Highlands) AS [Sum Of
Highlands],
Sum([Contacts Main Table].Inverclyde) AS [Sum Of Inverclyde],
Sum([Contacts
Main Table].Midlothian) AS [Sum Of Midlothian], Sum([Contacts Main
Table].Moray) AS [Sum Of Moray], Sum([Contacts Main Table].NorthAyrshire)
AS
SumOfNorthAyrshire
FROM [Contacts Main Table];

Thank you very much for your time - as we are a small charity we really
cannot afford an engineer to do this!!!

Kind regards
lesley

--
Many thanks
Lesley


Jeff Boyce said:
Lesley

There's a very good chance (though you haven't explicitly described this)
that your table is set up like a spreadsheet! Let me ask, are those "32
number items" each an individual field, set up as a Yes/No data type?

This would be one way you could add up the fields and get a negative
number
(since Access stores "True" as -1).

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

My database has 32 number items (scottish local authority areas) within
it
and when I do a count in the query I created, 30 of the 32 display as a
minus
number. I have compared the properties of the minus ones to the ones
which
are not displaying minus and they seem to be identical in everyway. I
don't
want any of them to display minus.

I am using Access 2000.

Can you help me?
Click to expand...
Click to expand...
Click to expand...
 
Lesley

I'll reinforce John's observation.

If you have "repeating fields" (your "32 ..."), you have a spreadsheet, not
a relational table.

Access is not a spreadsheet on steroids, and you will NOT get the benefit of
Access' features and functions if you insist on feeding it 'sheet data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Lesley said:
This has worked thank you very much!!!

Best wishes
--
Many thanks
Lesley


John Spencer said:
I see a couple of possible solutions.
1) Wrap your sums in the Abs function to turn the sum to a positive
number
ABS( Sum([Contacts Main Table].NorthLanarkshire)) AS
SumOfNorthLanarkshire

2) Update the fields in the table using the abs function
UPDATE [Contacts Main Table]
SET [NorthLanarkShire] = Abs([NorthLanarkShire])
, OrkneyIslands = Abs([OrkneyIslands])
, [Perth&Kinross] = Abs([Perth&Kinross])
....

It appears that your table design is incorrect and could cause you
further
problems since you are storing data as field titles.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Lesley said:
Hi Jeff

The 32 fields are individually set up and are number fields but you
should
know that they were originally YES/NO fields. I changed them to
number
fields to see what would happen. It is the results of the query I set
up
where the sum totals of each field are displayed. I think that when
they
were changed to number fields it was at that point that 2 of the neg
numbers
became positive.

Query: the query has sum in the total row and they are all set as
General
numbers (I can see this in properties). The 2 fields which are
positive
numbers appear to be identical to the other 30 neg in properties.

I have pasted the SQL view below of the query - the 2 fields with
positive
numbers are Scottish Borders and Aberdeen City

SELECT DISTINCTROW Sum([Contacts Main Table].NorthLanarkshire) AS
SumOfNorthLanarkshire, Sum([Contacts Main Table].OrkneyIslands) AS
SumOfOrkneyIslands, Sum([Contacts Main Table].[Perth&Kinross]) AS
[SumOfPerth&Kinross], Sum([Contacts Main Table].Renfrewshire) AS
SumOfRenfrewshire, Sum([Contacts Main Table].ScottishBorders) AS
SumOfScottishBorders, Sum([Contacts Main Table].ShetlandIsles) AS
SumOfShetlandIsles, Sum([Contacts Main Table].SouthAyrshire) AS
SumOfSouthAyrshire, Sum([Contacts Main Table].SouthLanarkshire) AS
SumOfSouthLanarkshire, Sum([Contacts Main Table].Stirling) AS
SumOfStirling,
Sum([Contacts Main Table].WestDunbartonshire) AS
SumOfWestDunbartonshire,
Sum([Contacts Main Table].WestLothian) AS SumOfWestLothian,
Sum([Contacts
Main Table].AberdeenCity) AS [Sum Of AberdeenCity], Sum([Contacts Main
Table].Aberdeenshire) AS [Sum Of Aberdeenshire], Sum([Contacts Main
Table].Angus) AS [Sum Of Angus], Sum([Contacts Main
Table].[Argyll&Bute])
AS
[Sum Of Argyll&Bute], Sum([Contacts Main Table].Clackmannanshire) AS
[Sum
Of
Clackmannanshire], Sum([Contacts Main Table].[Dumfries&Galloway]) AS
[Sum
Of
Dumfries&Galloway], Sum([Contacts Main Table].DundeeCity) AS [Sum Of
DundeeCity], Sum([Contacts Main Table].EastAyrshire) AS [Sum Of
EastAyrshire], Sum([Contacts Main Table].EastDunbartonshire) AS [Sum Of
EastDunbartonshire], Sum([Contacts Main Table].EastLothian) AS [Sum Of
EastLothian], Sum([Contacts Main Table].EastRenfrewshire) AS [Sum Of
EastRenfrewshire], Sum([Contacts Main Table].EdinburghCity) AS [Sum Of
EdinburghCity], Sum([Contacts Main Table].EileanSiar) AS [Sum Of
EileanSiar],
Sum([Contacts Main Table].Falkirk) AS [Sum Of Falkirk], Sum([Contacts
Main
Table].Fife) AS [Sum Of Fife], Sum([Contacts Main Table].GlasgowCity)
AS
[Sum
Of GlasgowCity], Sum([Contacts Main Table].Highlands) AS [Sum Of
Highlands],
Sum([Contacts Main Table].Inverclyde) AS [Sum Of Inverclyde],
Sum([Contacts
Main Table].Midlothian) AS [Sum Of Midlothian], Sum([Contacts Main
Table].Moray) AS [Sum Of Moray], Sum([Contacts Main
Table].NorthAyrshire)
AS
SumOfNorthAyrshire
FROM [Contacts Main Table];

Thank you very much for your time - as we are a small charity we really
cannot afford an engineer to do this!!!

Kind regards
lesley

--
Many thanks
Lesley


:

Lesley

There's a very good chance (though you haven't explicitly described
this)
that your table is set up like a spreadsheet! Let me ask, are those
"32
number items" each an individual field, set up as a Yes/No data type?

This would be one way you could add up the fields and get a negative
number
(since Access stores "True" as -1).

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

My database has 32 number items (scottish local authority areas)
within
it
and when I do a count in the query I created, 30 of the 32 display
as a
minus
number. I have compared the properties of the minus ones to the
ones
which
are not displaying minus and they seem to be identical in everyway.
I
don't
want any of them to display minus.

I am using Access 2000.

Can you help me?
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeff

I think I now understand what you mean when you talk about using access as a
spreadsheet. I know I can present the 32 areas as a drop down menu - would
that be better and would I still be able to count/sum? I would gratefully
receive any suggestions you may have - I am very new to using access!

--
Many thanks
Lesley


Jeff Boyce said:
Lesley

I'll reinforce John's observation.

If you have "repeating fields" (your "32 ..."), you have a spreadsheet, not
a relational table.

Access is not a spreadsheet on steroids, and you will NOT get the benefit of
Access' features and functions if you insist on feeding it 'sheet data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Lesley said:
This has worked thank you very much!!!

Best wishes
--
Many thanks
Lesley


John Spencer said:
I see a couple of possible solutions.
1) Wrap your sums in the Abs function to turn the sum to a positive
number
ABS( Sum([Contacts Main Table].NorthLanarkshire)) AS
SumOfNorthLanarkshire

2) Update the fields in the table using the abs function
UPDATE [Contacts Main Table]
SET [NorthLanarkShire] = Abs([NorthLanarkShire])
, OrkneyIslands = Abs([OrkneyIslands])
, [Perth&Kinross] = Abs([Perth&Kinross])
....

It appears that your table design is incorrect and could cause you
further
problems since you are storing data as field titles.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi Jeff

The 32 fields are individually set up and are number fields but you
should
know that they were originally YES/NO fields. I changed them to
number
fields to see what would happen. It is the results of the query I set
up
where the sum totals of each field are displayed. I think that when
they
were changed to number fields it was at that point that 2 of the neg
numbers
became positive.

Query: the query has sum in the total row and they are all set as
General
numbers (I can see this in properties). The 2 fields which are
positive
numbers appear to be identical to the other 30 neg in properties.

I have pasted the SQL view below of the query - the 2 fields with
positive
numbers are Scottish Borders and Aberdeen City

SELECT DISTINCTROW Sum([Contacts Main Table].NorthLanarkshire) AS
SumOfNorthLanarkshire, Sum([Contacts Main Table].OrkneyIslands) AS
SumOfOrkneyIslands, Sum([Contacts Main Table].[Perth&Kinross]) AS
[SumOfPerth&Kinross], Sum([Contacts Main Table].Renfrewshire) AS
SumOfRenfrewshire, Sum([Contacts Main Table].ScottishBorders) AS
SumOfScottishBorders, Sum([Contacts Main Table].ShetlandIsles) AS
SumOfShetlandIsles, Sum([Contacts Main Table].SouthAyrshire) AS
SumOfSouthAyrshire, Sum([Contacts Main Table].SouthLanarkshire) AS
SumOfSouthLanarkshire, Sum([Contacts Main Table].Stirling) AS
SumOfStirling,
Sum([Contacts Main Table].WestDunbartonshire) AS
SumOfWestDunbartonshire,
Sum([Contacts Main Table].WestLothian) AS SumOfWestLothian,
Sum([Contacts
Main Table].AberdeenCity) AS [Sum Of AberdeenCity], Sum([Contacts Main
Table].Aberdeenshire) AS [Sum Of Aberdeenshire], Sum([Contacts Main
Table].Angus) AS [Sum Of Angus], Sum([Contacts Main
Table].[Argyll&Bute])
AS
[Sum Of Argyll&Bute], Sum([Contacts Main Table].Clackmannanshire) AS
[Sum
Of
Clackmannanshire], Sum([Contacts Main Table].[Dumfries&Galloway]) AS
[Sum
Of
Dumfries&Galloway], Sum([Contacts Main Table].DundeeCity) AS [Sum Of
DundeeCity], Sum([Contacts Main Table].EastAyrshire) AS [Sum Of
EastAyrshire], Sum([Contacts Main Table].EastDunbartonshire) AS [Sum Of
EastDunbartonshire], Sum([Contacts Main Table].EastLothian) AS [Sum Of
EastLothian], Sum([Contacts Main Table].EastRenfrewshire) AS [Sum Of
EastRenfrewshire], Sum([Contacts Main Table].EdinburghCity) AS [Sum Of
EdinburghCity], Sum([Contacts Main Table].EileanSiar) AS [Sum Of
EileanSiar],
Sum([Contacts Main Table].Falkirk) AS [Sum Of Falkirk], Sum([Contacts
Main
Table].Fife) AS [Sum Of Fife], Sum([Contacts Main Table].GlasgowCity)
AS
[Sum
Of GlasgowCity], Sum([Contacts Main Table].Highlands) AS [Sum Of
Highlands],
Sum([Contacts Main Table].Inverclyde) AS [Sum Of Inverclyde],
Sum([Contacts
Main Table].Midlothian) AS [Sum Of Midlothian], Sum([Contacts Main
Table].Moray) AS [Sum Of Moray], Sum([Contacts Main
Table].NorthAyrshire)
AS
SumOfNorthAyrshire
FROM [Contacts Main Table];

Thank you very much for your time - as we are a small charity we really
cannot afford an engineer to do this!!!

Kind regards
lesley

--
Many thanks
Lesley


:

Lesley

There's a very good chance (though you haven't explicitly described
this)
that your table is set up like a spreadsheet! Let me ask, are those
"32
number items" each an individual field, set up as a Yes/No data type?

This would be one way you could add up the fields and get a negative
number
(since Access stores "True" as -1).

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

My database has 32 number items (scottish local authority areas)
within
it
and when I do a count in the query I created, 30 of the 32 display
as a
minus
number. I have compared the properties of the minus ones to the
ones
which
are not displaying minus and they seem to be identical in everyway.
I
don't
want any of them to display minus.

I am using Access 2000.

Can you help me?
Click to expand...
Click to expand...
Click to expand...
 
You would be able to sum the areas much easier if your table design was
correct.

Without knowing a lot more about your data I hesitate to make suggestions on
the design.

In addition to your present table I would probably add two tables
Table Areas - a listing of the 32 areas you have

Table ContactAreas -
Contact field
Area field
Value stored in Area field

You would then have one record for each Contact and Area in the above table.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Lesley said:
Hi Jeff

I think I now understand what you mean when you talk about using access as
a
spreadsheet. I know I can present the 32 areas as a drop down menu -
would
that be better and would I still be able to count/sum? I would gratefully
receive any suggestions you may have - I am very new to using access!

--
Many thanks
Lesley


Jeff Boyce said:
Lesley

I'll reinforce John's observation.

If you have "repeating fields" (your "32 ..."), you have a spreadsheet,
not
a relational table.

Access is not a spreadsheet on steroids, and you will NOT get the benefit
of
Access' features and functions if you insist on feeding it 'sheet data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Lesley said:
This has worked thank you very much!!!

Best wishes
--
Many thanks
Lesley


:

I see a couple of possible solutions.
1) Wrap your sums in the Abs function to turn the sum to a positive
number
ABS( Sum([Contacts Main Table].NorthLanarkshire)) AS
SumOfNorthLanarkshire

2) Update the fields in the table using the abs function
UPDATE [Contacts Main Table]
SET [NorthLanarkShire] = Abs([NorthLanarkShire])
, OrkneyIslands = Abs([OrkneyIslands])
, [Perth&Kinross] = Abs([Perth&Kinross])
....

It appears that your table design is incorrect and could cause you
further
problems since you are storing data as field titles.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi Jeff

The 32 fields are individually set up and are number fields but you
should
know that they were originally YES/NO fields. I changed them to
number
fields to see what would happen. It is the results of the query I
set
up
where the sum totals of each field are displayed. I think that when
they
were changed to number fields it was at that point that 2 of the neg
numbers
became positive.

Query: the query has sum in the total row and they are all set as
General
numbers (I can see this in properties). The 2 fields which are
positive
numbers appear to be identical to the other 30 neg in properties.

I have pasted the SQL view below of the query - the 2 fields with
positive
numbers are Scottish Borders and Aberdeen City

SELECT DISTINCTROW Sum([Contacts Main Table].NorthLanarkshire) AS
SumOfNorthLanarkshire, Sum([Contacts Main Table].OrkneyIslands) AS
SumOfOrkneyIslands, Sum([Contacts Main Table].[Perth&Kinross]) AS
[SumOfPerth&Kinross], Sum([Contacts Main Table].Renfrewshire) AS
SumOfRenfrewshire, Sum([Contacts Main Table].ScottishBorders) AS
SumOfScottishBorders, Sum([Contacts Main Table].ShetlandIsles) AS
SumOfShetlandIsles, Sum([Contacts Main Table].SouthAyrshire) AS
SumOfSouthAyrshire, Sum([Contacts Main Table].SouthLanarkshire) AS
SumOfSouthLanarkshire, Sum([Contacts Main Table].Stirling) AS
SumOfStirling,
Sum([Contacts Main Table].WestDunbartonshire) AS
SumOfWestDunbartonshire,
Sum([Contacts Main Table].WestLothian) AS SumOfWestLothian,
Sum([Contacts
Main Table].AberdeenCity) AS [Sum Of AberdeenCity], Sum([Contacts
Main
Table].Aberdeenshire) AS [Sum Of Aberdeenshire], Sum([Contacts Main
Table].Angus) AS [Sum Of Angus], Sum([Contacts Main
Table].[Argyll&Bute])
AS
[Sum Of Argyll&Bute], Sum([Contacts Main Table].Clackmannanshire) AS
[Sum
Of
Clackmannanshire], Sum([Contacts Main Table].[Dumfries&Galloway]) AS
[Sum
Of
Dumfries&Galloway], Sum([Contacts Main Table].DundeeCity) AS [Sum Of
DundeeCity], Sum([Contacts Main Table].EastAyrshire) AS [Sum Of
EastAyrshire], Sum([Contacts Main Table].EastDunbartonshire) AS [Sum
Of
EastDunbartonshire], Sum([Contacts Main Table].EastLothian) AS [Sum
Of
EastLothian], Sum([Contacts Main Table].EastRenfrewshire) AS [Sum Of
EastRenfrewshire], Sum([Contacts Main Table].EdinburghCity) AS [Sum
Of
EdinburghCity], Sum([Contacts Main Table].EileanSiar) AS [Sum Of
EileanSiar],
Sum([Contacts Main Table].Falkirk) AS [Sum Of Falkirk],
Sum([Contacts
Main
Table].Fife) AS [Sum Of Fife], Sum([Contacts Main
Table].GlasgowCity)
AS
[Sum
Of GlasgowCity], Sum([Contacts Main Table].Highlands) AS [Sum Of
Highlands],
Sum([Contacts Main Table].Inverclyde) AS [Sum Of Inverclyde],
Sum([Contacts
Main Table].Midlothian) AS [Sum Of Midlothian], Sum([Contacts Main
Table].Moray) AS [Sum Of Moray], Sum([Contacts Main
Table].NorthAyrshire)
AS
SumOfNorthAyrshire
FROM [Contacts Main Table];

Thank you very much for your time - as we are a small charity we
really
cannot afford an engineer to do this!!!

Kind regards
lesley

--
Many thanks
Lesley


:

Lesley

There's a very good chance (though you haven't explicitly described
this)
that your table is set up like a spreadsheet! Let me ask, are
those
"32
number items" each an individual field, set up as a Yes/No data
type?

This would be one way you could add up the fields and get a
negative
number
(since Access stores "True" as -1).

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

My database has 32 number items (scottish local authority areas)
within
it
and when I do a count in the query I created, 30 of the 32
display
as a
minus
number. I have compared the properties of the minus ones to the
ones
which
are not displaying minus and they seem to be identical in
everyway.
I
don't
want any of them to display minus.

I am using Access 2000.

Can you help me?
Click to expand...
Click to expand...
Click to expand...
 
Thanks John - i will give it some thought.
--
Many thanks
Lesley


John Spencer said:
You would be able to sum the areas much easier if your table design was
correct.

Without knowing a lot more about your data I hesitate to make suggestions on
the design.

In addition to your present table I would probably add two tables
Table Areas - a listing of the 32 areas you have

Table ContactAreas -
Contact field
Area field
Value stored in Area field

You would then have one record for each Contact and Area in the above table.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Lesley said:
Hi Jeff

I think I now understand what you mean when you talk about using access as
a
spreadsheet. I know I can present the 32 areas as a drop down menu -
would
that be better and would I still be able to count/sum? I would gratefully
receive any suggestions you may have - I am very new to using access!

--
Many thanks
Lesley


Jeff Boyce said:
Lesley

I'll reinforce John's observation.

If you have "repeating fields" (your "32 ..."), you have a spreadsheet,
not
a relational table.

Access is not a spreadsheet on steroids, and you will NOT get the benefit
of
Access' features and functions if you insist on feeding it 'sheet data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

This has worked thank you very much!!!

Best wishes
--
Many thanks
Lesley


:

I see a couple of possible solutions.
1) Wrap your sums in the Abs function to turn the sum to a positive
number
ABS( Sum([Contacts Main Table].NorthLanarkshire)) AS
SumOfNorthLanarkshire

2) Update the fields in the table using the abs function
UPDATE [Contacts Main Table]
SET [NorthLanarkShire] = Abs([NorthLanarkShire])
, OrkneyIslands = Abs([OrkneyIslands])
, [Perth&Kinross] = Abs([Perth&Kinross])
....

It appears that your table design is incorrect and could cause you
further
problems since you are storing data as field titles.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi Jeff

The 32 fields are individually set up and are number fields but you
should
know that they were originally YES/NO fields. I changed them to
number
fields to see what would happen. It is the results of the query I
set
up
where the sum totals of each field are displayed. I think that when
they
were changed to number fields it was at that point that 2 of the neg
numbers
became positive.

Query: the query has sum in the total row and they are all set as
General
numbers (I can see this in properties). The 2 fields which are
positive
numbers appear to be identical to the other 30 neg in properties.

I have pasted the SQL view below of the query - the 2 fields with
positive
numbers are Scottish Borders and Aberdeen City

SELECT DISTINCTROW Sum([Contacts Main Table].NorthLanarkshire) AS
SumOfNorthLanarkshire, Sum([Contacts Main Table].OrkneyIslands) AS
SumOfOrkneyIslands, Sum([Contacts Main Table].[Perth&Kinross]) AS
[SumOfPerth&Kinross], Sum([Contacts Main Table].Renfrewshire) AS
SumOfRenfrewshire, Sum([Contacts Main Table].ScottishBorders) AS
SumOfScottishBorders, Sum([Contacts Main Table].ShetlandIsles) AS
SumOfShetlandIsles, Sum([Contacts Main Table].SouthAyrshire) AS
SumOfSouthAyrshire, Sum([Contacts Main Table].SouthLanarkshire) AS
SumOfSouthLanarkshire, Sum([Contacts Main Table].Stirling) AS
SumOfStirling,
Sum([Contacts Main Table].WestDunbartonshire) AS
SumOfWestDunbartonshire,
Sum([Contacts Main Table].WestLothian) AS SumOfWestLothian,
Sum([Contacts
Main Table].AberdeenCity) AS [Sum Of AberdeenCity], Sum([Contacts
Main
Table].Aberdeenshire) AS [Sum Of Aberdeenshire], Sum([Contacts Main
Table].Angus) AS [Sum Of Angus], Sum([Contacts Main
Table].[Argyll&Bute])
AS
[Sum Of Argyll&Bute], Sum([Contacts Main Table].Clackmannanshire) AS
[Sum
Of
Clackmannanshire], Sum([Contacts Main Table].[Dumfries&Galloway]) AS
[Sum
Of
Dumfries&Galloway], Sum([Contacts Main Table].DundeeCity) AS [Sum Of
DundeeCity], Sum([Contacts Main Table].EastAyrshire) AS [Sum Of
EastAyrshire], Sum([Contacts Main Table].EastDunbartonshire) AS [Sum
Of
EastDunbartonshire], Sum([Contacts Main Table].EastLothian) AS [Sum
Of
EastLothian], Sum([Contacts Main Table].EastRenfrewshire) AS [Sum Of
EastRenfrewshire], Sum([Contacts Main Table].EdinburghCity) AS [Sum
Of
EdinburghCity], Sum([Contacts Main Table].EileanSiar) AS [Sum Of
EileanSiar],
Sum([Contacts Main Table].Falkirk) AS [Sum Of Falkirk],
Sum([Contacts
Main
Table].Fife) AS [Sum Of Fife], Sum([Contacts Main
Table].GlasgowCity)
AS
[Sum
Of GlasgowCity], Sum([Contacts Main Table].Highlands) AS [Sum Of
Highlands],
Sum([Contacts Main Table].Inverclyde) AS [Sum Of Inverclyde],
Sum([Contacts
Main Table].Midlothian) AS [Sum Of Midlothian], Sum([Contacts Main
Table].Moray) AS [Sum Of Moray], Sum([Contacts Main
Table].NorthAyrshire)
AS
SumOfNorthAyrshire
FROM [Contacts Main Table];

Thank you very much for your time - as we are a small charity we
really
cannot afford an engineer to do this!!!

Kind regards
lesley

--
Many thanks
Lesley


:

Lesley

There's a very good chance (though you haven't explicitly described
this)
that your table is set up like a spreadsheet! Let me ask, are
those
"32
number items" each an individual field, set up as a Yes/No data
type?

This would be one way you could add up the fields and get a
negative
number
(since Access stores "True" as -1).

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

My database has 32 number items (scottish local authority areas)
within
it
and when I do a count in the query I created, 30 of the 32
display
as a
minus
number. I have compared the properties of the minus ones to the
ones
which
are not displaying minus and they seem to be identical in
everyway.
I
don't
want any of them to display minus.

I am using Access 2000.

Can you help me?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top