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 Replies
ADVERTISEMENT
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
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
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
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
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
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
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 7 Replies
View Related
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
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
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
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
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
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
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
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
Oct 19, 2004
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
View 1 Replies
View Related
Mar 24, 2013
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
View 3 Replies
View Related
Mar 4, 2015
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
View 1 Replies
View Related
Oct 28, 2005
Hello All,
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
View 9 Replies
View Related
Nov 20, 2013
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
View 5 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Aug 12, 2015
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
View 4 Replies
View Related
Jul 15, 2014
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies
View Related
Jul 23, 2015
I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
View 14 Replies
View Related
May 31, 2006
:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)
View 1 Replies
View Related
