How To Calculate Fields In 2 Tables In VBA

Apr 23, 2013

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] .....

View Replies


ADVERTISEMENT

Tables :: Calculate Some Additional Data And Generate Quotations - Too Many Fields

Jun 2, 2015

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

View 14 Replies View Related

Tables :: DLookup Between Two Tables - Calculate Product Price?

Jul 30, 2014

I'm trying to do a lookup between two tables, whereas table A includes the product ID and table B includes the price. Is it possible to create a field in table A with a DLookup function on the product ID to get the product price from table B?

Whenever I try to type in the DLookup function, it does not calculate.

View 2 Replies View Related

Calculate Fields.

Sep 5, 2006

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

View 5 Replies View Related

How To Calculate The Fields From Two Queries?

May 23, 2005

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:

I'm stucked so far, how can I solved it? :mad:

View 5 Replies View Related

Calculate Yes/No Fields In A Query

Jul 23, 2007

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 Related

Having A Field Calculate Other Fields

Aug 15, 2007

I 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.

View 11 Replies View Related

Calculate Fields Between Dates

Aug 10, 2015

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)

View 2 Replies View Related

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.

View 11 Replies View Related

Queries :: Calculate Fields Within Table

Dec 18, 2013

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 1 Replies View Related

Calculate Distance Using Fields In Form?

Sep 28, 2013

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]....

View 1 Replies View Related

How To Calculate Average In Two Separate Fields

Oct 28, 2012

I need to calculate the average repaired time and the average response time. These are two separate fields ....

View 1 Replies View Related

How To Calculate Cumulative Values Of This Query Fields?

Jul 12, 2005

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.

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;

View 5 Replies View Related

Modules & VBA :: Use AfterUpdate To Calculate Age Using Two Text Fields

Apr 16, 2014

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

View 4 Replies View Related

Calculate Due Date Based On Fields From Another Table

Sep 22, 2011

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.

View 14 Replies View Related

How To Calculate A Percentage Of Two Fields And Format Properly

Feb 11, 2013

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.

View 2 Replies View Related

Queries :: Calculate Multiple Fields For One Result - Callsign

Feb 21, 2015

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.

View 2 Replies View Related

Calculate Sumproduct Of COST Between Any Two Fields (days Of Week)

Feb 7, 2013

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)?

View 1 Replies View Related

How To Calculate Results From Two Tables

Feb 1, 2013

i have a small school with number of teachers, every teacher has a specific number of observations in the semester, i need a small project that when i query a bout a teacher, a form appearing with his name and the remaining hours of observation

what i did is i made two tables teacher (id, name, total number of observations in semester) observation( Oid, name of teacher, date and time of observation, remaining observations) -- i dont know if i have to make a counter or what !!--

Now what i need to do is a form when i enter the id of the teacher, it shows automatically the name of the teacher, and the number of remaining observations

How can i join the two tables and show the results ?

a sample Db TEACHER.zip

View 1 Replies View Related

Queries :: DateDiff Function - Calculate Difference In Dates Between Two Fields

Apr 22, 2013

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 Related

Queries :: Calculate Difference Between Two Date Fields To Get Total Time

Oct 26, 2014

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.

View 4 Replies View Related

Queries :: Filter Query To Calculate Sum Of Fields In Date Range

Nov 18, 2014

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.

View 4 Replies View Related

Queries :: Calculate New Fields Based On Current And Prior Year-end Numbers

May 6, 2013

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.

View 2 Replies View Related

IIF Statement - Calculate Value Based On Comparing Current Date To Dates In Fields

Jun 27, 2013

In my table I have the following 4 fields with the associated date field:

Bronze: 11/1/2013
Silver: 5/1/2014
Gold: 11/1/2014
Platinum: 5/1/2015

I am trying to calculate a value based on comparing the current date to the dates in these fields. I am using the below formula. However, using 6/27/2013 as the current date, my formula keeps resulting in "Bronze" when it should result in "Standard" Am I doing something wrong?

=IIf(Date()<[Bronze],"Standard",IIf((Date()>=[Bronze]) And (Date()<[Silver]),"Bronze",IIf(Date()>=[Silver] And Date()<[Gold],"Silver",IIf(Date()>=[Gold] And Date()<[Platinum],"Gold","Platinum"))))

View 5 Replies View Related

Tables :: Calculate Remaining Days In A Table

Feb 11, 2013

I'm trying to step from Excel to Access and I have some issues to translate functions that I used in Excel to Access.This is how it looks in Excel.And I have issues to translate the following function in Excel to Access.

=IF(AND(I6=0),"Done",K6-A$4)

A$4 is related to cell where it states the function =now() Basically what I want is to calculate in the tablecolumn is the following.In the column "Days remaining" I want the remaining days from "date to pay" and today. If the amount states "0" then the outcome must be "done".

View 3 Replies View Related

Tables :: Calculate And Store Time From Dates?

Nov 11, 2012

We are collecting data from client files that have multiple dates (start_date, event1_date, event2_date, end_date.) For client protection purposes, we are absolutely not allowed to leave the facility with the actual dates. However, we want to know how many days have passed between each event. I thought about having the data collectors enter each date, have a calculated field store the time between dates, and then have the actual dates cleared when the record is saved or when the person moves to the next record.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved