I have three account types (Student, Departmental, and CC). On a fiscal month, each has a certain amount of counts per fee type. I want to find the total number of items among the three account types.
For example:
On July 2006,
The "Student Account" has 437 "Items Deposited"
The "Dept. Account" has 2,691 "Items Deposited"
The "CC Account" has 0 "Items Deposited"
I want to find the sum of these three (3128). I have 102 different months and 7 types of fees onto which I need to do this.
i have created a table that would calculate the total payments that are being made by a certain contractor. I am assuming that the contractor pays the money in three installments.
In a table I have created three fields which are all checkboxes that would state whether a payment has been made or not, for example, checkbox1 would state whether the first payment has been made. And I also have three other fields that states the amount to be paid on each installment
Now, i want to calculate the total amount that have been payed. For example, lets say that the first installment has been made (i.e the checkbox is checked), then the first payment should be added. And if the second payment has been made it should be added as well, thus finally giving the total amount that has been made.
I know how the logic should look like, but i just don't know the syntax that i should use in the query.
BACKGROUND: I have a query, in which the user decides a customer name. According to the customer name a specific custom price is chosen for the customer from tblInputProductSpecs. Based on this I do the following calculation Total Price:[Price]*[Quantity], where the quantity is already known.
PROBLEM: Since the Total Price is total price for a specific product. I wanted to calculate the TOTAL of all the Total Price/Product. But finding it really hard to do this. I tried to do Total:SUM([Total Price]), but this doesn't work. I get an error. I even tried Total Price:SUM([Price]*[Quantity]), but this also doesn't work. Is there any possible way to find this TOTAL?
I would really appreciate your help with this frustrating problem.
I have a number of fields that hold values (scores - either 0,1 or 2) that need to be summed but in some cases the value is 99 (which is recognised by our stats program as a N/A type of anwer- This occurs when a question is optional and the user hasnt answered it. I did not want to value to be null (since its not like the user didnt answer it because they missed out on it by accident but rather they could not) and it couldnt be 0 either since 0 actually means something different in the context of the questionnaire.Lets say I have Q1 which has a value of 0, Q2 has a value of 1, Q3 has a value of 99 and Q4 has a value of 2I want to get the total of Q1+Q2+Q3+Q4 for a particular entry but if the value of any or all of the questions are 99 I want access to ignore it and just sum the rest of the values i.e. the sum should read 0+1+2+(99)=3 not 102I want to be able to include this sum function in the expression builder if possible since I am using it for a report. Can someone please help?
I have created a report and i am having trouble with the =Sum() function. I would like to sum all the values that are held in a text box which has the expression of '=[BookingSF_Qry subreport].[Report]![TotalSF]*[HowManyDays]'. No matter what i try i cant get a total value of all of these.
Hey there, Im currently making a form so users can enter project information. For this particular project, the user must choose from a variety of options, and each option has a number value assigned to it for a rating. Now at the end of this form, I want a sum of the ratings, and then entered into the table. I am using a text box for the sum of ratings, and can get the sum of ratings to work on the form, but this data is entered as a 0 in the table. If anyone could help me find a way to put the actual sum in the table, that would be excellent.. thx a lot.
If it would be any help, here are the names of text boxes that I'm adding:
Health and Safety Rating, Maintenance Rating, Equipment Rating, School Size Rating, Student Enrollment Rating, SD Priority Rating, Project Requested Previously Rating
I need some help please. I am working on a performance management database. In this database we keep track of daily overtime and how that overtime is broken down into sub categories. For example, my staff worked 14 hours of overtime yesterday. 8 went for weather related overtime and 6 went to cover vacations. I have a report that shows all of the supervisor's overtime in a continuous list of running totals. But it's getting to big. I am trying to create a query for each supervisor that pulls the daily totals for the previous week and gives me that total sum. So far, my individual supervisor queries look like this. DATE, SUPERVISOR, DAILY TOTAL. They click on the report button and then they just count 7 days worth of numbers.
How do I create this?
TOTAL FOR SUPERVISOR X FOR THE WEEK OF 00/00/00 through 00/00/00 that pulls the daily totals out of my "overtime table"? and gives me a weekly total?
I am trying to create an access database to allocate people to projects and show the total time spent on projects. As peoples time can be allocated to different projects I want to add up the proportions of their times per day and show the results in a graph.The data I am entering is the date they start working on a project (Start date), the date they stop (End date) and their time allocation (e.g. 50%). How can I use this data to sum for date ranges?
I have attached an example database showing the problem. In the report the graph shows the type of thing I want for the output, clearly showing the total time spent on a project is more than 100% on one day and less on another day. Do you know how I can do this?
I have a sales form. The sales form has many Call, Meetings and Emails linked to each sales record. I want to total all the number of calls, meeting and email records related to the sales record to give a total- Touches.I've created 3 queries;
These all work fine however when I combine them to attempt to count the results it doesn't produce the correct results.I have a second query as well (no pun intended).
I am trying to sum together a column that has values in Time. The results displays as a decimal. How can I have the result display as a Time i.e. 1:20 (1 hour 20 minutes).
I have a DATE/TIME field in a table. I want this field to be optional. But when I try to insert a record without a date value for this field, the SQL fails. How do I fix that ?
Also I want to display this field only if there is a valid date...How can I do that?
I am doing some relational database work and need to rearrange and update some date values to a new field in my table. I know that this can be done with a simple update query as I've done it before, but I have forgotten how. Here is what I am trying to do:
I am starting with: 050805 in my date field
I need to update to show: 08/05/2005 because this is the correct date:
I tried: CVDate(Mid([DOH],1,2) & "/" & Mid([DOH],3,2) & "/" & Mid([DOH],5,2)) which gave me 05/08/2005 from the sequence of numbers. Sadly, this is not correct.
This thing is driving me nuts and is possibly something simple, but can't figure it out.
I have a query which gives me data in three fields : Vendorname, vendorlocation and surveydate The first two are text fields, the last is a date field. What I want is those names and locations, where the surveydate is in THIS month only. So today's month (5) is May and I want all entries for this month only.
I've created a function in the module that returns the financial year that the record is in called getFN() which works fine.
I use 2 text boxes to input the start and end date of the reports and use one of the text boxes as the input for getFN()
Basically, I can get a report that shows the values for each source in the financial year ok, and I can do the same for the values for the month selected but I can't join the two queries up in a single report.
I'm not sure if I need to have a blanket query that joins them. I'm a little stuck.
I want to make the default value of my column "TimeonOW" to be the difference in months(past 12) from the entered date in "OWStartDate" and the current system date.
Is it possible to create the following logic in query:
"If there is a "SHIFT2" or "SHIFT3" value on the last Friday in month, then change the date values for these entries to the next date (Saturday)"
In the attached sample, there is a number of such values within the "Adv Track Shift" field dated on Friday 04/25/08 in the "shift_date" field. The logic should replace the applicable dates to Saturday 04/26/08. This should work for any month, regardless if it finds either of the two shifts or both of them.
I have a field that shows dates as quarters. Right now it is just a date/time field, but that posses a problem, as the user can enter any date possible. I want the dates to be limited to March, June, September and December, and any year they choose.
I have two class variables (both arrays) among about 10 other class variables, that are not returning any values but "" for the string or #12:00:00AM" for a date. Here are my class variables:
Code: 'UPSData Class Module Private p_LetterArray() As String Private p_date() As Date Private p_LetterArraySize As Integer
My Properties
Code: 'Properties Public Property Get LetterArray(index As Integer) As String LetterArray(index) = p_LetterArray(index) End Property Public Property Let LetterArray(index As Integer, NewValue As String)
[Code] ....
As I said, tLet and tDate result in "" and #12:00:00AM# respectively. When I step through the code, the values for tmp.LetterArray(0) is assigned "src" and tmp.UPSDate(0) stores "12/25/2013" correctly.
When I assign tLet and TDate, the same thing happens when stepping through the code. I'll use the LetterArray property to describe what happens:
Get LetterArray is called. p_LetterArray(0) does equal "src" Let LetterArray is called. NewValue is "src" and p_LetterArray is "src" when End Property is highlighted in the debugger Scope returns to Get LetterArray with End Property highlighted. In checking the values, LetterArray(0) = ""
Same steps happen with the same results ("12:00:00AM" vice "")
I have a manufacturer that has sent me payments from 8/2/2012 to 05/20/2013 but there was a wide gap where we did not do business from the end of 2012 to April 2013.
How can split the date ranges up in the query so when I create my report, I can get two ranges for the same manufacture.
For example one list of payments from <#12/31/20112# and the other from >#4/1/2013#
I'm trying to create my own database dealing with hiring bikes on a daily basis.
I have created a form for the basic input, including StartDate, StartTime, EndDate and EndTime, followed by TotalDays. I added the time field so that if a client brings a bike back an hour or two after the pickup time on a later date (sounds complicated - e.g he takes it on Monday at 12pm until Thursday 14pm) then he would be charged for an extra day. It is similar to the car rental system used worldwide.
The TotalDays is currently using a simple expression =EndDate-StartDate.
Is there any way to make the form incorporate the time difference so that if EndTime-StartTime>1 then it would add an extra day to the "=EndDate-StartDate" calculation.
First of all, I didn't know how to search for the precise info regarding my question, so, If it is repeated, feel free to close this thread or delete it.
So, here it is my question:
I have a table with values taken once monthly, that is, for each person on that table, I would have a maximun 12 records per year. In that table I have the Id of the person, the date field where I store a date when I get the record, a field where I store a value and a control number field. The values on the control number field go from 1 to 20 (or less).
What I want to achieve is, given a range of dates, show the last two controls of every person, something like: id, last control date, last control number, value of last control, previous control date, previous control number, value of previous control. As you could see, I want to show in the same row the last two controls for each person, only if the happend to have two controls, if they have only one, it wouldn't be necessary to show them, so, how can I do this?.
I want to be able to display the result of a difference between Date/Time values in "HH.MM" format (i.e. yes I want the result in decimals and I don't want Access to round up or down just because it feels like doing it!). I have used the following:
Example 1:
Dim ActualManHours as Long ActualManHours = (txtEndTime.Value - txtStartTime.Value) * 86400 txtActualManHours.Value = ActualManHours
Example 2:
Dim ActualManHours as Long ActualManHours = DateDiff("h",txtStartTime.Value, txtEndTime.Value) & "." & Format(DateDiff("n",txtStartTime.value,txtEndTime. value) Mod 60, "00")
Example 3:
Dim StartTime as Double Dim EndTime as Double Dim ActualManHours as Long StartTime = CDbl(txtStartTime.Value) EndTime = CDbl(txtEndTime.Value) ActualManHours = EndTime - StartTime txtActualManHours.Value = ActualManHours
i'm going to start this by telling what i am working toward in the end as it is where the problem obviously stems from.
i am trying to create a report which will tell me how many times 'hcapp' is in a table. there are three different fields where 'hcapp' can be placed...i can easily create three different queries which return how many times 'hcapp' is mentioned in one field, but i have yet to figure out how to look through all three fields in one query...is it possible? if not, how can i have a report call all three of these queries and then sum the total?
