Calculation in Query

Creating tables and queries

Calculation in Query

Postby Itsmemike » Sun Nov 08, 2015 9:08 am

Hi

Can someone please tell how to enter a sum calculation in a query for example:

I have fields for Units Ordered and Units Received, I want to deduct the Units received from the Units Ordered. I would think it was something like example below but that dose not work.

Sum("UnitsOrdered"-"UnitsReceived")

I,m new to Open Office and only been on it for a few days.
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Calculation in Query

Postby F3K Total » Sun Nov 08, 2015 9:54 am

Hi,
depends on if you want to group the numbers by anything or not. Most simple would be:

Code: Select all   Expand viewCollapse view
Select "UnitsOrdered"-"UnitsReceived" as "Rest" from "PurchaseOrder"

or grouped e.g.
Code: Select all   Expand viewCollapse view
Select SUM("UnitsOrdered"-"UnitsReceived") as "Rest_Supplier" from "PurchaseOrder" GROUP BY "SupplierID"

R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: Calculation in Query

Postby Itsmemike » Sun Nov 08, 2015 12:49 pm

Thanks for your reply F3K Total I have tried both of your suggestions but can,t get them to work. Tried different things now run out of ideas, very frustrated.

I,ve attached my Db as I have moved on to the next stage of the development where you will see I am working on the inventory. I have also attached screen shots of a Db in access which I did showing what I,m trying to achieve.

Can you have a look and point me in the right direction, also in your code you have "Rest" can you explain this part of the code.

Thanks
Attachments
Screen Shots.docx
Screen shots from a Access Db
(99.78 KiB) Downloaded 126 times
Pratice DB_4_2_2_2.odb
Latest version of the Db
(75.92 KiB) Downloaded 55 times
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Calculation in Query

Postby F3K Total » Sun Nov 08, 2015 1:04 pm

Like this?
Attachments
Pratice DB_4_2_2_2.odb
(75.74 KiB) Downloaded 60 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: Calculation in Query

Postby Itsmemike » Mon Nov 09, 2015 2:22 pm

Hi
OK I surrender time to admit defeat and ask for help. :(

I have got my query working to show On order "UnitsOrdered" - "UnitsReceived" and On Hand "UnitsReceived" - "UnitsSold" - "UnitsShrinkage" - "Allocated"
Now I what to calculate Total On Order and Total On Hand. i have tried every way i can think but nothing works.

Next problem! after getting the results how do I put the fields on to my form (Parts Transactions). The form contains the Main Form (Parts Description) and Sub Form (Transactions). The data for the Query (TransactionQ) comes from the Sub Form. I would like if possible to put the results on the Main Form.

Question! How do I add the fields to the Main Form (Parts Transaction). The data for the Main Form is from the Query Parts Details.
Attachments
Pratice DB_4_2_2(PMC)_2.odb
(78.87 KiB) Downloaded 54 times
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Calculation in Query

Postby F3K Total » Mon Nov 09, 2015 6:37 pm

Hello,
i think you need to become familar with the Form Navigator, see attached picture, and investigate the structure of your form(s).
FN.png
You can move these structural forms and their controls by drag and drop within the structure.
To create controls, i mostly use these toolbars:
FC.png


R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: Calculation in Query

Postby Itsmemike » Tue Nov 10, 2015 7:50 am

Hi R

Thank you for your reply, I think i,m am trying to go too fast, I have downloaded some tutorials which I am looking at and also following your suggestion.

I need to back track to my query which I am stuck on. If you look at the TransactionQ query it shows the value for each line correct.

UnitsOnOrder 5 UnitsReceived 3 UnitsSold 1 On Order 2 On Hand 2
UnitsOnOrder 10 UnitsReceived 10 On Order 0 On Hand 10

I want to show a field for:
Total On Order 2
Total On Hand 12
I have looked up some tutorials and followed the example but can not get it to work. I created a test query (TransactionsTestQ) with what I thought it should,
If you have time could you look at the query and tell me where I have gone wrong.

Thanks
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Calculation in Query

Postby F3K Total » Tue Nov 10, 2015 5:34 pm

Hi,
Itsmemike wrote:If you have time could you look at the query and tell me where I have gone wrong.
If you would have provided the query ;-)
Maybe this would help:
Code: Select all   Expand viewCollapse view
SELECT "PartsID", SUM( "On Order" ) AS "total On Order", SUM( "On Hand" ) AS "total On Hand" FROM "TransactionsQ" GROUP BY "PartsID"

R
Attachments
Pratice DB_4_2_2(PMC)_3.odb
(79.17 KiB) Downloaded 63 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: Calculation in Query

Postby Itsmemike » Wed Nov 11, 2015 5:24 am

Hi,

Yes I guess it would of helped If I had attached the Query, sorry.

Thanks, that was what I wanted but 1 problem. When I put data (Open Balance 7, Received 7) into Record 2 it returns the same value from Record 1 in the Total fields. (12 & 2)

After submitting the post I did work how to get it to work, not exactly the same as you but it worked. Also I did not get as far as putting the fields on the main form. could you explain how you did that.

I prefer the way you have done but would, when you have Time, look at my attempt and give me some feedback. (this time I have attached the Db)
Attachments
Pratice DB_4_2_2(PMC)_2_2.odb
Latest Version
(97.07 KiB) Downloaded 54 times
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Calculation in Query

Postby F3K Total » Wed Nov 11, 2015 6:48 am

Hello,
itsmemike wrote:it returns the same value from Record 1 in the Total fields. (12 & 2)

you have to reload the form.
Advantage of Pratice DB_4_2_2(PMC)_3.odb solution is, that the form is writeable.
Would be possible with the reload button on toolbar Form Navigation, but find the sample, containing an extra reload button.
R
Attachments
Pratice DB_4_2_2(PMC)_4.odb
(79.31 KiB) Downloaded 62 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests