Its me again - SUM Function

[Adam1979]

Ok , I have a table called cons. I have a field in cons called NOP
I would like to add the numbers together in this field and so used

Select SUM(NOP) from Cons

This cases an error "datatype mismatch in criteria expression"

What is going on ?

Adam

I have looked at the database and the datatype is set as text. Could this be the problem ? If so , can I change the datatype without loosing my data ?

 

[CraigDolphin]

Is NOP a numeric field like Integer or is it a text field?

 

[Adam1979]

NOP is set as a text field . I get the feeling this is bad news ,unless there is a magic statement that will read the text as numbers ?

 

[CraigDolphin]

So your query is currently asking the equivalent of:
a + b + c = ?

About the only thing you can do without changing the text field into integers (you'll lose any non-numeric data doing that) is to create a query of your table using MyNewNOP: CInt([NOP])

Then make a query of this query that sums MyNewNOP.


However, if someone has entered something non-numeric in that field you might get an error.

Spend a little more time thinking about your table and fields in the setup phase to avoid this kind of problem. If you ever want to do math on a field it should be a strictly numeric field from the get-go.

 

[Adam1979]

So what would the staement look like ?

I admit , I wasnt thinking about the database when I created it , I know now though. All the entries in the NOP field are numbers , how can I add them all ?

 

[CraigDolphin]

Um. I thought I just told you how?
SQL would look something like:

Query 1
Select CInt(NOP) as MyNewNOP from Cons

Query2
Select Sum([MyNewNOP]) from Query1

 

[Adam1979]

Soz , Me a ikkle bit fick

What I have done isopened the database , created a new colum called NOP2 , copied the data from NOP to NOP2(numeric data) , deleted NOP and then renamed NOP2 to NOP , Problem solved.

Im sorry for not understanding I do have a thick book "complete ASP , ADO and XML but I have never read it. Like I said earlier , because of the wizard type software available , it is easier to use that then to really understand what is what , perhaps I should take up bedtime reading.

Thank you so much for your help

You truly are , a super star

Ads

 

[CraigDolphin]

Well I have been told that books are not very educational if proximal-osmosis is the chosen method for using them

As a guide to your reading there's a lot to be learned online and on this forum. There's a lot of book recommendation threads (search and ye shall find) for access. And a topic that you should come to grips with before you design your first db should include table normalization. As a rule of thumb, designing your tables and determining the relationships between them should take up a large chunk of your design process (30 - 50%).

Your tables and table-relationships are the foundation of your db. If you get this part wrong, then rebuilding all your data to fit the new model, with existing data in the old model can be a disaster. By comparison, if you get the forms wrong, oh well. Fix it or build another form. The data itself will still be good.

As an illustration: I was asked to design a database for some lawyers. Took me about two weeks to figure out and develop the db relationships, and build the forms etc. The existing data was not in a properly normalized relational structure. There were thousands of records in two existing data sources. It took two and a half months to reconcile the two data sources with each other, and populate the new database. All data manipulation. Horrible. To be avoided at all costs.