OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED]Sum fields from multiple records with identical ID's

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Nightsway
Newbie
Newbie


Joined: 09 May 2012
Posts: 3

PostPosted: Wed May 09, 2012 7:32 am    Post subject: [SOLVED]Sum fields from multiple records with identical ID's Reply with quote

Finding it difficult to condense this into a subject title.

Basically, I have a Sales database and an Orders database.

Multiple Sales records can be related to a single Order record (via Order table's PK - One-To-Many)

Each Sales record has a 'Price' field. Each Order record has a 'Total Price' field

If possible, I would like each Order record's 'Total Price' to equal the sum of all the related Sale record's 'Price's

Ideally, I'd like the Order record in the table itself to alter to reflect the sum. However, if this isn't possible, presenting the result in a query/report is second best.

I hope this makes sense, I've been trying to word this for an hour now, so I could google it. As you can see, I couldn't. Thank you very much for reading this, and doubly so if you can help.


Last edited by Nightsway on Thu May 10, 2012 7:19 am; edited 1 time in total
Back to top
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Wed May 09, 2012 10:52 am    Post subject: Reply with quote

Are you saying that multiple sales ITEMS are related to a single order?

That's perfectly normal if so.

(BTW, they are called tables not databases)

Is your question how to sum the order ITEMS for one order, and then have its total be recorded in the Order field "Total price?"
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003.
Back to top
View user's profile Send private message
Nightsway
Newbie
Newbie


Joined: 09 May 2012
Posts: 3

PostPosted: Wed May 09, 2012 11:25 am    Post subject: Reply with quote

Billyray wrote:
Are you saying that multiple sales ITEMS are related to a single order?

That's perfectly normal if so.

(BTW, they are called tables not databases)

Is your question how to sum the order ITEMS for one order, and then have its total be recorded in the Order field "Total price?"


Thanks for the help, sorry for the dodgy terminology. Haven't had to write about databases for 2 years. Surprised I even remember how to spell 'Database'.

Yes, multiple sales items are related to a single order.

I think you understand what I'm looking for. Just to make sure:

Sales Table
Order ID | Item ID | Item Name | Item Price
1 | 1 | Something | £1.99
1 | 2 | Another | £1.01
1 | 3 | Lastthing | £3.00

Order Table
Order ID | Total Price
1 | £6

For example.

Again, thank you very much for your help
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Thu May 10, 2012 2:04 am    Post subject: Reply with quote

Take a look here:

See also:

_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
Nightsway
Newbie
Newbie


Joined: 09 May 2012
Posts: 3

PostPosted: Thu May 10, 2012 7:19 am    Post subject: Reply with quote

Thank you both very very much. It's a lot simpler than I was anticipating it to be. Appreciate your help! Have a good one Smile
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group