I am trying to calculate 5 fields within my table in a query, but for some reason some rows do not calculate as not all the fields contain a value (blank field), is there anyway that I could bypass this issue?
View RepliesI need to pull 2 fields from 2 different queries, then calculate them. Those 2 queries has the same structure, just one is last year's data, the other is this year's. :rolleyes:
I tried using the 3rd queries to combine them, then calculate from there, but then I had no clue where to go from there. I don't sql...I was wondering is there anything to do with sql? :confused:
I'm stucked so far, how can I solved it? :mad:
I have never created a single query that is this complicated.
I have a time card database that I created back in 2003 and over the years more and more new task have been added to track volunteer hours has been incorporated into it.
The current task at hand is to determine if each member has achieved or meet certain requirements each year.
They are attend 8 or more meeting and check into 5 or more nets and be Net Control Operator for at least 1 radio net and be involved with 1 or more Public Service events. Sounds simple right?
I have a table that contains all the information that is need to get the result I want but am having a problem getting that result with a single SQL query. Everyone you talk to says use SQL query to obtain calculated vales and never store/save them.
So from the work table below meetings consist of 4,5,6,8,9 and nets consist of 10,11,14,15 and events consist of 2, 13 and the NCOflag is T/F.
So those are all the parts, count the number of meetings, nets, events and NCO and set the meets requirement flag T/F
I keep getting "You tried to execute a query that does not include the in specified expression <name> as part of an aggregate function. (Error 3122)"
In this case it keeps complaining Callsign.
You did not enter an aggregate function in the TRANSFORM statement.
Here's the statement I created:
SELECT [Work Hours].Callsign, [Work Hours].[Work Code], [Work Hours].NCOflag,[Work Hours].SubMtg, [Work Hours].SubNet, [Work Hours].SubNCO, [Work Hours].SubEvnt, [Work Hours].ReqSubTotal
FROM [Work Hours]
WHERE (((Year([Date Worked]))=(Year(Date()))))
[Code] .....
It is failing on the first HAVING (([Work Hours].SubMtg) =Sum(). I have tried many variations and changes but don't see what is wrong with it.
I am trying to calculate the difference in dates between two fields. How do I find the difference in days between field one which contains the date 04/12/2011 and field two which contains the date 04/12/2013? I have tried to use the datediff function, but it keeps telling me it doesn't recognize the field name, even though the spellings correct.
View 3 Replies View RelatedI have to create a query in access that will calculate two Date and time fields [Date & Time Left]/ [Date Returned], need to figure out between the two fields. Trying to identify when the rep returned the call and the number of business hours (6:00am - 4:30pm) it takes to return a message in Ms Access 2010.
The only issue is the calculation has to be done by time and so I have to calculate what time they left the message(so the difference between [Date & Time Left] and [Date Returned) [Date & Time Left] and when the rep returned the message which is suppose to be [Date Returned] but the problem with this field is the data entry is in date format (10/9/2014, 00/00/0000) of Date and not Date and Time like the [Date & Time Left] field, so I don't know what to do now. Not sure what to do now not a database that create or have allot of control over.
I have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.
All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.
I've been trying with Totals in the query and crosstab queries but am not familiar with them.
I have a form for entering safety training which includes Training Date and Expiration Date, this form is called "Scheduled Training".
The table containing training courses with their requirements and frequency is called "Course Requirements". Within this table I have a "Frequency" field (1, 2, 3, 4, 5, 6) and another field "FrequencyPeriod" (Year, Month, Initial, As Needed).
What I'd like for the Expiration Date on the form to do: Use [ScheduledTraining].[Training Date] and look at both the [CourseRequirements].[Frequency] & [CourseRequirements].[FrequencyPeriod] to populate the Expiration Date.
I am creating a Make Table Query and calculating new fields based on current and prior year-end numbers. If the prior year-end number does not exist (Is Null), I want the use the current rate or calculate the change in rate. I have typed the below in the Field Box:
CHG_IN_PGM_RATE: IIf(([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]) Is Null,([MASTER LIST CURRENT]![PTD_PM_PGM_RATE]),(([MASTER LIST CURRENT]![PTD_PM_PGM_RATE])-([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE])))
However, when running the query, I get the attached error message.
The screenshot will also show how the two tables are joined.
I want to count number of mountpoints appear in the table.
As below sample table,
- Different mountpoints will appear, based on number of server using it. As you can see, the mountpoints will have repeating name based on number of server using it.
- The Count_Mountpoints will calculate number of total Mountpoints appear in the list.
MountpointsUsedbyCount_Mountpoints
/a/ins2server12
/a/ins2server22
/a/ins1server31
/b/ins2server31
/b/ins1server41
I only managed to get number of TOTAL mountpoints.... but by individual's mountpoint total count...
I found the attached example a while back (can't find the site again though ) and it calculates a moving average. I've hacked out the parts I need for my own work and I can create my moving average query without an issue.
However, I need to extract the MA data into a table so planned on using append. I kept getting type errors so I tried make table to see what type it was creating and it appears to be Short Text rather than a number.
I've added an extra button and Make Table query to the example.
As far as I can tell from the code, the moving average value when calculated is a Single. However, when I write it to the table, its a Short Text.
How do I make the created Table use Number Type for my calculated moving average?
Using Access 2013
Hi, can someone help me with this formula.
i have one field called notice_1 this field is a combo boxs multiple choice ( YES - NO )
Then i have 1 field called score_1 ( if notice_1 = "YES" then let score_1 = 5 else then let score_1 = 10 )
Does this make sense , I am an, amateur at access 2003,
Thank You For any help i can get...
John Calcitrai
I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.
Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).
In a query that utilizes a table containing a yes/no field, is it possible to calculate the yes/no field and get a numeric return? If so, can you share how this could be accomplished. Thanks
View 5 Replies View RelatedI am trying to get one of my fields to calculate this expression which includes other fields:
If "Financing Type" = 1 or 2 THEN 20% * "Loan Amount" OR
If "Financing Type" = 3 or 4 THEN 100% * "Loan Amount" = 2,000,000.
I thought that this would work, but it only works in queries or reports.
=IIF([FinanceType]<3,[LoanAmount]*.2,[LoanAmount])
I would like this to work in the table.
I have 2 tables - Customer and Carrier.
Each table has 3 headers (fields). Address, Latitude and Longitude.
I would like to know, how I take the value of the field (i.e Latitude) in each table for my calculation?
In Excel, I can specify the column (i.e Range("B" & x) - where 'x' is the row number)...but how do I do it in Access.
Code:
Dim db As DAO.Database
Dim rsCustomer As DAO.Recordset
Dim rsCarrier As DAO.Recordset
Set db = CurrentDb
Set rsCustomer = db.OpenRecordset("Customer")
Set rsCarrier = db.OpenRecordset("Carrier")
[Code] .....
I have a database that I need to calculate a query that is based on 3 dates. For example...I have an admission date and a discharge date in my database.
If there is no date in the discharge date then I need it to calculate the number of days between the admission date and "today's date." If there is a discharge date then I need to calculate the difference between admission date and discharge date.
Basically i need a length of stay figure. I can figure the formula in excel but cannot get it to work in access. Here is my formula in excel
=If(ISBLANK(DischargeDate),Sum(today()),sum(Discha rgeDate-AdmissionDate)
I would like to achieve the following task described below:
Just for an example i have two tables = Table1 and Table2
Table1 contains following fields: ID , CusName, Price , Date_
Table2 Contains following fields: ID, CusName, Price, Date_
I would like to update Price and Date Field of Table 1 where Table 1 ID matching with Table 2 and Table 2 Date is maximum(most recent date).
I am trying to take the content of two fields from one table and dump them into the other table, I created an Append Query pulling up just the main table and appending to the destination table but it doesn't copy the info when I run it. I also pulled in both the tables (and yes they are joined) doing the same as above and still it doesn't do anything when I run it.
View 1 Replies View RelatedHere is an explanation of my database and what I am trying to accomplish. The tables and their fields I have in the database are as follows .....
EmployeeT
EmployeeID - Primary Key - Number (not an autonumber because they are county assigned)
LastName - Text
FirstName - Text
EmailAddress - Text
JobTitle - Text
MobilePhoneNumber - Text
Notes - Memo
[Code] .....
All users will interface with a form that will fill in data on the TransactionT table. I would like the data that is being input into the TransactionT.Quantity field to change the data ItemT.ESBuildingQty or ItemT.D3Qty based on which is chosen from StockLocation (they will only be able to choose either "ES Building" or "D3" when inputting the data).
For example, if the stock amount at the ES building of a particular item is 3 and the stock amount of that same item at D3 is 1 and a user inputs that a quantity of 3 of a specific item are taken from ES Building and Added to D3, I want the new values of ESBuildingQty to be 0 and D3Qty to be 4.
However Instead of entering the postal code continually or selecting from the combo box. How do I set zipcode 1 and zipcode 2 based on fields in a form. I would like to use the current FROM (Default zip based on the current job in form) and TO zipcode [Project Postal Code] that is on the form at that time. Not necessarily combo box.
Currently you have
zip1 = Me.Combo2.Value
zip2 = Me.Combo4.Value
I want Zip 1 to be Default zip (however I can still change it if needed) ZipCode field is [Project Postal Code]
Here is the full code:
Private Sub Command6_Click()
Dim zip1 As String, zip2 As String
Dim sResponse As String
Dim sLink As String
On Error GoTo Command6_Click_Error
zip1 = Me.Combo2.Value
[code]....
I need to calculate the average repaired time and the average response time. These are two separate fields ....
View 1 Replies View RelatedHi everybody. I got a access 2000 query that lists :
1)weekno
2)year
3)project (project number )
4)QweekylyReportHeader (project description )
5)customer (customer that requested this project)
6)department (department number and name that implements this project)
7)Projectleader ( project leader name and number that is responsible for this project)
8)Task (Task number that is done for this project )
9)task description (description of task )
10)employee ( employee number who is working in this project )
11)name (Employee name and initial and last that works for this project )
12)hours ( number of hours employee worked in this task ) ==> i want cumulative for this
13)salary (amount of salary given to this employee) ===>i want cumulative for this
I want to create another query that lists :
A)cumulative value of hours worked on particular project task up that point.
b)cumulative value for wages given for that project task up that point.
http://i5.photobucket.com/albums/y180/method007/weeklyprojectdata2.jpg ( query output sample)
The above query ONLY lists hours worked and wages gives for particular project task only during
each week.But i want hours worked and wages give for particle project task up to that point in week. For
example a project task might have implemented last week but not this so i want to take that in calculation as well.
I be happy if some expert show me how i can calculate the cumulative value for hours worked and wages given for particular
project task.
Notes:
- There is a possibility that during a particular week no task been implement for particular project.
- One employee can work in more then one project
- One employee can have more then one salary (amount) for the same
project because he might get raise in salary!
- Only tasks carried this week will be printed in the weekly report
http://i5.photobucket.com/albums/y180/method007/constraint.jpg ( pic of database)
http://i5.photobucket.com/albums/y180/method007/hourlywagesroportfinal.jpg ( query output population)
http://i5.photobucket.com/albums/y180/method007/queryindesign.jpg (query in design view)
query that display hourly wages of certain project during each week
SELECT
querythisweek.weekno,
querythisweek.Year,
querythisweek.Project,
QweeklyReportHeader.Customer,
QweeklyReportHeader.Department,
QweeklyReportHeader.description,
QweeklyReportHeader.ProjectLeader,
querythisweek.Task,
dbo_Task.description,
querythisweek.Employee,
[lastname] & ' ' & [initials] & ' ' & [insertion] AS Name,
querythisweek.hours,
querythisweek.Salary
FROM
dbo_Task
INNER JOIN ((QweeklyReportHeader INNER JOIN querythisweek ON QweeklyReportHeader.projectno = querythisweek.Project) INNER JOIN dbo_Employee ON querythisweek.Employee = dbo_Employee.employeeno) ON dbo_Task.taskcode = querythisweek.Task;
code for querythis week( calcualte the salary and hours worked)
SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hours_worked.hours, (select a.amount * dbo_Hours_worked.hours
from dbo_Hourly_wages a
where dbo_Hours_worked.Employee = a.Employee
and dbo_Hours_worked.Project = a.Project
and a.Year * 100 + a.weekno = (select max(b.Year *100 + b.weekno)
from dbo_Hourly_wages b
where b.Year < dbo_Hours_worked.Year
or (b.Year = dbo_Hours_worked.Year and b.weekno <= dbo_Hours_worked.weekno))) AS Salary
FROM dbo_Hours_worked;
i want to use the AfterUpdate to calculate the age using two text fields
Private Sub Birthdate_AfterUpdate()
Me.AGE = DateDiff("yyyy", [Birthdate], Now()) + Int(Format(Now(), "mmdd") < Format([Birthdate], "mmdd"))
End Sub
but it wont work
I have two fields CountOFAppeal Description and Amount Solicited.
I just want to get the percentage of response.
So this is what I used:
=[CountOfAppeal Description]/[Amount Solicited]*100
When I go into the properties to format it to % and add decimal places I get a crazy number
2520.46% When it should be 25.20%
When I change the format to general number the decimal point is in the right place 25.20.
The client db is not Normalized. I understand their requirements and the current table structure has to remain in tact. The DATA ENTRY staff need to enter cost for Jan1 to Jun30. One of the many calculations that needs to be updated daily is the SUMPRODUCT from STARTDATE to ENDDATE - which can be anywhere between Jan1 to Jun30 at any time. The example scenario below needs to calculate the sumproduct of COST between any two fields (days of week).
For example, let's say a Purchase Order has been created, and the initial entry looks like this:
StartDate = Jan30
End Date = Jun3
Jan1 thr Jan29 Cost = 0
Jan30 Jun3 Cost = 500 (average daily burn rate)
Jun4 thr Jun30 Cost = 0
... and the staff receives and update to the daily spread. The Cost Spread between Jan1 to Jun30 NOW looks like this:
StartDate = Jan15
End Date = Jun3
Jan1 thr Jan14 Cost = 0
Jan15 thr Mar29 Cost = 1000
Mar30 thr Jun2 Cost = 3000
Jun3 thr Jun3 Cost = 500
Jun4 thr Jun30 Cost = 0
Here is the code I am using to calc the SumProduct upon initial entry WHERE the 'average daily burn rate' for the entire spread is the same every day (FYI, we need to calculate the YearTo-Date (YTD) and Remaining Balance from YTD for both Commitment and Incurred cost. For this example I am only snipping the code here to reflect Incurred)
'Initial Calculations
If CurrentDate <= frmStartDate Then
SUMPRODUCTYTDIncurred = ZeroValue
Else
SUMPRODUCTYTDIncurred = Round((DailyBurnRate + DailyBurnRate * NoDays), 2)
End If
If CurrentDate <= frmStartDate Then
SUMPRODUCTRemainingIncurred = ZeroValue
Else
SUMPRODUCTRemainingIncurred = Round(DailyBurnRate + DailyBurnRate * (DateDiff("d", CurrentDate, frmEndDate)), 2)
End If
In my UPDATE SCRIPT (not shown - cause its TMI):
- I am using the For Each statement to search an ARRAY to filter for the db.TableDefs("MYTABLE").Fields I need to update.
- I am also using a Do While True statement to catch the cost for the found field.
How to catch all the cost between Jan1 to Jun30 before updating the SUMPRODUCT field and then .MoveNext until EOF (remembering that the For Each statement will stop when field Jun30 is found)?
Is it possible to create a query that will delete the fields within a table?
View 1 Replies View Related