Any Way To Get A Total To Show When Filtering A Query
Mar 12, 2014
I have a query that just shows all the records in a table. It is used by the end user for filtering primarily. Now the user would like to see a total for the amount filtered.
For example; the table is for repairs. The query just shows ALL the repairs. The user filters the client field to find all repairs for one client. He then wants to see what the total charges are for that query.
I cant create a new field and sum the records because it is not a totals query. Is there any way to embed the query in a form and use the form portion to sum the filtered results?
View Replies
ADVERTISEMENT
Apr 14, 2015
I've just made this query at work, it brings up the results from a database of telephone surveys. about going all gestapo on the table names and codes, I'm unsure as to how told off I could get for placing identifiable images on the internet
query.png
basically I would like to extend this query so it shows the sum of the "CountOfQ1 22" column and also shows what percentage of that total each entry in the "Q1 22" column is.
This will enable me to have results for the day sent to me at home every night at close of business as the person supervising the call centre at the time can run the query and email me the results.
View 4 Replies
View Related
Oct 20, 2014
In my report there is a field with a number of numerical value. I want the report to show those numbers that are more than 2 only.
View 8 Replies
View Related
Apr 4, 2013
Ok I have a list box (CounselorInitials) and a listbox (AssignedToYou)
The list box has 6 columns and is using a query (AssignedToMe). The important one is the 6th one (Counselor). All working off a table called DityLog.
I want to select initials in the combo box and then it will filter the listbox and only show records that are the same.
So if I select initials MC from the combo box I want the Listbox to show all records that have the initals with MC.
View 1 Replies
View Related
Jul 22, 2005
Hi All.
I created form with unbound listboxs based on query which included Credit field that I want to calculate total and show result in caption of window. If is it possible how to do it?
Thanks.
View 1 Replies
View Related
Nov 4, 2014
I am trying to build a query to show the total sum of hours a staff member has done in a certain department and the amount of sleep ins they have done. Each staff member has a unique ID. In the query I can get all the information but it shows on two lines dependent on department. I want this to show on a single line. Each employee will never have more than two departments. The query is called "QryForForum".
View 2 Replies
View Related
Jul 23, 2015
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
View 9 Replies
View Related
May 14, 2006
hi
i am trying to show the total number of records that are in a table.
there are currently 8 entries in the table 'applications'
now i would like to show this on a tabcontrol in a form. I have been told to use Dcount. I have read about this and tried this on my form but unfortunately its not working.
Can anyone show me an example of how i would do this.
View 1 Replies
View Related
Jan 24, 2007
Hi ALl,
I have a subform that accounts and adds all the expenses for a certain project (for example, total is $27,000). I would like to know how to duplicate the final cost in the subform and show it on an unbound textbox of that linked main form (the unbound textbox in the main form shows $27,000 also).
Thanks in advance, guys...
Caliboi
View 4 Replies
View Related
Feb 22, 2013
Access 2010 ... I have 2 tables. One with base information second one is linked with multiple results each having a price. On table one i see the + sign when i click i can see the linked second table. Can i get a total amount of the price on table 2 on table one?
IE:
Table 1:
Trans ID - Seller - Quantity - Lot Cost - Parts Cost<-- the one i need total for.
123 - joe - 3 - $100 - $20 <-- the total of the 2 linked parts (Keyboard, Mouse)
Table 2:
ID - Trans ID - Part - Cost
1 - 123 - Keyboard - $10
2 - 123 - Mouse - $10
View 11 Replies
View Related
Feb 15, 2014
I have a continuous subform. Because the records are often many, the user needs to scroll down the page.
How do I have a text box in each row that shows Record x of Total Records?
I tried setting a text box control source = [currentrecord] in the Detail area of the subform but it didn't work . It did weird things because I suspect the control is trying to show the record that has the focus or something
View 1 Replies
View Related
Nov 27, 2013
This code shows current record number:
Code:
=[CurrentRecord]
This code shows total record number:
Code:
=DCount("[Category]","tblCategory")
How do I combine them in a textbox to say something like "8 of 10"?
View 1 Replies
View Related
Nov 14, 2014
I need a solution for the following problem:
For example:
............F1 F2 F3
Rec1 ....4 ..2 ..5
Rec2 ....3 ..3 ..1
Rec3 ....1 ..2 ..1
Now, I want to know how many 1s are there in Rec3 the answer is obviously 2 but I want a field(F4, for e.g) that calculates F1+F2+F3 showing all their 1s 2s and 3s.
A solution I found was...... F4=IIf([F1],1,0)+IIf([F2],1,0)+IIf([F3],1,0)
But I have over 50 Fields that contain 1s and 2s and I can't count them all by using this formula as it will be tremendously long and access will reject it.
So I need another formula(or function) that will look up for the 1s across multiple fields and show the total count for each record in a new field.
Tried 'look up' function but achieved nothing ....
View 3 Replies
View Related
Jan 28, 2014
I have a form containing 4 text Boxes txt1, txt2, txt3 and txt4
I want
txt1 show records from table empDetail having age between 18 to 25
txt2 show records from table empDetail having age between 26 to 35
txt3 show records from table empDetail having age between 36 to 45
txt4 show records from table empDetail having age between 46 to 55
View 3 Replies
View Related
Feb 18, 2014
I am trying to build a form that can show the total from the amount that the report generate.
View 3 Replies
View Related
Oct 19, 2012
How can I sum the amounts in the list box column and show the total on the main form?
View 2 Replies
View Related
Aug 19, 2014
I need a report that show Total ordered for week by Fish for a single customer. I have customer table and OrderDetals table with order date. Shipping date Monday to Friday. When preview the report the it shows the current week Total order quantity for each fish for single customer. How do I get this.
View 2 Replies
View Related
Sep 10, 2013
I've created an access chart to show total sales ordered by customers.
I'm using a form with 5 comboboxes to select 5 particular customers from a customer table and pass this information into a query.
This query is then used on a report to create the graph.
Finally there is a button on my form that prints this report.
My problem is that the chart displays the customers in alphabetical order and I would like to order the customers numerically based on total sales value.
If I change my query so that the total sales order by is ascending then when I run the query from the report I am asked to enter a parameter value for the total sales field. Entering nothing and pressing ok simply returns the same graph I would have got had I not changed the order option at all.
*The customer filed in the report is set to group by and my totals field is set to sum - but I need both of these set to produce the graph.
View 3 Replies
View Related
Nov 16, 2014
The aim of what I am doing is to create a monthly statement to give to our intermediaries that shows the commission they will receive each month for the deals they have referred. I have managed to create this report, HOWEVER I can't figure out how to filter out which month I need, so I a report for Jan, Feb Mar etc... The idea is that at the end of each month I need to run the report so only the latest month shows...
View 3 Replies
View Related
Jul 5, 2005
I have a parameter query built and am curious if anyone knows of a good way to allow the user to define which fields are displayed in the query results.
i tried looking for other threads discussing this without any luck.. not sure if it was my search terms or what. thanks so much for any help..
View 10 Replies
View Related
Jun 11, 2006
I wondered if someone could help.
I have 3 columns of data within a table and from a select query am trying to seperate certain groups.
Table 1
yr period valuehome
2006 0 100
2006 0 10
2006 1 1000
2006 1 800
2006 2 60
2006 2 50
2006 3 40
2006 3 10
2006 3 5
2006 3 70
2006 4 50
2006 4 5
I wish to show both results for period 0 and an accumulative total for periods 1-4
so results would be
year period Total sum
2006 0 110
2006 1-4 2090
Can anyone help?
thanks
Paul
View 5 Replies
View Related
Mar 8, 2008
Hi,
1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.
2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)
3) I start with daily (Lets don't be too overly ambitious).
4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.
5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?
PS: Please forgive my ignorance :o:(
Thanks (In advance) & God Bless.
View 2 Replies
View Related
Jan 4, 2006
i have about 14 fields that relate to subjects, basically i want to be able to run a query that searches for all the people who have choosen the module and report bak who they are..
now i know i can do this 14 times over but is there a way that the user can choose the subject first and the query will then run hence meaning only one query/report.. i thought of using a list/combo box but have absolutely no idea...
im very much a novice but any help would be most apreciated!
cheers
si
View 2 Replies
View Related
Feb 27, 2007
I have a query filtering question. Please note attach pic for further details. I am trying to put together a search in a form to seach for a certain color or color series from a Qery from two two different sources (from a Combo Box and from a Text Box). I am not sure how to set this up in the query.
Can someone please point me in the right direction?
Thanks,
Kilch
View 2 Replies
View Related
Jan 11, 2008
OK, new problem to solve. I don't know if I am going about this the right way, so please feel free to make suggestions on better ways to acheive an outcome!
I have a query "qryBuildsPrinted" which searches a specific table for appropriate records, feeds these selected records into a report "rptBuildSlips" and prints them.
I am now trying to allow the user to print these build slips selectively, instead of printing out the whole lot pulled in by the query every time. My approach is to use the existing query, but filter the query based upon some selections made by the user in one of my forms. I am having trouble getting the query to filter successfully.
I realise that you can filter both the query and the report independently, so I probably only need to filter the report, but that is not working for me either!
Here is my code:
Private Sub cmdPrint_Build_Slip_Click()
Dim intBikeID As Integer, intBuildID As Integer
Dim varItm As Variant
Dim ctl As Control
Dim intCounter As Integer
Dim intRecords As Integer
Dim varArray() As Long
Dim strFilter As String
Dim blnPrinted As Boolean
Dim msgMessage As Variant
'Print Build Slip for selected bike
'Set Control
Set ctl = Me.lstResults
intRecords = 0
intCounter = 0
'check if a selection has been made (kinda dodgy)
For Each varItm In ctl.ItemsSelected
GoTo Selection_Made
Next
GoTo CleanUp
Selection_Made:
'Count how many records have been selected
For Each varItm In ctl.ItemsSelected
intRecords = intRecords + 1
Next
'Clear Array and Re-allocate Upper Bound of array (Array starts at 0)
ReDim varArray(intRecords - 1)
For Each varItm In ctl.ItemsSelected
'determine BuildID of selected record
intBikeID = ctl.ItemData(varItm)
intBuildID = DLookup("[BuildID]", "tblBuilds", "[BikeID] = " & intBikeID)
blnPrinted = DLookup("[PrintedSlip]", "tblBuilds", "[BikeID] = " & intBikeID)
If (blnPrinted = True) Then
msgMessage = MsgBox("One of the bikes selected has already had a Build Slip printed. Please adjust your selection", vbOKOnly, "Build Slip Already Printed")
GoTo CleanUp
End If
'Fill array with values, given how many values are present
varArray(intCounter) = intBuildID
'Adjust counter to go to next array value
intCounter = intCounter + 1
Next
'Select Filter String
Select Case intRecords
Case 1
strFilter = "[BuildID] = " & varArray(0)
Case 2
strFilter = "[BuildID] = " & varArray(0) & " Or " & varArray(1)
End Select
'Open query which displays the bike selected
DoCmd.OpenQuery ("qryBuildsPrinted")
'DoCmd.ApplyFilter , strFilter
'Print report of query with filter applied
DoCmd.OpenReport "rptBuildSlips", , , strFilter
'Close query, remove filter
DoCmd.SetWarnings False
DoCmd.Close acQuery, "qryBuildsPrinted"
DoCmd.SetWarnings True
ctl.Requery
GoTo CleanUp
CleanUp:
intBikeID = Empty
intBuildID = Empty
Set varItm = Nothing
ctl.Value = Empty
Set ctl = Nothing
intCounter = Empty
intRecords = Empty
strFilter = Empty
blnPrinted = False
End Sub
A further question that is probably on the same topic is that once this has been completed, I also need to modify my existing Update Query which makes a modification to the data in the table. Is it possible to apply a filter to an Update query also?
I realise this may not be the most effective use of Access, so as I said, please feel free to suggest alternate methods I can research.
Regards,
Jon
View 3 Replies
View Related
May 17, 2007
This query is to show aging. I have a field for processed date. I have a query that allows a date range to be selected using the Between function.
The query runs and pulls these variables from a form. It shows only those accounts which were processed between those dates.
However, it is also showing all of the accounts that have no date in this field. I need to set it up so that if the field has no date then it doesn't show those accounts.
I've tried Not (is null) for the criteria but that isn't working. I've tried a lot of stuff but nothing seems to work.
Thanks for the help :)
View 1 Replies
View Related
