Calculation in Query
Calculation in Query
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.
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
Re: Calculation in Query
Hi,
depends on if you want to group the numbers by anything or not. Most simple would be:
or grouped e.g.
R
depends on if you want to group the numbers by anything or not. Most simple would be:
Code: Select all
Select "UnitsOrdered"-"UnitsReceived" as "Rest" from "PurchaseOrder"
Code: Select all
Select SUM("UnitsOrdered"-"UnitsReceived") as "Rest_Supplier" from "PurchaseOrder" GROUP BY "SupplierID"
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
Re: Calculation in Query
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
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 204 times
-
- Pratice DB_4_2_2_2.odb
- Latest version of the Db
- (75.92 KiB) Downloaded 129 times
OpenOffice 4.1.2
Re: Calculation in Query
Like this?
- Attachments
-
- Pratice DB_4_2_2_2.odb
- (75.74 KiB) Downloaded 137 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
Re: Calculation in Query
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.
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 130 times
OpenOffice 4.1.2
Re: Calculation in Query
Hello,
i think you need to become familar with the Form Navigator, see attached picture, and investigate the structure of your form(s). You can move these structural forms and their controls by drag and drop within the structure.
To create controls, i mostly use these toolbars: R
i think you need to become familar with the Form Navigator, see attached picture, and investigate the structure of your form(s). You can move these structural forms and their controls by drag and drop within the structure.
To create controls, i mostly use these toolbars: 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
Re: Calculation in Query
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
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
Re: Calculation in Query
Hi,
Maybe this would help:
R
If you would have provided the queryItsmemike wrote:If you have time could you look at the query and tell me where I have gone wrong.
Maybe this would help:
Code: Select all
SELECT "PartsID", SUM( "On Order" ) AS "total On Order", SUM( "On Hand" ) AS "total On Hand" FROM "TransactionsQ" GROUP BY "PartsID"
- Attachments
-
- Pratice DB_4_2_2(PMC)_3.odb
- (79.17 KiB) Downloaded 142 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
Re: Calculation in Query
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)
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 126 times
OpenOffice 4.1.2
Re: Calculation in Query
Hello,
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
you have to reload the form.itsmemike wrote:it returns the same value from Record 1 in the Total fields. (12 & 2)
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 139 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