This is what my query looks like at the moment:
Field: Issue,Rejected,Inspected,Area
Total: Group By,Sum,Sum,Where
Criteria:,,,[Area:]
What I'd like to have is a calculated field after "Inspected" which would show the grand total of all "Inspected" for that "Area".
Example:
Issue, Rejected, Inspected, Total Inspected
Scratched, 5, 100, 130
Chipped, 1, 10, 130
Dented, 2, 20, 130
Any ideas on how to get the "Total Inspected" field?
Thanks!
I have several fields that I fill in on a form and they all need to add up in the last field. The first way that I tried this worked good for the initial data entry on the form. However if I need to edit one of the fields in the form then it resets the TotalCost field and I have to enter all of the numbers again. Is there some way that I can make this work? Or is this an Access quirk I will have to live with?
I tried to do this two different ways.
Here is the code for the first method:
Option Compare Database
Dim C1 As Long 'For Total Cost
Dim C2 As Long 'For Total Cost
Dim C3 As Long 'For Total Cost
Dim C4 As Long 'For Total Cost
[Code] .....
This way works great the first time that I tried it, I had to re-enter info in all the fields if I wanted to change one.
Here is my second method:
Which didn't work, i received an error message:
"The expression After Update you entered as the event property setting produced the following error: Invalid outside procedure."
And then nothing changes.
Code:
Option Compare Database
Dim C1 As Long 'For Total Cost
Dim C2 As Long 'For Total Cost
Dim C3 As Long 'For Total Cost
Dim C4 As Long 'For Total Cost
[Code] ....
So I know this second method is incorrect.
I have to count the number of records in a subform that meet certain criteria. I have a yes/no field and presently have a field in the subforms footer that =Count(*) and I would like to be able to count only the "Yes" records. Is there a way to use the count function to do this.
View 4 Replies View RelatedHi all,
I'm fairly amateurish on Access so be gentle.
I need to ask Access to calculate a total number of records but only includes duplicate entries once.
For example: If the field entries were A, A, A, B, B, C, C, D
It would return a total of 4 (counting A, B, C & D only once).
Can anyone advise on how I can do this? My only alternative is to print the database out and cross out duplicates. Not the most interesting way to spend my afternoon.
Thanks!
Stu.
I have a Table which has a column that contains a Data (last day of the month: 8/31/2013, 9/30/2013) and also contains a Balance field.
What I am trying to accomplish is a query that will provide me with the total for the last month (Sept), and then also provide me with a Percent break down from a whole balance.
I have the following SQL for it:
SELECT ATB.FC, Sum(ATB.Bal) AS SumOfBal, (select sum([Bal]) from ATB) AS Total, Sum([Bal]/[Total]) AS [Percent]
FROM ATB
GROUP BY ATB.FC, ATB.MMDDYY
HAVING (((ATB.MMDDYY)=Date()-Day(Date())));
Right now, when I run the above, the Sum(ATB.Bal) pulls just the month of September, but the Percent is pulling a percentage of each Sept. Value against the whole Balance for the Year. So, when I sum the percent column to get a total of 100% for the month of September, I am ending up with a number less than 20%.
I've tried many things (compact and repair, Nz(,0), sum()) and I can't get it to work.
I entered =[field1]+[field2] but it behaves like if I had entered =[Field1] & [Field2].
Yes instead of adding the numbers it just puts them together.
For example: Field1=3 and Field2=5 instead of field3=8 it shows Field3=35.
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..
I have a query, and I want a field that shows the current balance as of that transaction, eg:__________________________________________________ _______|__Transaction ID__|__Type_______|__Amount__|__Balance__||__1______________|__Deposit_____|__£10______|__£1 0_______||__2______________|__Deposit_____|__£30______|__£4 0_______||__3______________|__Withdrawal__|__£15______|__£2 5_______|So far, I have this:SELECT [Transaction ID], [Type], [Amount], (SELECT Sum([Amount]) FROM tblTransactions AS tblTransactions2 WHERE tblTransactions2.[Transaction ID] <= tblTransactions.[Transaction ID]) AS BalanceFROM tblTransactionsWHERE [Account ID]=1;Although this does not look at the 'Type' field; it just adds the amounts; regardless of it being a deposit or withdrawal. I'm really not sure how to add this.Thanks in advance.
View 11 Replies View RelatedBelow is the sql code I have for a CrossTab Query. Total as GrandTotal
shows GrandTotal of all weekly columns. Is there anyway I can get the
Query Display to sort on the GrandTotal Column?
PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 );
TRANSFORM Sum([Trends-1-3TON-WEEK].Totals) AS SumOfTotals1
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory
ORDER BY Sum([Trends-1-3TON-WEEK].Totals)
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;
I have a table with dates in field1 and an amount of seconds in field2.
field1 field2
01/01/2015, 1345
02/01/2015, -132
04/01/2015, 259
I would like to produce a query that performs a running total in the third column like so:
field1 field2 field3
01/01/2015, 1345, 1345
02/01/2015, -132, 1213
04/01/2015, 259, 1472
This is quite simple to achieve in Excel. (eg =SUM($B$1:B3))
What is the query formula for Access?
I have qry with these fields: DateOfPayment and Ammount.
I would like to add another field with running total sum. I am trying this:
RunnTot: Format(DSum("[Ammount]";"qryCFSUM"; [DateOfPayment] <=#" & [DateOfPayment] & "#" );"0 000"" Kč""").
But It still does not work.
Example of my data in "qryCFSUM":
DateOfPayment
20.1.2013
31.1.2013
30.3.2013
Amount
1 2000 Kč
15 456 Kč
23 465 Kč
And what I would like to have:
RunnTot
1 200 Kč
16 656 Kč
40 121 Kč
i would like to sum up 5 fields and save (or just show it in the form view is enough really) in a seperate field, i have managed to sum the 5 fields, but it summed up all 5 fields in all records, i'd like to have my form show the sum for the record, not the table.
it would be ideal that it would refresh when one of the 5 fields data changes, i.e from 1000 to 2000. again updating in the form view is what is needed.
I have three tables (Group, Customer, Savings).
Every member of a group do savings once every 15 days and each group has maximum 20 members, group has one to many relationship with customer and customer having one to many relationship with Savings (there is no direct relationship between group and savings) i have created a continuous form for saving entry. in this form i select a group which is unbound and based on that group, its members are listed in the combo box named CusID, after selecting customer other saving details are entered.
What I want, is that for every group i want a total saving for all that specific group's members selected in the mentioned unbound combo box in that continuous form, this total should be based on the group selected in the mentioned unbound combo box, in case i select another group the total should also be based on newly selected group.
I have a DB to keep track of my employees. I have one table with named EmployeeT with the basic information like name, address, phone number, etc. And another table named EmployeeHistoryT which tell me about there hisorty in the company, basically, If they were Late, did not show up, cancelled from that day. They share a relationship threw the employee ID. IN the history table, I have DateofAction:, TypeOfAction:, and Description:. In the type of action area I have it set to were you type in what they did, wether it be Late, Did not Show up, Cancelled, etc. I want to be able to get a total about of Late, Cancels, etc. I want to know that John Smith was Late 16 times, cancelled 10 times, and Did not show up 2 times. Is this possible with only one feild.
View 6 Replies View RelatedAfter testing it, the database got corrupted. I had backed it up just prior to using this.
It was something like = Sum(Abs[AmountPaid], [Paid] = "X")
I have a continuous subform with an "AmountPaid" column. The total is displayed in the subform footer. I need it to display the total for only the fields with an X in them denoting that they were paid. This total should match the statement we receive.
After clicking the button to put the X in the Paid field, then I used the formula to update the AmountPaid field.
Joe..........10..... X
Al..............5.....X
Flo.......... 25
.....Total = 15
I want to calculate running total and find out the date when that total is greater than a number.
My initial plan was to use Dsum and then use dlookup to find when that Dsum value > [Fixednum].
But when I try Dsum and use Totals in query, access shuts down. maybe because of 15000 rows.
I have attached a sample database that shows what Im working with and what I would like.
I have a simple table named [Groups] containing a Primary key ID, a Group name, a Group Leaders name and a date field. The date field merely lists the date of a particular Group related occurrence.
I need to know how many records have dates entered and assumed a simple total would achieve this. The Total is correctly shown in the appropriate row but most, but not all, of the date records are converted to show the same 'total' number.eg the date 28/01/2014 is changed to !4. Quite a weird occurrence.
Is it the case that date records cannot be totaled, in which case, why not all?
the relevant fields look something like this:
TellerNum - Date - Field1 - Field2 - Field2
I need a way for my query to take all rows with the same TellerNum and add Field1, Field2, and Field3 together for those records. So, there may be 20 records with the same TellerNum, and I need Field 1, 2, and 3 to be added together and then totaled for all 20 records to give me 1 grand total. I need this to be done for each TellerNum, so that the results will be a grand total for each TellerNum. So, the results may look something like this:
TellerNum 1: 486
TellerNum 2: 300
TellerNum 3: 240
etc..
I am trying to create a query that ONLY shows me the total count of each combo box choice for one field. It sounds very easy and I'm sure it is but everything I try tells me I am using the wrong syntax, or requires me to have rows when I only want column totals.
I have a combo box with a dropdown of 10 common places that people travel to. I want the report to ONLY show the total number of people who chose place 1, place 2, place 3... etc. But I always have to do according to date or time or something. I want it to look like this
Place 1 Place 2 Place 3 Place 4
13............36.........3........77
So essentially there would only be one row. But access will not let me. Is there something I'm doing wrong? Or is this not possible?
I have a table called Price list, and in it I have two fields, one called Service and The other called cost. The services are listed and their matching prices are listed beside.
In another table called appointments, when making an appointment, I have linked the information so that I can choose from a dropdown from the price list table, under Service type.
I need to be able to create a receipt for the appointment. How can I do that in a query format?
=([frmservices_2013].[Form]![sub2013])
I have this expression in a field on a form. It's been a really long time since I worked in Access. Is sub2013 a field on a form, in a table?
It's doing a running total of a field from a form. I can't find a subform with this name, or a field anywhere, I'm drawing a blank.
using Access 2007....
I need calculated field (in a query) that executes a running cummulative total for three conditions: "="&Project Name, "="&Service and "<="&Date.
I can do it in Excel using SUMIFS(), but have no clue on how its done in Access 2010 !
The "Balance" field should be the end result:
Project Service Date Amount Balance
A Welding 1/1/2012 100 100
A Welding 1/2/2012 120 220
A Wiring 1/3/2012 150 150
B Welding 1/1/2012 103 103
B Painting 1/2/2012 124 124
B Painting 1/3/2012 155 278
C Welding 1/1/2012 106 106
C Wiring 1/2/2012 127 127
C Wiring 1/3/2012 159 286
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.
Hi.
I am setting up a database to run a shop. In my 'Orders' form, i have a subform of the order details (The item (combo box), price, quantity and subtotal (price*quantity).
In the form in which the subform is displayed, how can I display the total of the subtotals? Is it easy, could anyone show me roughly what i need to write?
Many, many thanks,
- Smiffezo.
Hi guys- totally in the frying pan here...I can't seem to get past this initial stumbling block with Access
i've made a simple form that "should" create a primary key from the first two digits of a clients last name and last four digits of their phone number...i've tried an update query button and a refresh data button...the key field gets filled in on the form, but it will not transfer to the underlying table...I keep getting a null value error for the key....any help would be greatly appreciated...
heath
I have a report with a calculated field. This calculated field needs to pull the value or total from a field in another report. I think the formula is :
=([AssmntC].Reports!totalreqamnt4)
but I'm getting ?Name as the result.
The report is AssmntB where I need to have the value copied.
The original report is AssmntC and the field is totalreqamnt4 where the value is originally calculated.
totalreqmant4 is also a calculated field which sums fields from a query.
Help with the formula? please?