AUTHOR'S MAIN SITE  /  TABLE OF CONTENTS for Open Office database tutorials.
Delicious.Com Bookmark this on Delicious    StumbleUpon.Com Recommend to StumbleUpon

Open Office Base (database) Tutorials

Pulling values from several places, doing calculations, generating reports

You may find that the database included in OpenOffice (and Libre Office, I believe, but I make no procises for that variant) delights you as much as it has me. This page tries to help you use it.

Remember that Open Office, including ooBase, is free. Don't let that fool you, though. Big organizations, governmental and civilian, have adopted it as their standard office suite... and saving million$, but still Getting The Job Done. And making things easy for users on different platforms... Linux, Mac, Windows all spoken here!

There's more about ooBase in the main index to this material. Adabas? Star Office? Ancient history.

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is fully explained, and there are more tips, at my Power Browsing page.)

Page contents © TK Boyd, Sheepdog Software, 7/15.



Where we are going

A little database to demo certain tricks

Nominally for someone selling on eBay and Amazon, but many points of general applicability. It will just be for keeping track of money made. Both online markets give sellers good stock tracking tools.

You can download the "finished" database, "FDB021.odb" ("Free Data Base, example 21")... but I would recommend ALSO "building" it, step by step, as you read the tutorial. I think you will be amazed a the wealth of details which come to your attention, if you do.

The user will enter a record for each sale made. From time to time, small additions will be needed to a table recording what taxes are levied by various states. As often they wish, users will run two reports. One will give profits from eBay sales, the other profits from Amazon. Why two? The rules about fees are different in the two marketplaces. Although I won't address it in depth here, it would be easy enough to make the report give profits for a given range of dates.

I'll concentrate on the tables I set up, then the query and reports needed. I hope you will be able to infer some of the whys and wherefores as we go along. You will have to take on faith WHY the tables are as they are for a bit.

One of the challenges of database development is that you have to start with the tables, but what you need in the tables is determined by what you want to achieve with your reports. You have to DESIGN from the "report" end, but DEVELOP from the table end.

Many things can be done differently... you might want to put the price in as dollars and fractions of dollars, instead of as pennies, for instance. But do it my way while working through the tutorial, if you want The Easy Life. THEN do your version, for your wants. This is just a STARTING POINT for your own database. One thing I have left out: The seller's cost in the goods sold. Adding this would be trivial. Just another field in Sale, and another term in the calculations. (I didn't want to clutter this with "details".)

In all of the following, by the way, things are often case sensitive. If I say call a field "Price", don't use "price"... if you want a quiet life.

Tables in database...


Main table... "Sale"

SaleID: Primary key, unique identifier made up of the date of the invoice, yymdd (5 and always 5 characters. Oct, Nov, Dec as a,b,c), Plus a letter, to allow up to 26 sales per day. E.g. 15b072 for the second sale of 7th November 2015. That format will seem odd to many. It really does work very well, has many virtues.

Price:... in pennies. Before post and packing. Before taxes. And we are going to base all of this on the premise that the fee charged to the seller by the marketplace is "hidden" from the buyer.

TaxAu, for "Tax Authority", data type, Text(fix), length:2: This field will have entries like "NY", "CT", "nn". The first two are US states, where the sales taxes vary from state to state. The last is for "no sales tax payable on this". The values in this field will have to be present in the "TaxPayable" table, which we will come to in a moment.

Mkt, for Marketplace, i.e. eBay or Amazon: "e" for eBay, "a" for Amazon. I haven't done it, by the table should be set up to REQUIRE an entry, and REQUIRE "e" or "a" here. (Limiting the input to a range of values IS demonstrated in the case of the TaxAu field.

PP, for post and packing. Again: In pennies.


Tax rates table, "TaxRate"

This won't have many entries in it.

TaxAuID- Primary key: See note above about Sale.TaxAu. Also data type Text(fix), length:2

TRa- Tax rate. Enter 12 for a 12% tax.


Tables defined... what next?

Those are my tables. If I've got them right, the rest will be "easy".

First a simple (ha!) relationship...

Close all the tables, but not the database. From the main project management window, select "Tools/Relationships"

Add the "Sale" and "TaxRate" tables to the workspace.

Put mouse pointer on Sale.TaxAu. Press and hold left mouse button. Drag (nothing will move) pointer over to TaxRate.TaxAuID. Release mouse button. You may want to drag one of the little boxes standing for each table to a new position. When things are nice, you should see a line between Sale.TaxAu and TaxRate.TaxAuID, with "n" at the Sale end, "1" at the TaxRate end.

SAVE THE RELATIONSHIP!

You have just said that you don't want entries in the Sale.TaxAu, unless the entry matches an entry in TaxRate.TaxAuID. The "1" says that for a given value, e.g. "NY", you will only see it ONCE... in the TaxRate table.

Play with the tables... try to enter some data. Be sure to "set" any data you enter into TaxRate before trying to add a record in Sale which depends on it. That is to say here's what you do if you have a sale to PA to record, and you haven't put PA into the TaxRate table yet. I am going to instruct you to do it "wrong", so that you can see what happens in something that I often do, even to this day, and which can have you scratching your head.

Go to the TaxRate table.

Enter a record for PA, but, for now, DON'T do more than...

Enter PA in the first field, 6 in the second field. After typing the 6, just do nothing more in TaxRate.

Use your mouse to go back to the Sale table.

You'll get an "integrity violation" error when you try to leave the record, after you have "entered" it in the Sale table. Actually... you haven't entered it. You have only STARTED to enter it. Same thing with your PA record in TaxRate. Click "Ok" in the error message to make it go away. Go back to the TaxRate table. Click on a line OTHER than the "PA" line. NOW your entry has (fully) gone into the table.

Go back to the Sale table. You will find that NOW you CAN leave the line with the sale to PA in it.


Moving on...

Good! We've got out tables. Enter some data, or just content yourself with the data supplied in the download.

Provide at least....

Sale...

157181  5000 PA e 100
157182 1000  NY a 100

----
TaxRate...

PA  5
NY 16

Now... USING the data!....

Three steps...

a) Create a basic query. Queries pull data from one or more tables, and create a "temporary", "invisible", "inside the computer" table. THAT table will be the input for a report we will create in a bit. It is also where the Really Clever stuff happens.

b) Create queries on the queries!

c) Create reports.

Create first query

About 20% though the following, you may lose the will to carry on. The good news is that this is by far the worst part of this project. The "queries on queries" are easy, and the reports REALLY easy.

It may console you in your misery to remember that while I was writing this, I somehow lost my query, and had to re-write it from scratch. Happily, these notes were done, to follow. Whew.

I INITIALLY created query with the wizard. Pulled in...

All fields from the Sale table
TaxRate.TRa

Use the wizard's defaults for all other aspects of the table, up to the name it is to be saved under. Save it as SaleDataForProceeds.

Now, from ooBase main project manager window, right click on the query, open it for editing.

You should get a "two pane" type window, rather like the one we used to set up the relationship. But this is DIFFERENT!

The "two forms with a green tick" button will run it. Click that. You should now have a three pane window... three horizontal bands, rather like the flag of Spain (without the crest!) You may have to adjust their vertical widths by dragging at the boundaries.

A vast number of records in the result, because, broadly speaking, you get everything multiple times, covering all possible combinations.

In the band with little boxes showing the fields of the two tables, as we did when setting up relationships, create a line from TaxRate.TaxAuID to Sale.TaxAu by dragging.. Your line won't have the "n" and "1" we saw before.

Run the query again, and you should get a sensible result...


SaleID  Price  TaxAu  Mkt    PP   TRa
157181   5000     PA   e    100     6
157182  10000     NY   a    100    16

In another, not too hard to reach, scenario, you would have been given NO records! Oops! But, again... we WOULD BE progress!

In the other scenario, which matters to you because you may "go there" one day: First too many records, now too few. Sigh.

I can't really explain it, I haven't got the words.

(BIG brownie points to anyone who emails to tell me where THAT line comes from!)

We have created a "join" for our query to use. In OUR case, it is already of the right sort. I will now show you how to tweak it, make it the RIGHT sort of join, for the day when you need that.

The join, by the way, doesn't, by the way, say or enforce anything about the data in the tables. It is simply a part of the query, part of the rules for what will be put into the temporary table the query produces.

Right click on the line representing the join between Sale.TaxAu and TaxRate.TaxAuID.
Select "Edit".

You will, I expect, see that you currently have a natural inner join. Good! That's what we need. Another time, it might not already be selected for you, and I'm not sure of the right name for the join you want... but there is helpful text at the bottom of the Join Properties dialog. In our simple case, for the "inner" type join, the text says...

Includes only records for which the contents of the related fields of
both tables are identical.

Leave the "Fields Involved" ALONE, but
Leave the "Natural" box UNticked.

In a (slightly) more complicated scenario, the message at the bottom for what we want would be....

Contains ALL records from table "Sale", but only records from table
"TaxAuID" where the values in the related fields are matching.

This, as I say, I can't properly explain, but what I half understand from the text just given is at the heart of it.

Click "Cancel" to get out of looking at the join properties dialog, or "Ok" to complete the edit of the link, if you are exploring the effects of different choices.

If you have made any, save the changes to the query.

Run the query again. You should get still a sensible result! (Or get one now, if you have advanced into the complex circumstances.

What is a sensible result??...

You saw one earlier, I hope. It will look like a table. (It IS a table, but not in every way that you may assume.)

It will have the following fields. They should seem familiar....

SaleID
Price
TaxAu
Mkt
PP
TRa

Looking at what we have...

As I said, the table looks unremarkable, at first glance. But think about it...

The first five columns are displaying what's in the Sale table. Good! We need to know about our sales. But think about the last column, think about where the TRa value in each record has come from. THIS is CLEVER! In the table assembled by the query, the TRa value for each line is a number fetched from the TaxRate table, the RIGHT number to go with whatever TaxAu was specified in the record in the Sale table.

Don't worry if you don't see WHY that is cool. Read on. WHAT it is good for will be sufficient.

We've made a start. But we need to add another column (at least). This one will be even more "magic" than the ones so far.

First we need to take a moment to look in detail at the way the marketplaces charge for their services. I should add that what I am going to state here as "the way" is NOT necessarily EXACTLY what Amazon or eBay do. Even if it were, they could change it. But what I will describe COULD be their rules, and will show you the principles of putting together the reports, so that you can adapt the examples to your current needs.

Let's say that you only sell books on Amazon, and that they (Amazon) always adds $6.00 ("600" in the units of the database) to the "price", collects the sum of your price plus the $6.00 from the customer, and then passes on to you that, minus a flat fee of $5.00. (This, with different numbers, is what they did for a long time. Only fly in the ointment? The amount they added for p&p was insufficient to cover the actual costs to the seller. In some cases I lost money when I sold a book.) Under those rules, you don't need to enter a lot of the data you need to enter for the eBay sales. In fact, for an Amazon sale, you only need to enter a SaleID and a price! If only everything were so simple! (You only have to enter those two to find out how much you will receive from the sale. Why do you care what the customer would have to pay?)

Let's say that for an eBay sale, they charge you a percent of (Price+Tax+PP). This will be messy. Sigh. Life is messy. But we can do it.

A detail: For now, the percent will be hard-coded into the query. Changing it will be a matter of changing the query. Not rocket science, but it would be so much nicer... and I KNOW it CAN be done... if you could simply put the percent into a small (one record) table, and the query would fetch the number from there. Even nicer, but it would be a lot more work... what if the percent changes half way through the year, and you want profits for the year? Under my crude answer, presented here, you'd have to run two reports, one for the part of the year with the first rate, and a second for the other part of the year. If the "eBay percent" table also had an "up to this date" field, you COULD... with quite a lot of work, I suspect, have just one report, and it would apply the right percent to each transaction.

Back to our "simple" answer....

We're going to add two columns to the table the query produces. One will be "Proceeds By Amazon Rules", the other "Proceeds by eBay Rules". Then we'll make two reports, one to tabulate proceeds from Amazon sales, the other for the other.

What we are doing is very like what I explained in my tutorial "Calculated Fields in the OO database". If this tutorial is giving you a headache, maybe give that one a try, for (almost) the same techniques, in a slightly simpler scenario.

Still in the query editor, in the bottom pane, where you have multiple columns for the fields in the table produced by the query, you need to add what I will explain in a moment.

Take a second to get your bearings. Usually, fields are the ROWS (horizontal) in a table. Here it is one field to a COLUMN (vertical). But we can do this! We're computer people!

For a gentle start, not that it is worth MUCH, click on the first empty cell in the "Field" row. Enter Price+3. (If, as I hope, you entered "Price" as the name of the field in the Sale table, you enter "Price" here. If you used "price", use "price" here. The entry is case sensitive, in other works.) Don't be alarmed, or fight it, if Base adds quotes around the word Price before long. Save the query. Run it. The table that results should have gained a column, with 5003 and 10003 in the two rows for my demo data.

Now we will tweak the start we've made.

Add ProcAm as alias for the field we're working on.(For "PROCeeds from sale by AMazon rules"). Don't worry about the GeekQuotient of that name. Users won't see it, and it is good for our wants.

By the way: It will seem odd, but it is the only way to our goal that I know: We will calculate what our proceeds WOULD be, by BOTH marketplaces' rules for each sale. Of course, only one of them, in each case is of any use to us in the long run.

Go back to the "Field" field (!) for the column we've just added.

We said that Amazon gives the seller the price the book sold for, excluding tax, excluding p&, i.e. the number in our "sold", MINUS a net $1.00 ("100").

So change the formula in the column we are working on to "Price"+100.

Easy! And reasonably "real world"... if you add in at least one column to the Sale table, and do what's necessary to incorporate what's in it. The column would be "cost of goods+p&p expenses". (If it were me, I would split those out into TWO columns, one for each.) As presented, our database tells us what money we get from Amazon and eBay, who both take their fees at source... we never see them. But it doesn't take away from our flawed "proceeds" figures our OTHER costs. As I said... providing for those is trivial, and was only left out of this to reduce clutter.

Save and run the query again, and you should see good "Would get from Amazon" numbers.

Now we'll add a column for "Would get from eBay"

There's nothing "new" in what we need to do for this. It is just a little more complex in the details of the formula because of the rules we said eBay applies.

A detail: Under eBay, WE decide how much p&p is collected from the customer, on our behalf.

Start another column. Call it ProcEB, and, just to get it started, again make the field "Price"+3. Save your changes. See that you get what you should...


SaleID  Price  TaxAu  Mkt    PP   TRa  ProcAm ProcEB
157181   5000     PA   e    100     6    5100   5003
157182  10000     NY   a    100    16   10100  10003

(By the way: the alignment of the data won't be as above. But don't fuss with it. Users... and before long you... won't be seeing the data in the table in this form.)

That's the "hard" stuff done. Well. Intellectually hard. Now we have to fuss with the details of the formula for what eBay will send you.

We said they would add the price, the p&p together. Taking our sale in PA for an example, that gives us 5100. Then add the relevant tax. 6%.... IF the goods are SENT to an address in PA. (Something the database doesn't currently deal with... we're assuming that it always is added. The way around this is to enter "nn" as the tax code for parcels not going to the state the seller is in.... which means, I guess, that much of what we've done is unnecessary! A NY seller would need the NY entry in the TaxRate table, and the "nn" entry... and no others! Oh well.)

(Shake my head in annoyance, and to clear cobwebs. Back to what I was saying....)

We had 5100 so far. Assume a sale from PA to PA, and add the 6% that our table gives us. (The table is still worth having, as it makes changing the rate of tax charged (when it IS charged) easy.)

That brings us to 5406, the tax being 306*.

Let's say eBay would take 10%. Your proceeds would be 4866 or 4865, depending on how eBay's computers are programmed to cope with the fraction of a penny they are due.

Remember, we are working on pennies, and we have entered percents not as fractions, but as whole numbers.

I could give you "the formula"... but I want you to see how you GET the formula. There are multiple routes you could have taken. This is how I got to the result...

First I made the column display the tax to be charged. Between various things, that was a little tricky, we are working in pennies, and have entered percents not as fractions, but as whole numbers. (Working differently brings on other problems!)

You have to multiply by 100 and then divide by 100 in various places because, since we decided to work with integers (whole numbers), fractions, if they should arise, are just thrown away.

(You can change the width of the columns in the bottom pane of the query design window, by the way... just drag them.)

The formula you need is....

(("Price"+"PP")*"TRa")/100

YOU DO NOT, by the way, want to put something like this in your main tables. That is a thought that occurs to many people. And it works. Sort of. For a while. And then you finally conclude that "the experts" were right, and you have to spend hours cutting that stuff OUT of your main tables again.

Now we build in another layer. We build up the formula to show what the price plus the tax would be. Parentheses, (.....), are your friend, but you have to use them with care. Start by putting all of what you had before inside a pair of parentheses. Then add "Price"+ "PP"+ in front of it. The formula becomes...

"Price"+"PP"+((("Price"+"PP")*"TRa")/100)

That should give 5406. At this point, it would be really nice if we could "put" that somewhere, let's call it "SoFar", and could use a reference to "SoFar" for what we need next.... a formula for what eBay gets. If we could, "next" would simply be...

(SoFar*10)/100

(*10/100, with the parentheses shown.. they can matter!... because eBay, we said, takes 10%, and because we aren't doing fractions. (It would be *9/100 if eBay were taking 9%.))

Sadly, I know no way of creating a "SoFar". Happily, although the result is messy to look at... not a trivial flaw... heaven help us if there is an error hiding in it... with the computer's cut and paste, we can create the following relatively easily...

(("Price"+"PP"+((("Price"+"PP")*"TRa")/100))*10)/100

... which should give 540 or 541, depending on how the fractional penny is dealt with. eBay was due 540.6. In this case, ooBase returned 541... it rounded, it didn't truncate. More clever than I thought!

Now that we know what eBay will take, we can calculate what we will get from...

"Price"+"PP"-WhatEBayWillTake

Of course, the "WhatEBayWillTake can't be written like that... we have to use the great long thing we worked our a moment ago. Start creating the new line by putting parentheses around what we had before, to avoid "order of operations" problems.

(It should come up as 4559, shouldn't it? Always create some test data, make sure that the computer gets the same answers, as you go along, as you get with a pencil and paper.)

By the way... despite seeing no end of numbers, we haven't seen the number the customer would actually pay, have we? Not if the customer was paying tax on the deal. But we don't need that, do we? Not broken out and displayed. It was USED in our calculations, but not left visible in our final result.

WHEW! Who would have thought it could be so tedious? But not, in essence, "complicated". If you take it step by step.

Hurrah! At last!

At last we have the main QUERY on which we will build our other queries, on which we will build our REPORT!

(We're DONE with the query, once we get the report built. Until the rules the marketplaces use when calculating their take change, anyway. But if those rules DO change, we won't need to change the report, or the other queries, when we change the underlying query.)

Query as basis for eBay report

Use the query wizard again, to start the query....

Step 1: Make sure the "table" selected as the basis is SaleDataForProceeds

The ESSENTIAL fields are...

SaleID
Mkt
ProcEB

(You might want others... Price, maybe?)

Step 2: DON'T apply a sort at this point. (We will do the sorting during the report generation. I THINK it was a sort applied here that got in the way of other things, later.)

Step 3: Search: Use Match Sale...Mkt / is equal to / e

Steps 4-7 (you won't visit them all): Use defaults.

Step 8: Call query "DataForEBayProceeds"; click Finish.

If you put only my two test records into Sale, you should see just the record for 157181 in the result of running the query. Close it.

Repeat the above, with the obvious (I hope!) small differences to create a query called DataForAmazonProceeds

Hurrah! The hard work is done!

All that is left is creating two simple reports.

The report we need will not be very "clever". You might want to visit some of my other "make a report from a query" tutorials, as I am tired, hungry, and short of time here!

As I mentioned earlier, we are going to create TWO reports... one to report proceeds from Amazon sales, one to report proceeds from eBay sales.

Here we go. We'll do the one for eBay first...

Select "Reports" in the left hand pane of the ooBase main project manager window.

Click on "Use Wizard to create..."

Make sure that the "Tables or Queries" pull down has "Query:DataForEBayProceeds" selected.

From the fields on offer, move to the "Fields in Report" column....

SaleID
ProcEB

Click "Next" to get to step 2 of the wizard, at least as it was at OO 4.1.1

Change the ProcEB label to "Proceeds before cost of goods and p&p"

In step 3, just click next. (Invoke no grouping.)

Step 4: Sort by SaleID. If you used my scheme for assigning sale IDs, this will give you the data in chronological order. (I think they would appear in the right order, anyway, as we put a sort order in place with the query... but might as well be sure.)

Step 5: Layout: Tabular

Step 6: Title "EBay Proceeds", dynamic report, create now.

That will create a good start to a report. It is nothing special. It will LOOK awful.... but does it REALLY matter, for instance, that the amounts are left justified, not right justified? (Fixing that is trivial.)

You will want to tidy up the report's layout14c, make it more elegant. For that I leave you to your wits, or to my other tutorials. Yes...you CAN have it add up the total of all the proceeds.

Before you play with the report too much, go back to your "Sale" table, add more records.

The report for your Amazon proceeds is created the same way.

If you want a report on the proceeds for a given span of time, you achieve that by putting criteria in the relevant query (DataForEBayProceeds, or DataForAzonProceeds). You can have TWO "SaleID" columns in the query design. Leave "Visible" for one of them un-ticked. Put criteria like...

>'14c999'
<'158000'

...into the query design, and your report will only list, for the example criteria given, sales from after the end of 2014 until before the start of August 2015.





Editorial Philosophy

I dislike 'fancy' websites more concerned with a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Not everyone has fast broadband.

I present this material in a format aimed at to helping you USE it. There are two aspects to that: The way it is split up, and the way it is posted. Details at my page about how the material is split up and how it is posted.

Please remember the material is copyright. (TK Boyd, 2006 and later) The procedures in the page just cited are suggested only for convenient personal use of the material, however, also....

Feel free to use this information in computer courses, etc, but a credit of the source, quoting the URL, would be appreciated. If you simply copy the pages to other web pages you will do your readers a disservice: Your copies won't stay current. Far better to link to the original pages, and then your readers will see up-to-date versions. For those who care- thank you. I have posted a page with more information on what copyright waivers I extend, and suggestions for those who wish to put this material on CDs, etc. (There is at least one prison using the material for inmate education. Situations do exist where good internet connections are not possible!)

Translations are welcomed. Tell me about yours, so I can post links to it. (More information at the page about copyright waivers.)




Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE


If you liked this ooBase tutorial, see the main index for information other help from the same author.

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.






Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org


One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .