473,324 Members | 2,268 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

How to create a sum in a query where the sum is based on a sub form

i think im confusing myself here but ill try my best to explain

i have a form where cds can be purchased, within it is a subform which allows a user to choose from a combo box of all the cds

Now i got a textbox to display the sum of the prices using =sum([prices])
but i need to put that sum into a query, i understand you can do the total: [price1]+[price2] in order to add prices up but because my prices are based within a sub form i only have 1 price field and therefore can only add them up using the unbound textbox =sum([prices])

is there anyway of doing somthing similar in a query? oh and i have tried putting total:sum([prices])

thanks
May 9 '10 #1
6 2352
Jerry Maiapu
259 100+
Why do you have to put sum([prices]) into a query? if the unbound textbox =sum([prices]) is on the parent form form then what is the problem..

Give the table detaisl from which you forms (Parent +child) is based on and tell us what you are trying to achieve rather than mentioneing wha you'would like to do..nad maybe we maight help..
I screen shot would be a good idea as well..

JM
May 10 '10 #2
Hey sorry I forgot to mention what i'm trying to get out of it, I'm creating a query so that I can make a report based on a transaction number that will display what items the customer has chosen and a final total price. I know it's bad practice to store the total price so I'm trying to put the sum in the query

so basically I need a recipt in the form of a report with a total price on it

I'm not at my computer at the moment so I cannot get a screenshot but if one is required I will get one later

thanks
May 10 '10 #3
NeoPa
32,556 Expert Mod 16PB
Assuming the correct names the following should work in a query :
Expand|Select|Wrap|Line Numbers
  1. Total:=Sum([Prices])
Welcome to Bytes!
May 10 '10 #4
Nope i put that I tried that in the first post, it errors and doesn't work I'm afraid
May 10 '10 #5
NeoPa
32,556 Expert Mod 16PB
I guess you want the query to show both detail and aggregate data then. I misunderstood your question as most realise that is not how queries work (except by fiddling a fair bit).

If you are using a form with a subform, then your easiest route to showing a sum of the items on your subform, on your main form, is to create an unbound control (TextBox) in the Footer section of your subform, which can either be visible or hidden depending on your choice, and refer to that item (See Referring to Items on a Sub-Form) in another TextBox control on your main form.
May 10 '10 #6
Jerry Maiapu
259 100+
I don’t know if I am on the right track but I think you need to have a nested (sub query) to do that. But Ill show you how to use two different queries like what I do as am not good at nested sub queries..

First run query wizard for the two tables (assuming they have 1-many relationship: also assuming that the many side contains the CD names and its prizes).
Select the necessary fields and save the query.

Now run query wizard and create another query based on the last query you created.
Go to design view and click the Totals button.

On the sort row leave the GROUP BY clause and select SUM for the prices.

When you run the query you’ll see a single row for each item with their tolal price.
Create your report based on this summary query.

Tell me if I am wrong somewhere.

Regards

Jerry
May 11 '10 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: JDJones | last post by:
Using PHP and MySQL. Trying to put a list of categories into a drop down select option of a form like: <form name="form" action="<? print $_SERVER?>" method="get"> <select name="subject">...
4
by: James | last post by:
Dear group, Can anyone tell me how to do the following? I have a basic list of numbers in a table I want to be able to search these by letting the user type "<10" or ">=50" on a simple form...
1
by: Matthew | last post by:
Hey, I have built a form that has certain combo and text boxes on it, which a user specifies his criteria and then clicks on a search button to run a query based on that criteria. To build to...
3
by: dskillingstad | last post by:
I'd appreciate any help I can get. I'm not sure what I'm doing wrong, but.... I've searched these groups for some solutions but no luck. I have an unbound form (frmSearch), with several unbound...
2
by: amith.srinivas | last post by:
Hi all, From a word macro in VBA, I am trying to create a report in access DB. The report is based on a query with a parameter. I am using Set rpt = Application.CreateReport rpt.RecordSource =...
5
by: Kaspa | last post by:
I am creating a contact database and I would like the user to create his own groups of contacts based on criterias, thefore I would like to let him pick the fields and criteria for a particular...
7
by: J-P-W | last post by:
Hi, in the past I've spent ages writing VB routines to get around this problem, is there a simple way? I have three tables for a membership system: tblMembership - MembershipNumber; Names etc...
7
IGGI
by: IGGI | last post by:
Looks like this is the best place for the right Answers. Got a database that stores users skills, "Leader", "Investigator","Engineering" ect in a "personnel" table, A form called "Search Query"...
5
by: Ferasse | last post by:
Hi, I'm an occasional Ms-Access developer, so there is still a lot of stuff that I don't get... Right now, I'm working on a database that stores contractual information. One of the form that...
1
by: samtymom | last post by:
I have a database that is used for Salvage Vehicles. (At present I am using 2002, but will be moving to 2007 shortly.) Presently there is a Switchboard that opens the Form View of the Salvage...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.