Calculation in Query

[Itsmemike]

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.

[F3K Total]

Hi,
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"

or grouped e.g.

Code: Select all

Select SUM("UnitsOrdered"-"UnitsReceived") as "Rest_Supplier" from "PurchaseOrder" GROUP BY "SupplierID"

R

[Itsmemike]

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

[F3K Total]

Like this?

[Itsmemike]

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.

[F3K Total]

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

[Itsmemike]

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

[F3K Total]

Hi,

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

SELECT "PartsID", SUM( "On Order" ) AS "total On Order", SUM( "On Hand" ) AS "total On Hand" FROM "TransactionsQ" GROUP BY "PartsID"

R

[Itsmemike]

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)

[F3K Total]

Hello,

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