is this even possible and if yes how do you write a report (or query for the report) to do:
201030 = 1 spring semester
201050 = 1 summer semester
201070 = 1 fall semester
so then attendance from 201030 till 201230 = 7 semesters
and also going the other way so 201230 - 201030 = 7 semesters
I have 3 reports
Report 1. is attendance from January to June
Report 2. is from June to December
Report 3. is from January to December
The are all run from the same query with different Between Dates.
The scenario is that Paul attended the support group from Jan to June so in the Jan to June report he is counted once. The query removes duplicate values
Paul attended the same support group from June to December so in the June to December report he is counted once. The query removes duplicate values.
This shows the Paul attended he support group from Jan to December so in the June to December report he is counted once. The query removes duplicate values, if you removed the duplicates from this query he would be counted twice.
Not all people attend the same group for 12 months but if they do the are a duplicate and Paul is a duplicate. That is ok because we need to know now many duplicates there are for the year.
If I print all 3 reports with the duplicates remove query and you looked at the totals it would show (Example) 30 attendees in the 1st report and 30 attendees in the 2nd report but in the 3rd report it would show 59 not 60 which is the total of reports 1 & 2. we need to report these figures as 1st 6 months with 30 and the second 6 months with 30 but the whole year would be with 59 and 1 duplicate.
The way it is now I have to run all 3 report and do the math by hand this way.
Is there a way on the to do the math with code calculations on the full year report with the figures from the 2, 6 month reports to automatically show total attendance for the year 59 with 1 duplicate?
I have a database and one of the functions is to track the purchase of fuel. Part of purchasing fuel is registering what the current odometer reads. My query gets the Vehicle ID, Date of Transaction and Odometer reading. I want to show a report that shows how many miles the vehicle is has traveled for a certain amount of time. If it's for 2013 then I'll put the criteria for 2014 in the date. This will give me a list of all vehicles and their odometer readings. I then sort the date by ascending.
In the report I group on vehicle ID and then I want to show all their transactions and add up how many miles were driven.
Vehicle 1
1/2/201
1/5/2013
1/15/2013
ETC...
Total Miles Driven in 2014: XXXXXX
Hi guys,
I have a prob with summing up my subtotal figure in my query. Here goes:
I have an Orderdetails Table with : Qty,Unit Price,Discount.
That makes my total in query "Total:[qty]*[unit price]*(1-[discount]). However, I can't use the SUM to sum up my "totals" as subtotal. How am I going to remedy this problem? THanks
Hi there hope you can help :)
I have created a form with a subform and has the following information displayed:
(Form)
Tenant ID Mobile Telephone Number
Title Credit Reference
Surname
First Name
(Subform)
Date Due Amount Paid On
(Form) Total Of Arrears -
I need text box at the bottom of the form to caculate each tenants arrears.
To show if the tennant has any arrears there will be no date shown in the [Paid On] box (If there is a date in this box it means the tenant has paid and therefore no arrears) so the [Amount] for that tenant owes will be totaled up in the box at the bottom of the page [Total of Arrears]. Also a tenant might miss a payment on one week and a pay another rent payment on time. Please could you show how to do this in access and not by VB code.
I've attached my database - well thanks for looking and hope you can help
HeadintheClouds
I kinda new to access but What I need to happen is I have a Bill Table, and I have a search query, you type in the customers last name and the different services they request come up. I have it do a report, at the bottom I want the Cost field to add up and display total cost. any help would be apreciated...
thanks,
Not exactly sure if a query is what I need in this situation or if it is what I need how to get there.
In the attached db example on the case form
there is a section for technicians to go in and take credit for steps that they performed as part of the overall case
So clv1 might be done by User A
Then clv2 might be done by User B
But the next case it might be switched.
I need a method getting the sum of the total clv's field for each technician in two different ways
1 would be the total clvs for USer A for the current month,
2nd would be the total for the year- or actually a prompt for a date range
Tried using the query wizard but it doesnt combine the names
Then I tried an individual query on each set - that worked but then I only get the ones in the first column - not all the clvs that they did.
Hope that makes sense - is there a way to do this or am I in the wrong forum for trying to figure out a way?
Well the db example is imb so it cannot be attached
Thanks for any help.
I have a continuous subform (frmLabor) that has an unbound box in the footer. This unbound box totals all the numbers in the Total1 field using DSum. However, when I change the record in the main form (frmCustomers), that number still stays there, and when I add records (service hours) to the subform under a new record (customer) in the outer form, those numbers sum. I understand what's wrong, I just don't know how to fix it. How do I get it to calculate ONLY the records (service hours) in the subform per record (customer) in the outer form?
-Jason
I have a form that has 7 text boxes, each of which contains a time value (hh:mm)
Two of them are to be permanently totaled into one text box (lets call it Rate1), and the other five are to be totaled into another text box (lets call it Rate2) - BUT, if the value of a combo above each one of the five text boxes is set to 'BH', I want the value to be totaled in with the Rate1 totals.
I did think about doing an After Update event on each of the five textboxes where the value of the totals text box is updated by doing the current value + the value in the textbox (and the totals text box that is updated is dependent on the value of the combo) - but that idea landed flat on its face upon the realisation that if a user entered a value, then decided to change that value, the old value would not be deleted from the total.
HELP! I’m working for a charity in a small town Tanzania and there is not a soul for miles who knows anything about access.
I have created a database to track requisitions of Office Supplies. I have table full of the requisitions which contains multiple rows for each item we have in the store and the quantity requisition on each occasion.
What I want is a query that will calculate the total quantity of each item requisitioned in a month. Is there an easy way to do this??
I'm needing to total 8 records with the same id number, but different amounts in the the field named 'amounts'. For instant, if the amount field had:
23
23
25
67
45
23
88
97
I need a formula to total the above figures so it can equal 391.
we have a calculated field in an append query Line_Cost which eventually creates a purchase order.
We have to have a minimum order value of £1500.00, so, we need to total the values before running the append query.
I'm trying to do a calculation in access that will allow a user to receive a discount in one field and show the results in another. The form looks like this. Having a hard time figuring out the line total. It's giving me a the wrong amount. The employee discount is $.20 and the price is $2.27 for example.. Thanks
Bound text box [UnitPrice] - Bound text box [EmployeeDiscount] x Bound text box [Quantity] =
Bound text box [LineTotal]
I have two times I need to subtract in a query. When I subtract these in the query I get a decimal point. How can I convert this data. Thanks
View 1 Replies View RelatedHi everyone,
I am familiar with databases and basic SQL, but never used Access, so your help is greatly appreciated!
What I *think* I need to do here is take the results from one query, subtract another query from that, then create a table of the results, so that I can make a chart in a report. I say that I *think* I need do that because I’m not confident that this approach is the best but that’s a whole other story...
Please look at the following tables below for an example. The Item table has ItemCode as a primary key. The Bids Table has multiple rows for each ItemCode showing bids for the item:
Item Table
ItemCode Date Address
JH231 2008-Feb-20 123 Test St.
KH831 2008-Feb-21 512 Test St.
Bids Table
ItemCode Amount
JH231 100.00
JH231 60.00
JH421 310.00
KH831 20.00
KH831 40.00
KH831 10.00
KH831 90.00
To make a pretty graph, I would like a table (so a query, or macro, or VBA or something?) that returns the minimum Amount for an ItemCode subtracted from the maximum Amount for an ItemCode based on the example above. What I would end up is something like this:
ItemCode Difference Between Maximum and Minimum
JH321 250
KH831 80.00
I hope this is clear. Any help on how to do this from you Access pros would be great, OR if I’m taking the wrong approach, suggestions on what I should try next. Thanks!!!
Marc
Hi, I have a data base that is used to keep track of stock level. When i issue a stock item and move to the next record I would like the stock level to be reduced by 1, any ideas.
View 2 Replies View RelatedHi, here's the situation:
I have a query A, that gives me the a set of numbers.
I have a query B, that gives me another set of numbers.
Numbers in B are always equal or larger than those in A. (B >= A)
I want to create a new query C, that does the subtraction. (C = B-A)
Here's an example to explain what I want.
Let's say I have A=4,7 and B=6,9
Now, when I create the query, I get the following result:
A | B | C=B-A
4 | 6 | 2
4 | 9 | 5
7 | 6 | -1
7 | 9 | 2
What I want is this:
A | B | C=B-A
4 | 6 | 2
7 | 9 | 2
How do I do that?
Thanks.
My new job requires me to use access so I'm learning as I go. :confused:
I have already used previous threads from this forum to answer another problem I encountered. Thanks for providing this forum to the public!
I am attemping to run a query that will subtract military time. (I am not allowed to change the format of the times in the original tables. )
Example:
Column A Column B
1111 6.5
I need 1111 - 6.5 in Column C
I found the following code on this site in a search for a method to subtract two dates ie [Startdate] and [EndDate] and display the result as a whole number. ie 2, 6 etc. This code finds the difference but displays the results as 1 year 5 months 2 days, where I need the equivalent as 17.
Can this code be modified to produce the desired result and if so how would I go about it. Thanks for the help.
Option Compare Database
Option Explicit
Public Function getTimeElapsed(StartDate, Optional EndDate As Date) As String
On Error GoTo Err_Handler
If IsNull(StartDate) Then
getTimeElapsed = "No startdate"
Exit Function
End If
Dim YY As Integer
Dim MM As Integer
Dim DD As Long
' use current day if EndDate is not supplied,
' + 1 makes both days inclusive.
EndDate = IIf(EndDate = 0, Date, EndDate) + 1
MM = DateDiff("m", StartDate, EndDate)
DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
' recalculate if DD is negative.
If DD < 0 Then
MM = MM - 1
DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
End If
YY = MM 12 ' integer division.
MM = MM Mod 12 ' remainder.
getTimeElapsed = YY & IIf(YY < 2, " year ", " years ") & _
MM & IIf(MM < 2, " month ", " months ") & _
DD & IIf(DD < 2, " day", " days")
Exit Function
Err_Handler:
MsgBox Err.Description
Exit Function
End Function
Any Help!
I have fields that are time formated 99/99/99" "00:00;0;_ and I want to subtract them.
I need to get the results in two forms; first in Days and second in Hours. I have used the obvious subtract one from the other and it gives me days in a rounded numbeer, but the hours is not working out.:confused:
DB
How do i subtract one value, worked out using a query, from another value, worked out using a query, and display it in a text box?
View 7 Replies View RelatedBefore I start implementing this, I would like to know if it is possible to do in principle
I want two fields, say TimeStarted and TimeEnded. I then want to subtract the two from one another to see how long the task took.
Is this possible or would i be wasting my time trying it?
I am looking to subtract number inside the same field. and then return the difference to a new field (column). This data is used to generate a graph and the this is currently done by hand. I know there has to be an
easier way to do this.
Below is an example of what I am looking for.
I have a table called Numbers, and fields or columns called...
Value(s) Result(s)
-70 0
-70 1.02
-68.98 1.09
-67.89 3.11
-71 .71
-69.29 1.52
-67.77
So what I am doing is subtracting row two from row one. The difference is then put in another field(column) in row one. The process repeats, row three from two, four from three, five from four, etc, etc. This occurs for hundreds of rows.
Is there a SQL statement that can do this? Your help is much appreciated!!!
Thanks for your help.
My form consists of a textbox with a selected date. I have a combo box with integers 1-15. I have a table with 15 people and their birthdays.
I am trying to find the birthdays after a selected date by the integer selected in the combo box. For example, if I select 2/1/2013, and pick 7 in the combo box, the subform should show me people with birthdays 2/1 through 2/8.
I have a query with a datediff function. This is my query expression Expr1: DateDiff("d",[DateofBirth],[Forms]![Calendar]![Text1]) The criteria is <=[Forms]![Calendar]![Combo3].
It displays some data, but it's not consistent at all. I'm hoping to fix this without any SQL changes or form code. But if that's what I need, I'll do it.
I'm using access 2010, and I'm fairly new at it.
I am having a problem, probably due to my inexperience with Access. Here is the seniero:
-I have a form/table set up for operators to enter numbers on a daily basis.
-I am setting up queries/reports to display not only the entered data, but also calculated values from the entered values
--I have no idea on how to subtract a previous day's value from today's value and report that value.
- Since Access gives no "ownership" to any value I do not know how to reference yesterday's value in today's calculation.Any Ideas?
Example
Record #1
Date - 1/17/06
Value - 15,232
Record #2
Date - 1/16/05
Value - 14,111
Desired output
Date 1/17/06
Value - 1121
I have a field called DATE_END_DEERS which is in a format of YYYYMMDD (20060530). I need to run a query that shows 6 months subtracted from this date. I can never get an answer that is even close. Can someone please help? ACCESS2000.
View 3 Replies View Related