| View previous topic :: View next topic |
| Author |
Message |
Nightsway
Newbie
Joined: 09 May 2012
Posts: 3
|
 Posted: Wed May 09, 2012 7:32 am Post subject: [SOLVED]Sum fields from multiple records with identical ID's |
 |
|
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 |
|
 |
Billyray
OOo Enthusiast
Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio
|
| Posted: Wed May 09, 2012 10:52 am Post subject: |
|
|
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 |
|
|
Nightsway
Newbie
Joined: 09 May 2012
Posts: 3
|
| Posted: Wed May 09, 2012 11:25 am Post subject: |
|
|
| 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 |
|
|
dacm
Super User
Joined: 07 Jan 2010
Posts: 734
|
|
| Back to top |
|
|
Nightsway
Newbie
Joined: 09 May 2012
Posts: 3
|
| Posted: Thu May 10, 2012 7:19 am Post subject: |
|
|
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  |
|
| Back to top |
|
|
|
