Doing interesting things with queries: multiplying two fields together, and summing detail records

Here are a couple interesting things you can do. Let's say you've got a bunch of customers with a few invoices each, and you just want to know how much each of them has spent. You take your one customer record and you add many invoice records together to get the total they spent on all invoices.

You can also multiply items: let's say your invoice table has the item price, and number of items purchased on the invoice, but not the total per invoice. You can multiply item price by number of items to get the total.

Open the database.

Click on the Queries icon and choose to create a query in Design view.

You'll get a window where you can choose which table to use. In this example I'll use the invoices table. Click Add to add the table.

Then double-click a few fields to add them. I'm going to add a few here, then delete some later.

Now I've got the fields added.

If I click Run Query then I just get those fields.
 

Note I have the unit price and number of items but not the total per invoice. Here's how to do that. Just type as shown in the far right column. It is case sensitive.

Clicking Run Query gives the results.

Which look like this--you get the Alias you typed, not the multiplication. Note however that it is not formatted as currency in this example. If you want to change it you can right-click on the column heading, choose Column Format, and select a currency or number format.

And here's what that looks like.

Here's a note. You don't have to have the fields you're multiplying in the query. You can unmark the checkmarks as shown and they won't show up. Or you don't even have to have them at all; you can right-click on any column head and choose Delete to remove it from the query. (Do this in the bottom area of the query where you do the defining, not the top where you see the results.)

Now you're thinking about doing that sum thing, adding up all the totals for all the multiple invoice for each single customer. For this, you need to have just the customer ID (one) and the calculated field (many for each one customer). So I deleted everything but those two columns.

Then for the single field I selected Group in the function list, and I select Sum for the many field, as shown.

Run query, and here are the results. Note that now each customer is only listed once, and the total for their invoices is calculated.

 

Comments

[**=runescape power leveling=**=]

**=runescape power leveling=**=
runescape power leveling Clearly about the changes between Death Knight, please visit it in selling wow gold.They are able to provide a world of runescape power leveling service at first time when it came out.
★* ★ ★

[Björn Gedda]

Hi
This was very usefull bau I also need to do sum different fields as in
"Quantity" + "UnitPrice" but the "+"-sign doesn't seem to work in this way. Can you tell me why and possibly how it should be done?
(ie: the table has six columns for different additional costs to each unit and I want to sum the costs so I know what each unit has cost in total)
/Björn

[Solveig]

Hi Bjorn,

I think the fields have to be the same data type to add together...or at least check the data types and be sure that they're both numeric. Are all six additional costs the same data type?

I just tested it and adding two decimals, two integers, an integer and a decimal, etc. all work fine.

[Mark]

Do you have a list of what functions can be used? I have something I'd quite like to concatenate, for example.

[Björn Gedda]

Hi Solveig,

I think I foumd the problem. It was that I have empty values instead of zero. Apparently 3+empty=empty (as do 3*empty).

It seems that I have to fill all my empty values with zeros but since
Cost=0 is not the same as "No cost yet", I would appreciate i there was a solution where I could leave cells empty in the form and still do the math.

I tried to up a image but failed so I put it here instead:
http://picasaweb.google.se/bjorngedda/UntitledAlbum?authkey=Gv1sRgCKib8vmHuM2FRg&feat=directlink

[Solveig]

Hi Bjorn,

One thing you can do is (and this is a pain) do an IF statement for all your calculations, if result is not null then put in the normal solution, else 0.

[Björn Gedda]

Thanks Solveig. I think that'll work.

Thanks for the help.

[lotro powerleveling]

Do you want to play WoW game?Welcome to our website for lotro gold
and lotro powerleveling
service.You can come and have a look!

[cheap lotro gold]

Thank you very much. I am wonderring if I can share your article in the bookmarks of society,Then more friends can talk about this problem.

[michael jordan shoes]

For further information about the Warhammer CD-key and Warhammer Timecard, Please keep an eye on air jordan shoes.
For more information please keep an eye on cheap jordan shoes .

[wow power leveling]

http://www.gamegoldme.com/
http://www.wowgold-powerleveling.com/
http://www.wowgold-wow.com/
http://www.wowpowerleveling.me
http://www.watchrolexshop.com
http://www.wowgold-wow.com/wow-power-leveling
http://rs-runescapegold.com/
http://www.watchrolexshop.com/wow-power-leveling/
http://www.cheap-lotrogold.com/
http://www.globalsale.me/Aion-gold-083.aspx
http://www.cheap-gamegold.org
http://www.gamegoldvip.org
http://www.globalsale.me/

[michael jordan shoes]

Your article very interesting, I have introduced a lot of friends look at this article, the content of the articles there will be a lot of attractive people to appreciate, I have to thank you such an article.