Source language: Translate to:

Summing a Database Ideas

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Summing a Database Ideas

Postby datadon » Mon Dec 05, 2011 8:30 am

I'm looking for the best way to do this:

I have a spreadsheet with lets say 3000 records. There are 5 fields, the first is an item no. Of the 3000, lets say there are 700 unique numbers in the item no. field. That is, there are also a lot of duplicates. something like this:

10001
10001
10002
12010
13054
12010
10002
10001

etc.

What I want to do is to total another columns values for each of the unique numbers in the item no field. In the example, I want to total another field for 10001 and then I want to total 10002 etc. I do not want to miss totaling any of the item nos. I then want to take the results and put them in another file. That part I can do.

Here is what I know I can do. I can create a list of unique numbers from the item no field. I can then loop and query on each of those items and the use dbpsum to get a total for that query and write it to the other file. This can be a bit time consuming.

Is this the best way to do this?
Don

'If you want to get a brontosaurus from 'a' to 'b' then you ride the dinosaur - you don't carry it!'
User avatar
datadon
 
Posts: 389
Joined: Sun Apr 03, 2005 7:55 pm
Location: Lorena Texas

Postby Gaev » Mon Dec 05, 2011 8:51 am

Don:

I don't know which SQL commands are supported when your Database is a spreadsheet (assuming you are accessing it via NeoBookDBPro's ODBC connectivity) ... but here is an example from the MySQL documentation that will help ...
The table's contents can be summarized per year with a simple GROUP BY like this:

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 | 4525 |
| 2001 | 3010 |
+------+-------------+


Adding WITH ROLLUP after the BY year will give you an extra Row (that calculates the grand total) ... for more info ... http://dev.mysql.com/doc/refman/5.5/en/ ... fiers.html
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby datadon » Mon Dec 05, 2011 9:47 am

I gave it a try, but doesn't seem to want to work with a spreadsheet. Spreadsheets have some limitations for sure. That would certainly be an output that I would like to see.

It tells me I need to open a dataset. I tried using the
open dataset
command and it doesn't recognize it.

Maybe someone has an idea about how to make that happen with a spreadsheet.

Thanks as always
Don

'If you want to get a brontosaurus from 'a' to 'b' then you ride the dinosaur - you don't carry it!'
User avatar
datadon
 
Posts: 389
Joined: Sun Apr 03, 2005 7:55 pm
Location: Lorena Texas

Postby Gaev » Mon Dec 05, 2011 11:46 am

Don:
I gave it a try, but doesn't seem to want to work with a spreadsheet.

Assuming Access databases support these features, you could ...

a) Export your xls spreadsheet to a csv file (only need the two fields (columns) in question).

b) dbpCreateAccessDatabase with the same two fields.

c) dbpImportFromCSV.

d) do the "SELECT year, SUM(profit) FROM sales GROUP BY year;" using the dbpExecSQL command.

Steps (b), (c) and (d) can be scripted in NeoBook.

If you have Excel installed on the same machine ... this page http://stackoverflow.com/questions/1858 ... mmand-line talks about an automated method of doing (a)
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby datadon » Mon Dec 05, 2011 12:57 pm

I converted it to Access, but I still get the message about not working on a closed dataset. That is, this code:

Code: Select all
dbpExecSQL "Time" "SELECT taskact, SUM(hours) FROM hours GROUP BY TaskAct; " "[allofthese2]"


I've searched the web on this and the replies really stink. Seems like I remember something on this a while back but can't find it.

Found the link here: It was you.

http://www.neosoftware.com/forum/viewto ... ed+dataset

Let me read it now and see what it says....

few minutes later....I tried the status indicators and it says connect and browse, so i guess that is good.

The problem does not appear to be solved on this link, any others?
Don

'If you want to get a brontosaurus from 'a' to 'b' then you ride the dinosaur - you don't carry it!'
User avatar
datadon
 
Posts: 389
Joined: Sun Apr 03, 2005 7:55 pm
Location: Lorena Texas

Postby Gaev » Mon Dec 05, 2011 5:34 pm

Don:
I converted it to Access, but I still get the message about not working on a closed dataset.

When do you get this message ? ...

a) when you try to invoke the dbpExecSql command ? ... in which case check that ...

- the Table called hours has been dbpOpenTable'ed before you invoke this command
- [allofthese2] contains the name of the temporary Table you want to use for storing the results of the query

Also noticed that you use taskact and Taskact ... these things are case sensitive ... assuming taskact is the correct name of the field, perhaps you might try ...
Code: Select all
dbpOpenTable "Time" "hours"
dbpExecSQL "Time" "SELECT taskact, SUM(hours) FROM hours GROUP BY taskact" "TimeSummary"


b) or when you subsequently try to view/fetch the results data ? ... in which case did you first dbpOpenTable the temporary Table in question ?[/b] ... from the Help file ...
A temporary table created with dpbExecSQL can be opened with the dbpOpenTable and displayed with dbpShowGrid actions.


Finally, a point I have made on these forums before ... avoid using generic words (like time, hours) when naming databases, tables and fields ... sooner or later you will run into a conflict with a Reserved Word ... something like TaskTime or TimeSpent ... and TaskHours or HoursSpent ... are much safer.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby datadon » Tue Dec 06, 2011 1:09 pm

Finally as usual, with Gaev's patience I am able to accomplish what I am looking for. I was misunderstanding the SQL code strucuture. Hours was not the table, but a different name. It now works. The results show up in the browse window. I was expecting them to show up in a variable as text. Either way works. It is very fast.

Thanks again as I learned something I did not know 3 days ago.....now if I can just remember it 3 days from now.
Don

'If you want to get a brontosaurus from 'a' to 'b' then you ride the dinosaur - you don't carry it!'
User avatar
datadon
 
Posts: 389
Joined: Sun Apr 03, 2005 7:55 pm
Location: Lorena Texas

Postby Neosoft Support » Wed Dec 07, 2011 12:40 pm

Thanks Gaev!
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest