General :: How To Calculate Fields With Restrictions
Apr 10, 2015
Here 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.
Background: I'm doing a database for a camp They can only have 96 children for any given week of camp I have a combo box that the data entry clerk uses to assign weeks (1, 2, 3, etc)
Question: Is it possible to make it so that I can limit how many entries (campers) are selected to attend a week of camp? So that only 96 entries can have week 3 selected?
I have my data base splitted in to front/end. There's login form implemented retrieving username and password from a table. There're couple of restrictions like: admin - can delete records; non admin - can't delete records. But my main purpose is to have such restriction that each front user will see the data entered by him only and back end should reflect all data entered by different front end users.
I am building a database from a detailed Excel file which has upwards of 22 decimal places. Access restricts the decimal usage to 15 characters. Even when selecting the 15 characters, Access rounds the entry. The only way it will work so far is as text, but it is a calculated field. Any suggestions?
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,
I 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:
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
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
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?
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
Hi 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.
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 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 have 3x time fields in a table i.e time received, time started, time completed i need to accurately caculate the time between time received and time completed. whenever the time goes past 00:00 it seems to add 24 hrs to my calculated time is there a macro or code that i can use to rectify this problem?
I have an birthday report for the current month (august) and would like to make a button on the report that can calculate next month (september) and shows birthdays for that month. how to calculate next month?
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.
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:
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)?
Currently using a lot of Power Pivot Tables which in part calculate "averages" - but when the data comes form a Query that contains a calculated field using the iff() funtion such as: Outcome: iff(Status = "P", 1,0) we end up with a lot of zeros that create a problem in calcuating a true average. In the following example 2,3,0,1,0,0 the average is "1" but if the zeros were blank the average would be "2" because the blank cells would not be counted.
Is ther a way to have something like: Outcome: iff(status = "P",1,"blank") but what I want is ether 1 or a blank cell, NOT TEXT. If we use "" for the false outcome we still have cells that look blank but are in fact text cells and are counted in calculating average.
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.
I 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 database that will take lots of data entered by an employee and calculate some additional data and generate quotations. Within each quote there is a possibility for 15 different metals (5 Precious Metals, and 10 Base Metals). There is also 5 fields that need to be filled out about each Metal (What the metal is, the market being used, weight, whether it is included in a different price, and the price). I currently have 75 fields to address each Metal and their 5 fields respectively. Is there a better way to Normalize this data, and accomplish what I need accomplished? I want at the form level the employee to tell the database whether they want to add a Precious Metal, or Base Metal, or Move on to other data entry.
Here is a Screenshot of the design view of one of my tables with too many fields : table screenshot1.PNG
