Calculating A Subtotal - Should Be Easy?

Aug 2, 2005

Hi all,
I have a subform within my form for quotations (see attached).

A user will manually type in "Repair Cost" and "Accessory Cost". The subtotal field is calculated using this expression =Nz([Repair Cost])+Nz([Acc Cost]) in the Control Source box
This is all Ok.

I want to subtotal these on the main form then work out the VAT and final total.

My problem is, No matter what expression I seem to use on the main forms subtotal, it will not work.

Here is an example of what I have tried:

=Sum([SubTotal])
=Sum[Forms]![TblQuotation subform].[SubTotal]
=Sum[TblQuotation subform].[Form]![SubTotal]

Nothing seems to work. :confused: Any pointers would be greatly received. Many thanks.

View Replies


ADVERTISEMENT

SubTotal Query

Jun 19, 2005

Hi All,

I have been searching through the forum to see how I can do a query that does subtotals, but to much to my avail there has been nothing that specifically answers my question. So here it is. If I have two tables say one called A and the other B. Now in A there are two fields that I want to use and these are item and quantity (The quantity is the number of that item). In the other table I want to use the field rate. Both tables are related by the item. How do I do a subtotal query that gives me the subtotal for an item i.e. rate * quantity, and shows the information in the specific format:

item : quantity : rate : subtotal

Thanks
Greg

View 1 Replies View Related

Form Help (Subtotal)

Jun 28, 2006

I have a form with a control "TypeCode," it's set to:


=IIf(Year(Date())<=Year([date]),[qryVacation.TypeCode],"Other")

So that if the date on the form is less than the current year, the control will return "Other" in the box.

At the bottom of the form, I have a subtotal:

=Sum(IIf([Typecode]="Sick",1,IIf([Typecode]="Half Sick Day",0.5,0)))*8

This helps tally Sick day and Half Sick Days for me, but if they are a year old, then they should not be counted. Once I got the field to say "Other" I thought I was home free, but it is still counting the sick days.

I know it's probably still getting the value from the qryVacation, but how do I have it so it just subtotals off the form iteself?

View 1 Replies View Related

Subtotal On A Form

Aug 15, 2006

I have a form were I got the corrent fields:
date, account, ammount; the account field is a numeric field. What I am trying to do is to have a extra field on the same form wich will give me the total ammount for the relative account as soon as I have inserted the 3 data. What I don't knowis if I need a query to extract all the ammount for the relative account shown on the field "Account" or it can be done in a easyer way!!!

View 4 Replies View Related

Subtotal Row Formatting

Dec 2, 2004

Hello,

I have a code in my 97 Access db that exports a query in Access to Excel. The code also includes all the formatting needed for the Excel sheet including subtotal rows. What I would like it to do is automatically format the subtotal rows to be a different color so they stand out. This is part of my code:

xls.Cells.Select
xls.Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(13, 17), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Since the data changes continually I have no idea which rows will have the subtotals.

Is there some code I can insert in my export code to automatically color or highlight the subtotal rows?

Thanks in advance for any help.

Elizabeth

View 2 Replies View Related

How To Run A Subtotal And Ranking Query

May 27, 2005

Have a table containing a column called "BOC" (Budget Object Code) that contains approximately 85 different 4 digit codes, with one selected for each row. (See sample below). Table has 30,000 rows of data. What I need to do is run a query that will give me a subtotal dollar amount for each unique BOC, and then display the results in descending order for both Obligations and Expenditures. Any ideas??

Sample Data:

COST CENTER BOC OBLIGATIONS EXPENDITURES
119900 2670 1000.00 500.00
119901 2685 2000.00 1000.00
119999 2670 5000.00 2000.00
119920 3130 5000.00 5000.00

View 1 Replies View Related

Running Balance / Subtotal

Nov 12, 2007

Hi

I haven't been on for a while. so hello everybody.

I am trying to keep a subtotal in a table. there is an index (actually its formatted text, but its a number and i can converted i.e. cdbl()). I was thinking that i should create one query with dmax in one column and and then use another one to show the value plus the previous value.

can anybody help me out please?

thanks,

Sam

View 6 Replies View Related

Subtotal Data In Access Problem

Mar 7, 2008

Hi,
I am trying to work out away of subtotalling a field in access, similar to that of the subtotal function in excel,
Basically my table is structured so i have a store number column and a transaction number column so as an example my data looks like this:
Store noTransaction Num
1234522664499
1234522664499
1234533775599
1234533775599
1234544886600
12345611227755
12345622449955
12345677556699
The transaction number is made up of several other columns and basically represents an item sold by an assistant at a certain time - if the asst sold 2 items i will get the same transaction number appear twice, if they only sold 1 item it will only appear once.
I want to be able to count the transactions by store so i end up with a result like this (based on the above data)
Store no Count of Trans No
12345 3
123456 3

Can anyone help?

View 2 Replies View Related

Creating A Invoice Subtotal Totals Etc

Oct 24, 2007

i was wondering if there is any easy to follow information anywhere or examples with how to do..Specifically on creating an invoice??

The form would need customer details then a subform with the qty, price per unit, extended price then a section on the form which works out the subtotal adds it to freight charges
then gst and finally the total.These all automatically then being placed in there appropriate tables..i Managed to figure this out once before but i cant for the life of me figure it out again..I have spent weeks trying any clues???

View 1 Replies View Related

Modules & VBA :: Subtotal Based On Month Not Day?

Oct 4, 2013

I have created some subtotals in some columns of data which for example are the dates, names, and amounts of invoices.

The code I used is:

Range(Range("C1"), Range("C1").End(xlDown)).Select
Selection.CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3)

This is working of sorts but it is giving me subtotals based on the day in the date column. How can I change this to group by the month?

View 10 Replies View Related

Reports :: Subtotal By Date Range

Apr 3, 2014

I have a set of records that contain the following attributes;R_Name, Cost, StartDate, EndDateI need to subtotal the cost for each R_Name by year. The start and end date attributes can span several years and I need the correct cost for partial years.

View 5 Replies View Related

General :: Subtotal On Subform Saving To Table

Jul 17, 2013

find the attached MS access DB for INVOICE printing . I am facing two issues there .

1. {form INVOICE } sub-total must be Calculated from line total on {INVOICE DATA Subform} , I know this can be accomplished by adding a new field at sub-form and calling that value to main form's sub-total field .

2. And the real issue is i want to save this value from subtotal on form INVOICE to table field name subtotal .

3.I wish to make a reporting format for each INVOICE NUMBER , whenever report is being generated , the report shows more than 1 invoice details .

View 2 Replies View Related

Create A Query That Will Return Subtotal For Order

Nov 16, 2013

I have set up a basic database that has a customer table , orders table , join table and products table. I have created an invoice form that has customer details and then in a subform has the order ID and product ID , name , price and quantity.I have a text box on the form to which I am trying to output the sub total to when the form is launched(quantity X price for the current order).I was trying to create a query that will return the subtotal for an order.I had the query calculating the subtotal but I was then trying to get t the cost based on the order ID. I was then attempting to get the Order ID for the query from the open form by adding this to the criteria in the query:

[Forms]![Invoice]![invoicesubform].[Form]![Order ID]

I was then trying to use Dlookup to put the result of the query into a subtotal text box by using:

=DLookup("[cost colum from query entered here]",[the query name was entered here]")

View 1 Replies View Related

Reports :: Stop Subtotal From Adding Detail Lines

May 16, 2014

How to keep Access from including the detail line items in my report total?

Below is a quick image of part of the report.

The report is compiled from two tables. The first table has detail information on each line, in four columns:

State Name Amount Quantity (Not used)

AL Store 1 313.94 60
AL Store 2 12.59 1
AZ Store 3 576.45 90
CO Store 4 172.22 24
CO Store 5 502.25 88

The 2nd table is a summary by state with three columns:

State Amount Quantity (Not used)
AL 326.53 61
AZ 576.45 90
CO 674.47 112

I'm trying to make the report group by state, then name, amount and a subtotal for each state. This all works fine in the report.

I want to also add the amount field from the 2nd table (State Subtotals) to the report just for reference. I've managed to make this work as well. (See State Total on image)

The problem that I'm having is that when I try to do a final total of the State Total column in the report footer, it seems to add the (State Subtotals) amount field on every detail line (See red numbers in picture. they are not actually on the report, but Access thinks they are and includes them in the final total).

So far I've tried different joins, hiding various fields, sub reports, ect. I just can't figure out how to stop Access from calculating the detail lines.

View 2 Replies View Related

Modules & VBA :: Subtotal Data Access To Excel - Range Class Failed

Jun 6, 2014

I'm trying to subtotal data in an excel file but I am getting a subtotal method of Range class failed in the red text below. I have been trying to get this to work with no success.

Code:

Public Function SUMMARY()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
Dim wb As Object

[Code] .....

View 3 Replies View Related

Forms :: Calculate Subtotal On Subform And Display On Upper Subform

Jun 20, 2013

im having problems getting a subform to calculate another subform's total and display it as it keeps coming up with the #NAME?.Basically my database is like this

tblCustomer
- tblOrders
- tblItems

and this is displayed on a form. so you can flick through clients, then flick through the orders and its broken down into the items inside each order.i have seen many answers that are only about calculating something on a subform and displaying on a form but this does not work when displaying on a 'parent' subform. the form structure looks like this

frmCustomers
- sFrmCustomerItems
- sFrmCustomerOrders

i have a calculated control textbox in the footer of sFrmCustomerOrders with sums up all the prices using =sum([Price])and in my sFrmCustomerItems, i have a field called Amount (the total amount of all the items) and the Control Source formula i was using was =[Forms]![sFrmCustomerOrders]![sFrmCustomerItems]![txtTotalPrice] but i get a #NAME? error.

View 5 Replies View Related

Should Be Easy

Jun 1, 2005

I want to have code that disables fields based on the value of the contents of one of the fields. I wrote this code which goes on and on but it doesn't work. Can someone help?

Private Sub Form_Open(Cancel As Integer)

Dim Item_Number As String

If Me.Item_Number = "300123C" Then
Me.Batch_Lot_Number.Enabled = False
Me.Issue_Date.Enabled = False
Me.Production_to_BPT.Enabled = True

Elseif blah blah blah

else blah


Endif

End sub

View 3 Replies View Related

I Know This Must Be Easy!

Mar 22, 2005

I don't normally work in access so I am sure I am asking a really easy question.

I have a table that is connected to a form where users enter survey data. Currently they have a drop down form to mark the responses to the survey as "Fully Completed", "Partly Completed", etc.

On the table I want to add the text "Awaiting Completion" to all those without any entry so that we can find out how many are left to do.

I can filter the table to give me the correct records, and assumed I could paste the text directly into the column (as with Excel).

Can anyone please let me know how to update the column easily.

Thanks for your advice.

Chris

View 2 Replies View Related

Should Be An Easy One...I Think!

Mar 30, 2006

Warning - Complete newbie post!

Hi all,

I have 2 Access databases setup, each containing 1 table. Let's call them A and B. These two tables contain some of the same fields.

Table A is used for collecting new client registration info through our website and gets updated by SQL.

Table B is on a different server and is used for company newsletter mailings.

Table A is the one which is automatically being updated so I don't need to do anything with that table. Also, some fields in Table A are auto-incrementing numbers so we can differentiate a clients interest and provide a more informative newsletter for them specifically on their selected interest.

Whenever an entry in certain fields of Table A is made, I would like the same fields of Table B to be updated with that same information automatically. For example...

Client subscribes to our newsletter on our site and selects their interests, the email field now contains the subscribers email address and the various 'interest' fields contain a '1' telling us they are interested in that particular area of our business. As this takes place on Table A, it needs to be also replicated to Table B.

Surely this is possible, but how?

View 1 Replies View Related

This Is Probably Easy To Do

Apr 18, 2006

As I am an SQL novice I need some help

I have 3 tables

Table - Fields
SalesLedger - TransactionID, ProductID
Vouchers - TransactionID, Voucher_Ref
Products - ProductID, Product_Description

The Salesledger has fields TransactionID and ProductID

The Salesledger is linked to the Products table by the ProductID field

The SalesLedger is linked to the Voucher Table by the TransactionID field

How do I produce a query to list all the items in the Salesledger but if there is a Product in the Products Table matching the ProductID the Product details, and if there is a TransactronID in the Voucher Table list the VoucherRef.

At the moment, it only shows transactions if there is a corrosponding TransactionID in the Vouchers Table

Probably not explaned this at all well. I will try to elborate if required.

View 1 Replies View Related

Easy If You Know How

Jun 24, 2005

Hi all,

I am making a query which sorts birthdays into chronological order.

I have used the DatePart function to determine the month:

DatePart("m",[DATE OF BIRTH])

But I want to know how to display the month in CAPITAL LETTERS, i.e. instead of reading 1 it reads JANUARY

Thanks in advance

View 7 Replies View Related

Probably Easy To Do But.....

Feb 16, 2006

I have a list in Xcel of approximately 1100 client #'s that I have to match to our master list of 10000's.

I can create a query to pull these Clients, but I'd like it to only pull the 1100 requested. Is there a way to import an excel file to bump up against a query so that it only pulls the requested clients?

IE do I have to create a table and bring that data in then use them both in the query?

Sorry if this is a simple question, I'm just being thrown into the fire and have very little access background.

View 1 Replies View Related

Easy One...

Mar 2, 2006

To start: Running Office 20003. I have created a survey with Frontpages wizard. Basically It has a topic and the end user rates that topic on a scale of one to five like so:

1. Work Environment
a. I enjoy my work place. 12345
b. I think my office has a good reputation. 12345

1-5 are radial buttons. It takes the users input and stores it in an access database as im sure you all are aware. Looks kinda like this:

1a 1b 2a etc.
3 5 1

What i would like to do is this: When the users are done submitting i would like to go back and take results. I.e. we had this many users select option 3 for question 1a and we had _ number of users select option 2 for 1a, etc.
The thing is i was wondering if there was a way to create a query for it. I know i could just put =1 or =2 but that would require running 5 queries on each question.... not fun. Anyway, any help would be appreciated.

Thanks,

Viko

View 1 Replies View Related

An Easy One I Think ?

Mar 22, 2006

Hi - I think this can be quickly sorted.

I use the following code in my query to merge together my customers details to 1 field:

LeadName: [Title] & Space(1) & [FirstName] & Space(1) & [LastName]

I want to do it for their address, what do i use instead if SPACE so that i get a NEW LINE each time rather than1 space to the right?

Thankyou

View 1 Replies View Related

This Is Probably Easy To Do

Apr 18, 2006

As I am an SQL novice I need some help

I have 3 tables

Table - Fields
SalesLedger - TransactionID, ProductID
Vouchers - TransactionID, Voucher_Ref
Products - ProductID, Product_Description

The Salesledger has fields TransactionID and ProductID

The Salesledger is linked to the Products table by the ProductID field

The SalesLedger is linked to the Voucher Table by the TransactionID field

How do I produce a query to list all the items in the Salesledger but if there is a Product in the Products Table matching the ProductID the Product details, and if there is a TransactronID in the Voucher Table list the VoucherRef.

At the moment, it only shows transactions if there is a corrosponding TransactionID in the Vouchers Table

Probably not explaned this at all well. I will try to elborate if required.
Edit/Delete Message

View 3 Replies View Related

Probably Easy But I Don't Know

Dec 1, 2006

Hi all. I need to know if there is a way to show the last entry for a record. In my database there is a set of numbers i.e. 3000, 3001, 3002 etc which have multiple valuation figures with them i.e. 3000 could have valuation 1 £1000, valutaion 2 £1245. So as mention what i need is my query to show the last valuation entry for the selected reference number i.e. 3000 and valuation2 only.

Any ideas?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved