Total and query


I have a form frmreferb which has a sunbform called frmreferbdetails
(Continues Form) on the subform I have a text box called quantity and a
Priceperunit text box. I have a Total Text box with the control source set
to =[Quantity]*[PricePerUnit] this gives me the total of each line. What I
would also like to do is calculate the total text box to give me a grand
total on the frmreferb form. Does anyone have any ideas?

The other thing I am trying to do is I have created a query called
ReferbTotalsQuery this contains the quantity and total of each item. I need
to calculate the total of each line ad then calculate the totals to create a
grand total of which I would like to display in a text field on the
frmreferb form. I have tried to get my head around this but I am struggling
with not storing the total of each invoice.

Any suggestions or advise would be gratefully received!

KR

S




0
SG
11/17/2007 3:26:13 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
607 Views

Similar Articles

[PageSpeed] 1

SG wrote:
>I have a form frmreferb which has a sunbform called frmreferbdetails
>(Continues Form) on the subform I have a text box called quantity and a
>Priceperunit text box. I have a Total Text box with the control source set
>to =[Quantity]*[PricePerUnit] this gives me the total of each line. What I
>would also like to do is calculate the total text box to give me a grand
>total on the frmreferb form. Does anyone have any ideas?
>
>The other thing I am trying to do is I have created a query called
>ReferbTotalsQuery this contains the quantity and total of each item. I need
>to calculate the total of each line ad then calculate the totals to create a
>grand total of which I would like to display in a text field on the
>frmreferb form. I have tried to get my head around this but I am struggling
>with not storing the total of each invoice.


You can calulate the total if the subform records in a text
box in the subform's header or footer section.  Then text
box's expression sould be:
	=Sum([Quantity]*[PricePerUnit])

If you really need to display that total on the main form
(and make the subform's header/footer invisible, then use a
main form text box with an expression like:
	=subformcontrol.Form.textboxinsubform

I don't understand what you want from the query in your
second question.  It sounds to me like the same calculation
as in the subform.

You are more likely going to get better responses by posting
each question separately to a specific forum instead of
crossposting multiple questions to multiple forums.

-- 
Marsh
MVP [MS Access]
0
Marshall
11/18/2007 12:52:13 AM
Hello SG.
Second question:
You can't display/calculate item values AND a grand total in only one query. 
Use a report for that.
First question: 
I guess that you have tried to use a textbox in the form footer calculating 
the sum of the "Total Textbox"? Well, this won't work. Aggregate functions in 
forms/reports require fields from the record source, in this case:
=Sum([Quantity]*[PricePerUnit])

-- 
Regards,
Wolfgang


"SG" wrote:

> 
> 
> I have a form frmreferb which has a sunbform called frmreferbdetails
> (Continues Form) on the subform I have a text box called quantity and a
> Priceperunit text box. I have a Total Text box with the control source set
> to =[Quantity]*[PricePerUnit] this gives me the total of each line. What I
> would also like to do is calculate the total text box to give me a grand
> total on the frmreferb form. Does anyone have any ideas?
> 
> The other thing I am trying to do is I have created a query called
> ReferbTotalsQuery this contains the quantity and total of each item. I need
> to calculate the total of each line ad then calculate the totals to create a
> grand total of which I would like to display in a text field on the
> frmreferb form. I have tried to get my head around this but I am struggling
> with not storing the total of each invoice.
> 
> Any suggestions or advise would be gratefully received!
> 
> KR
> 
> S
> 
> 
> 
> 
> 
0
Utf
11/18/2007 12:53:00 AM
Marshall,

The second question is to enable me to display a running total of 'Ongoing' 
costs of all jobs within my database. This would then highlight to the user 
costs to date. Is this possible?

KR

S


"Marshall Barton" <marshbarton@wowway.com> wrote in message 
news:3e2vj39mjlfl9a1oqob2co8vpu9e7ls8bu@4ax.com...
> SG wrote:
>>I have a form frmreferb which has a sunbform called frmreferbdetails
>>(Continues Form) on the subform I have a text box called quantity and a
>>Priceperunit text box. I have a Total Text box with the control source set
>>to =[Quantity]*[PricePerUnit] this gives me the total of each line. What I
>>would also like to do is calculate the total text box to give me a grand
>>total on the frmreferb form. Does anyone have any ideas?
>>
>>The other thing I am trying to do is I have created a query called
>>ReferbTotalsQuery this contains the quantity and total of each item. I 
>>need
>>to calculate the total of each line ad then calculate the totals to create 
>>a
>>grand total of which I would like to display in a text field on the
>>frmreferb form. I have tried to get my head around this but I am 
>>struggling
>>with not storing the total of each invoice.
>
>
> You can calulate the total if the subform records in a text
> box in the subform's header or footer section.  Then text
> box's expression sould be:
> =Sum([Quantity]*[PricePerUnit])
>
> If you really need to display that total on the main form
> (and make the subform's header/footer invisible, then use a
> main form text box with an expression like:
> =subformcontrol.Form.textboxinsubform
>
> I don't understand what you want from the query in your
> second question.  It sounds to me like the same calculation
> as in the subform.
>
> You are more likely going to get better responses by posting
> each question separately to a specific forum instead of
> crossposting multiple questions to multiple forums.
>
> -- 
> Marsh
> MVP [MS Access] 


0
SG
11/18/2007 9:35:17 AM
SG wrote:
>The second question is to enable me to display a running total of 'Ongoing' 
>costs of all jobs within my database. This would then highlight to the user 
>costs to date.


For a form, you have to do it in the form's record source
query.  Here's an air code example:

SELECT T.id, T.job. T.cost, T.sort, ...,
			(SELECT Sum(X.cost)
			 FROM table As X
			 WHERE X.job = T.job
					And X.sort <= T.sort) As RunTotal
FROM table As T

FYI: a running total query is logically the same as a
ranking query.

A running total in a report is easier because you can use a
text box with the RunningSum property.

-- 
Marsh
MVP [MS Access]
0
Marshall
11/18/2007 2:21:50 PM
Reply:

Similar Artilces:

An XML question
I would like to produce the following output based on my XML file: My Album (2005) Elapsed Time (hh:mm:ss): 00:07:00 Song 1: title1 Length (hh:mm:ss): 00:02:30 Song 2: title2 Length (hh:mm:ss): 00:02:15 Song 3: title3 Length (hh:mm:ss): 00:02:15 ===== <album> <general> <title>My Album</title> <year>2005</year> </general> <content> <song> <songTitle>title1</songTitle> <songLengthInSeconds>150</songLengthInSeconds> </song> <song> <...

Linq query
Hello, I have the following list: IList<Int32> roles = { 1, 2, 3 } I need to get all users where in user.Roles there is at least one Role which is id 1, 2 OR 3. var a = users.Where(u => u.Roles. ??? ); How can I do this? Thanks, Miguel shapper wrote: > I have the following list: > > IList<Int32> roles = { 1, 2, 3 } > > I need to get all users where in user.Roles there is at least one Role > which is id 1, 2 OR 3. > var a = users.Where(u => u.Roles. ??? ); users.Where(u => u.Roles.Any(r => roles.Contains(r))) ass...

Totals on Sales Inquiry native windows
We would like to have the totals on the Sales Inquiries: Transaction by Customer Transaction by Document Sales Documents Sales Items Today we can retrieve this information in separated inquiries and reports, be we would like to heve a easier way to see this totals... The same suggest can be applied to Smartlist. If possible, you can create a tool to sum a field after you make a filter I created a screen where you can see all sales, separated by day of week, and week of year, and totals YTD,MTD and WTD, but I am not able to have the same information grouped by item number, for example.....

group query 02-05-10
I'm trying to create a query that gives me the average price of each product based on the last 10 purchases. The table is called "t_worksheet", with fields called "buy_price_alt_currency" which represents the price, "processing_grade" which represents the product and "collection_date" which represents the purchase date. I've been struggling with sub queries but can't get the results I need. Please could somebody help. Thanks Ian I think this will work (UNTESTED) -- qryLastTen -- SELECT processing_grade, buy...

Unmatched query wizard??
I have succesfully made a Unmatched query using the wiz. I changed the query to a delete query, but when I run it, it asks me which table do i want to delete the records from. I keep screaming at it 'what do you mean which table isn't it obvious', but that doesn't work. Can anyone tell me how to overcome this please. Regards Geeves1293 Use a subquery to select the unmatched records This example deletes records in tblInvoice that have no matching records in tblInvoiceDetail: DELETE FROM tblInvoice WHERE NOT EXISTS (SELECT InvoiceID FROM tblInvoiceDetail ...

total cost on report
I have areport with a cost field that I would like to total that collum at the end of the report. How do I do this ? do I need to create another field (total) please help thanks, jettro Jettron wrote: >I have areport with a cost field that I would like to total that collum at >the end of the report. How do I do this ? do I need to create another field >(total) The usual way is to use a text box with an expression like: =Sum([cost field]) -- Marsh MVP [MS Access] I'm in design view of the report, where do I insert this expression? I clicked on page footer to insert thi...

Setting ID# to 1 & Total on bottom of query result
Hi, I have 2 separate questions. First how do I set the ID# to 1 for the first record. I copied my whole database including queries and forms which were 2009 records and changed the titles to 2010. I did a "compact and repair" to the 2010 database. But when I start to enter records for 2010 the ID# is 2550. How can I reset the ID# back to start at 1? On many of my queries I have the results be for example 28 Caucation, 43 African American, 65 Hispanic, 4 Asian, 2 Native American. I need these numbers separate like they are but I would like a total line at the bottom...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

query issues
I am having trouble figuring something out and hoping someone out there might be able to help. I have built an Access database for a hospital education department to track education activities that employees have taken and/or need to take. I have built into the structure a means of requiring a specific job code to take a specific activity, or employees from a specific department to take an activity. I have tables set up - Employee, JobCodes, Depts, JC_Requirements, and Dept_Requirements. Each employee has a Dept_Num and a JC_Num. These fields are linked to the Dept and JC tables. Each ta...

Query AD
Hy, there is some tools that i can use to query the active directory? I need to know if users are members of some groups or not. Thanks Kentucky On 21 Nov 2006 05:56:58 -0800, "Kentucky" <aforino@libero.it> wrote: >Hy, there is some tools that i can use to query the active directory? >I need to know if users are members of some groups or not. >Thanks >Kentucky ldp.exe (Windows Support Tools Download) adfind (you can google that) are two popular tools On 21 Nov 2006 05:56:58 -0800, "Kentucky" <aforino@libero.it> wrote: >Hy, there is some to...

Delete duplicates QUERY
I'm having two tables, each with one field which contains some data: ___________ Table1 Field1 Per Pert Perte Perter ___________ Table2 Field2 Pert ___________ Now, I want to make a DELETE Query in MS ACCESS 2003. I go by the "Find Unmatched Query Wizard" (1st point on Table2, 2nd point on Table1, 3rd click on Field2<=>Field1, 4rd point on Field2 to see. Then I have "Table2 Without Matching Table1" (I then change Is Null to <>"False" and show Table1.Field1 in stead of Table2.Field2)) I'll then end up with this SQL...

Parameter Query 05-15-07
I am setting up a phone book I have set up the query [What Company] When I run the query and the Prompt Box asks for the company name, I cannot get it to produce any companies that I do not know the correct spelling for How can I get it to produce company names, Wildcards????? -- JohnM To get wild card in a query use Like + * In the criteria - ============== Like "*" & [What Company] & "*" will return the records that contain the string, any where ============== Like "*" & [What Company] will return the records that contain the string, in the...

Total Items
Hi - Is there a way to get a total number of items that are about to be tendered, on the POS screen ? Thanks. You can customize the status.htm file. Not sure exactly what the variable is that you want, but I know it cn be done. "NJS" <NJS@discussions.microsoft.com> wrote in message news:538F3CC7-F23D-43C7-8A5A-4319B8688E5B@microsoft.com... > Hi - > Is there a way to get a total number of items that are about to be > tendered, > on the POS screen ? > Thanks. Jason, Send me your email I have already customized this and will forward you. "Jason&qu...

Total of Totals
Hey there, I've set up a database consisting of four tables. I run a query which calculates a total for the item (unit price * quantity). Is it possible to calculate the final total of all this for a report? If that's possible, how can I get it to be at the bottom of all the records but only on the last page? Thanks Mathew On Aug 1, 12:48 am, KARL DEWEY <KARLDE...@discussions.microsoft.com> wrote: > Yes. > Open the form in design view, click on menu VIEW - Report Header/Footer a= nd > add a text box in the footer with Control Source =3D[unit price] * > [quantit...

Manipulating totals and columns in Pivot Tables
Hi, I'm constructing a pivot table that has investors as columns, investments as rows, and the general data is values. Trouble is there is a column which I want to only display half of each value. I know that I can make a formula that divides a column "column A/2", and then hide the unwanted columns. But I would also like the hidden columns to not be included in the grand totals. How do I make this happen? Thanks! Hi, So let me understand what you want, something like this in the Pivot Table: A B C Name Jp ...

date diff expression in query
I am trying to calculate the difference between two dates 'End Date' and 'Date of Visit' using a query. This is the expression I used: TimeFrame:[Date of Visit] - [End Date]. However, 'End Date' is present in both tables I have used to create this query 'tbl_NewInfo' and 'tbl_Foloowup'. I want the 'End Date' from 'tbl_NewInfo'. How do i specify this in the expression and thereby prevent the error msg I am getting? Thankyou You must fully qualify the field name like: [Table Name].[Field Name] I use a naming convention that resu...

Pole display Sub Total
Hi All, When a client of mine scans items the amount displaying is the sub total. Is there any way to display the total (ie including tax amount) Regards Amanda ...

how do I get a total for all worksheets in my workbook?
I have ten worksheets, each with a total. How do I get a total of all totals? Considering you have 3 sheets, and the totals are in cell A1, then use: =SUM(Sheet1:Sheet3!A1) Mangesh "Aileen Hewat" <AileenHewat@discussions.microsoft.com> wrote in message news:18C80118-642F-4690-8B5E-7D68B63752E3@microsoft.com... > I have ten worksheets, each with a total. How do I get a total of all totals? This file might be a help: http://www.bygsoftware.com/examples/zipfiles/consol.zip It's in the "Accountants" section on page: http://www.bygsoftware.com/examples/exa...

How to change query empty field to "0"
Hello, At the beginning forgive me at the bad English. I am a beginner in Access. I'm working on a database that deals with records warehouse. To simplify the. I have one query, for example query A, a query where the calculation of 50 items. This query A has sum by the code of materials. The second query is composed of one few table and query A. But in this table is 60 items with the calculations of other tables. The column, which is associated with a query (sum: SumOfqueryA) has a problem. 50 fields have result, and 10 field is blank because I have 50 results in QueryA. How...

Calculate Total Books
I am preparing packing slips for boxes to be sent out. There are different book orders going into the same box. Each book in the order will have its own set of id numbers that are usually, but not always, in a string(101-121 etc). I use a calculated field on the form to display the number of books in the string as I store the start book number and subract it from the end book number and add 1 to get the actual number of books in the string. I use the same calculated field on the report to show the number of books in each detial, but I have multiple strings in 1 box. I need to show a...

Button Wizard:RunQuery ignores delete queries?
If I put a button on a form, choose misc, run query, I get a list of queries that does not include any delete queries. Why is that? It is easy enough to get around, but why? Don't have an answer for you, other than to confirm that delete queries are not presented in the list of queries.... ACCESS has been this way for a number of versions, for some reason. -- Ken Snell <MS ACCESS MVP> "Phil Smith" <phil@nhs-inc.com> wrote in message news:OjpW0tg1HHA.1100@TK2MSFTNGP06.phx.gbl... > If I put a button on a form, choose misc, run query, I get a list...

Web Query #4
Thank you in advance for any help provided to the subject question. I am operating Excel 2003. A data query has been established to pull investment data from finance.yahoo.com (i.e. stock volume, % ownership of management, etc.). The query works fine and pulls approximately 50 values from the webpage. My questions is this: If I have a list of 100 stock symbols how can I link that list to the web based data query to automatically insert each symbol into the data query individually and then dump that data automatically to another worksheet within the same workbook. Should I use a ma...

total for bills
Is it possible to have the total of the bills not yet paid in the current or for the upcoming month? Thank you in advance. Bepp ...

Totaling Hours Worked
I have one column that has my start time (6:30 AM) and one column that has my end time (3:30 PM). How do I make the third column total the hours I worked (9.00)? Thanks for any help. Debra Ann (EndTime - StartTime)* 24 To Excel, a day = 1 and an hour = 1/24 so, to convert an hour value "up" to an integer, multiply by 24. Hope this helps, -- George Nicholson Remove 'Junk' from return address. "Debra Ann" <anonymous@discussions.microsoft.com> wrote in message news:022801c3d6d5$39f8a340$a101280a@phx.gbl... > I have one column that has my start time ...

Query to find select columns in several tables....Help!
Here's the situation: I have 22 personnel. Each is in a different category (Enlisted Air Force, Enlisted Army, Officer Air Force, Officer Army) Each category has different training requirements. Please note that in my Personnel Table, each person is actually assigned to one of those categories. (Hoping that it might be in some way helpful to finding a solution) What I've done: I created 5 Tables. Table 1: Personnel Data. The key field is Social Security Number. It does include a dropdown selection of the 4 categories mentioned above. Tables 2, 3, 4, and 5: Each o...