I have successfully gone down to where the instructions say GO TO THE QUERIES SECTION. I could not find the Queries section to create a new query in SQL view
On Mon, Oct 5, 2009 at 2:28 PM, Andreas Saeger <[email protected]>wrote: > Walter Hildebrandt wrote: > >> On Mon, Oct 5, 2009 at 9:49 AM, Andreas Saeger <[email protected] >> >wrote: >> >> Walter Hildebrandt wrote: >>> >> I get two columns once every week which are your Value and Name columns. >> That means in the second week I have four columns. In the second week, >> the >> first column is a Value column and the second column is a Name column, >> both >> of which are from the first week. The third column is a Value column and >> the forth column is a Name column from the second week. In the second >> week, >> I want to find out what Names appear in the 4th column and do not appear >> in >> the 2nd column. >> >> I will continuing adding two new columns every week. In the 4th week I >> will >> have 8 columns and I will want to see how many Names appear in column 8 >> that >> do not appear in column 2. >> >> There will be other weeks when I want to compare a Name column to the >> Names >> in column 2. >> >> Copying and Pasting is working is now working with the data. I copy both >> the Value and the Name for the Name that appears for the first time do a >> Paste. This means I use the Name columns to see what names appear for the >> first time and then select both the Value and Name columns and do a Copy >> > >> Paste. >> >> Can this be done with the data pilot? I am assuming that the data pilot >> would be easier for me to learn than to start from scratch and learn Base. >> >> > All list processing software tools I'm aware of use the concept of > normalization: > >> http://support.microsoft.com/kb/283878/EN-US/ >> > > In short: List processing software works best with tables that grow by rows > with a constant amount of columns. > Spreadsheets have absolutely no concept of normalization and experienced > power users may derive all sorts of information from arbitrary table > layouts. This dark magic may be required when you have no control over the > incoming data layout (for instance data copied from a selection in a > browser window). > In this particular case we get controlled input data where the first column > always contains numbers and the second column contains only text. > All the built-in spreadsheet tools assume normalized lists, the data pilot > in particular. > > For your problem I suggest that you collect all the weekly incoming data in > a database table together with an automatic time-stamp. > > So your table looks like this: > Value Name Timestamp > 13.5 | Walter | 2009-09-29 12:45 > 12.9 | Andreas | 2009-09-29 12:45 > 33.1 | Peter | 2009-09-29 12:45 > 3.5 | Walter | 2009-10-05 09:13 > 2.9 | Andreas | 2009-10-05 09:13 > 3.1 | Peter | 2009-10-05 09:13 > > You can derive all kinds of combinations from this vertically growing list > with week numbers and everything. > There is no reasonable limit of rows and you can easily compare sub-sets > with each other. In spreadsheets you can calculate with the same database > data. > I created this database table for you and you get the same when you follow > this: > - menu:File>New>Database... click the [Finish] button and store the new > database to some place where it can stay for a while (my private cash > register started in 2003). > - menu:Tools>SQL... paste the following command: > CREATE TABLE "Data"( > "Value" DECIMAL(10,2) NOT NULL, > "Name" VARCHAR(50) NOT NULL, > "Stamp" TIMESTAMP(0) DEFAULT CURRENT_TIME NOT NULL, > "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL > PRIMARY KEY) > - push button [Execute]. "Command executed successfully" should be the > response. > - Close the command line tool > - menu:View>Refresh tables (tell Base that we modified the database > structure "behind it's back". > Now you see the table named "Data" we have generated with a simple > copy&paste. > Select it and call > - menu:Edit>Edit... > This reveals the inner structure of the table. > All fields are mandatory ("Allow empty values"=No in the graphical tool, > NOT NULL in the above SQL statement). They are not allowed to be empty. The > database will refuse to store rows with missing values. > Numeric field "Value" is a 10-digit decimal with 2 decimals behind the dot > ("Value" DECIMAL(10,2) NOT NULL). This is a commonly used type for > currencies. > Field "Name" is a case-sensitive text with a maximum lenght of 50 > characters. > You may change the field types for your own two fields, but leave "Stamp" > and "ID". > "Stamp" gets the current time as default value for each newly created > row("Stamp" TIMESTAMP(0) DEFAULT CURRENT_TIME NOT NULL). One of the worst > limitations in Base is that this default value is not accessible through the > graphical user interface. > The ID column adds a unique ID number to each new row. It is required for > technical reasons (it's a "primary key"). Just ignore it. > ################# > - Copy this: > SELECT YEAR( "Stamp" ) || '-' || WEEK( "Stamp" ) AS "Week", "Name" AS > "Client", "Value" AS "Amount", "Stamp" FROM "Data" ORDER BY "Stamp" ASC > > Go to the queries section, create a new query in SQL view, paste the > command and save the query. > Then open the query and it should look like: > Week Client Amount Stamp > 2009-41 Mary 23,4 05.10.09 21:08 > 2009-41 Paul 12,91 05.10.09 21:08 > 2009-41 Peter 33,11 05.10.09 21:08 > > A query can recombine a table in any order of rows and columns, using any > column names, sort order and filter criteria. It is possible to combine data > from more than one table. > ################## > When you receive new spreadsheet data, all you've got to do is this (about > 5 seconds): > - Open the downloaded Excel file and the "beamer" (hit the F4 key). > - Expand your database, so you access the "Tables" and "Queries" sections. > - Select the data range including the column labels and drag the range onto > the "Tables" in the beamer's left pane. > - A wizard pops up where you type in the name of your table ("Data") and > choose option "Append Data". In the next step you can adjust the 2 sides so > the respective columns of the spreadsheet and database are side by side. I > arranged the table's column order so you don't have to adjust anything. > When I drag the following 4 rows from a spreadsheet into table "Data", > "Amount" corresponds to database field "Value" and "Client" contains the > data for field "Name". > > Amount Client > 33,11 Peter > 12,91 Paul > 23,40 Mary > > > You can disregard the other two fields unless you want to import a > timestamp other than the current time (import last week's data). In this > case you fill a third spreadsheet column with the wanted date and import 3 > columns to override the default time stamp. > ###################### > OK, I assume you have some data in your table and you see the table and the > query in the beamer window. > - Open a new spreadsheet. > - menu:Data>Data Pilot>Start... > - Create from data source and choose the name of your source and the name > of your query. > - Drag "Week" (calculated from "Stamp") into "Column Fields" > - Drag "Amount" ("Value" in the DB) into "Data Fields" > - Drag "Client" ("Name in the DB) into "Row Fields" > [More Options] > Specify A1 of a blank sheet as target. > Check all the other options. They don't hurt. > ########################## > - Open the next Excel file > - Drag the list onto "Tables" and append data to table "Data" > - Refresh the pilot > > We set up some definitions for a table a query and a data pilot once. Then > we can fill the defined table with new data by means of a simple drag&drop > operation once a week. > The suggested data pilot shows in a cross-table the amounts for every > (vertical) name by (horizontal) weeks. > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > >
