Making Calculations Between Certain Dates?

Jun 24, 2013

I am building a credit tracking database. Each person attends an event and can receive credit in up to 5 different categories. Right now I have a query that adds the number of credits in each category, then adds up the sums of each category (for each person). I am stuck now trying to figure out the best way to only add the credits up during a certain time period. Each person has a specific reporting period, which is a calculated date.

For example: Joe's reporting period is from 5/1/2012-4/30/2014. I want to calculate the number of credits he earned only during that time period.

I tried adding the event date to the query but then it undoes of the sums.

View Replies


ADVERTISEMENT

Making Query Give Me Anything For Dates BETWEEN 10/01 And 10/15

Nov 10, 2006

Hi!

I'm trying to write a query that will display my data for anything that has a date between 10/01 and 10/15. I went into the "Build" area and looked at the "functions" available but none of them sound right - and the one book I have on Access is not so great.

Can someone help me with the correct formula?

Thanks!

View 2 Replies View Related

Calculations On Main Form Do Not Reflect Calculations On Subform

Sep 28, 2015

I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:

=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]

#Error results when the doc is put into a form mode.

Now when I input each value in the equation above separately.I still get no entry.

For " = sbfCustomerRoomUse" , I get #Error;
and for "= txtTotalPhoneUse", I get #Name.

CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.

View 14 Replies View Related

Calculations On Calculations--too Much For Access?

Jun 8, 2005

I have an odd problem that has stumped me for several days. I'm working on a form that contains a chart. The chart is based on a query, which is based on another query, which is based on a table. In the top query, I need to put some calculated fields that operate on other fields. But when I try to multiply two fields together, I get Null. I'm pretty confident that the fields I'm operating on are numerical.

The fields of the query should be:

MaxOfEverInSchool
MaxOfSurvivalToGrade
MaxOfEverInSchool

With these being the calculated fields:

MaxOfSurvivalToGradeCum: MaxOfEverInSchool*MaxOfSurvivalToGrade/100
MaxOfCanReadCum: MaxOfEverInSchool*MaxOfSurvivalToGrade*MaxOfCanRea dCum/10000

Any insight would be greatly appreciated--I just can't understand why Access would suddenly refuse to do math.

View 1 Replies View Related

Making Specific Fields In A Current Record Copy Themselves When Making New Record

Mar 28, 2005

;) Hey everybody,

I am working on a database used in recording device characteristics/test information. The main table of information has dozens of columns for test/part detailed information. When inputing the data for each specific test, many of the info. details are repeated when testing say 20 devices of the same part all at once. Rather than retype every piece of detailed information in every field, everytime, is there an easier way? Does anyone know of a way to make specific fields copy/paste the previous record's information in the fields automatically when a new record is created? Please, if anyone could help or has ANY ideas, let me know...

Thanks

gunwax

View 9 Replies View Related

Forms :: Dates As Column Headers To Update Table With Dates As Rows

May 12, 2014

Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???

The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)

tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc

I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?

It's Access 2010.

View 1 Replies View Related

Queries :: Calculate Expiry Dates Of Training Courses - Due Dates Not Shown

Aug 28, 2013

I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?

View 1 Replies View Related

Queries :: Access 2007 - Select All Dates Between Two Dates?

Apr 9, 2015

I have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)

I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date

So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.

Is there a way to do this?

So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015

02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-Apr-2015

(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)

Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?

View 4 Replies View Related

Subtracting Dates From Adjacent Dates In Same Column

Sep 7, 2006

Hiya-

I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.

One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.

This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.

What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?

Many thanks, Jules.

View 3 Replies View Related

Queries :: Count Dates Between Dates In Two Tables

Jul 8, 2014

I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.

To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014

table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014

So the answer of the query would be 2,0,4.

Explanation:
Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)
Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2
Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2

View 2 Replies View Related

Summing Data Between Two Dates (When Dates Are Different Per Record)

Nov 15, 2011

I have a master table which shows all transactions per record (person) over a financial year.

Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.

I need to be able to create a field which sums all expenditure per record between the start and end dates

Name Start Date End Date Invoice Date Amount

Matt 15/5/11 15/9/11 1/11/11 £100
Matt 15/5/11 15/9/11 7/7/11 £200
Matt 15/5/11 15/9/11 12/12/11 £200

In this case I would only want to sum 7/7/11 as this is between the start and end dates

I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly

(The start date and end date will always be the same per person)

Is this possible in access?

View 10 Replies View Related

Calculations

May 3, 2005

Hi ive made a query but instead of calculating the total at the end of each line I want it to add up the total quantity at the end.
For example:

ID Description Quantity
ID Description Quantity
ID Description Quantity
ID Description Quantity
ID Description Quantity

How do i get the query to add up all of those quantities?

View 5 Replies View Related

Calculations Need Help!!!

Oct 30, 2005

I'm hoping this is a no brainer...I am a complete newbie and self taught so apologies for not knowing the correct techie terms!!

I have a db with products...

Each product has a price (to customer) and cost (to me the seller)

What I want to work out is the margin i.e. price minus cost divided by proce multiplied by 100 to give a margin in percentage terms.

The data is held in a table with a record per product with the price and cost..

I want to build in another fieled per record that calculates the margin...

Thanks very much

Jessica

(I would also like to be able to work out decreasing margins if I was to extend say a 10% or 20% discount to the customer but I guess I shoudl walk before I run!!)

View 3 Replies View Related

Calculations

Nov 15, 2005

Hi, I have a database and on one table I have dates stored for certain jobs. I was wondering if anyone knew how to automatically calculate an end date if the start date and duration is entered!

Do I use a certain query?

Hope you can help, Tobi :)

View 2 Replies View Related

Calculations

Jul 26, 2006

Hello all,

I'm after some advice. Access is not one of my strong points (even though I use it alot in work!)

Anyway, is it possible to create calculations in access similar to those in excel????

View 2 Replies View Related

Help With Calculations

Mar 18, 2008

Ok I'm really new at using access so go easy on me. I am trying to write a database to keep 30 measurements of a part grouped by lot number and then calculate the standard deviation, mean and a few other calculations, however I am unsure about how to do the calculations in access.

I would like the database operator to be able to enter the data then print out a report per lot, with the entries and the calculated answers.

I'm just unsure where and how to do the calculations... any help or suggestions?

Thanks in advance!

View 14 Replies View Related

Calculations

Mar 30, 2007

Hi,

I've read plenty of advice warning against performing calculations in tables. What is the best way for me to achieve this.

I need to calculate the product of a number of fields.

Field1 * Field2 * Field3 * Field 4 = Value

Field1 and Field 2 change periodically

Field3 and Field4 change less frequently

I want to be able to perform filter queries using the resulting value

i.e return all items with a value greater than 100

Thanks in advance.

Al

View 2 Replies View Related

Age Calculations

Apr 13, 2008

I'm having a problem with this age formula in the query :

AGE: DateDiff("yyyy",[birth_date],Date()) & " " & " years" & " " & "and" & " " & DateDiff("m",[birth_date],Date()) Mod 12 & " " & "months"

Need your help guys in 2 probs :
1. age calculated is not right.
2. need to compute what w'd be age on the sep 1st 2008 - how do I do it?

Appreciate your help.
Thanks.

View 2 Replies View Related

Sql And Calculations!

Apr 26, 2005

Hi,

I'm trying to a create a report to list results for a running club based on series-to-date. To do this, I allocate points to the first 100 runners to cross the line in each race. The winner gets 100, second place 99, and so on with the 100th person getting 1.

In the database i have 1 table holding member details, then one table holding races. I also have a table linking the 2 called race_entry so i know what members have entered for which races. I have one other table called race_result that links the runner to a race and holds the position they finished in the race.

How could i use the data in the race results table to add up all the points they earned by looking at their finishing position in the races they run and then listing the points in order in a report.

I assumed it wouldnt be necessary to have a point field in the table as its deriveable.

Any help urgently needed!!!

View 1 Replies View Related

Need Help With Calculations

Jul 13, 2006

This is more than likely simple for a lot of people out there but it has me stumped. I have a form that has a two combo boxes with dates in both ( so the user to pick from and to dates) now I am always going to be adding more dates as time progresses. I need the next form to add the info with the dates no mater what the user picks.
For example I am going to (once a week) be adding new info to the table.The end user is going to pick 06/24 (to indicate the end of the week) in both forms or 06/24 in the from box and 07/01 in the to box. In all situations I need the next form to add the data together.
Sorry for the long post but didn't know how to state it any simpler.

View 10 Replies View Related

Calculations

Dec 6, 2007

Hi everyone!

I am a beginner with Access and was wondering if someone would be so kind to give me some pointers

I have created a form where the user can select which dates they require a hotel for the evening. I was looking for some advice on how I can go about putting an area on the same form which calculates a sub price for the days they have selected. If I am not being clear then ask for more details.

Thanks

View 2 Replies View Related

Calculations In Access

Aug 24, 2005

I need to be able to use some functions available in excel in an access 2000 database. These include SUM and IF functions. If any one can help me figure out how to do this any help help wouold be greatly appreciated

View 6 Replies View Related

Date Calculations

Mar 7, 2006

Hi
I would like to manipulate some date information. I have a start date field and an end date field and I would like to draw up a schedule for payment where the Acess takes the finished date and the start date to get total days appart then devides this by a value set by the operator. say If the operator wanted 6 paymes then my total days would be devided by 6 giving me the number of days to add on to the start date. Then with these dates a schedule is made up and the code I have tried looks something like this.

Dim valuation1 As Date
Dim valuation2 As Date
Dim valuation3 As Date
Dim valuation4 As Date
Dim valuation5 As Date
Dim valuation6 As Date
Dim valuation7 As Date
Dim valuation8 As Date
Dim valuation9 As Date
Dim valuation10 As Date
Dim valuation11 As Date
Dim valuation12 As Date
Dim startdate As Double
Dim findate As Double

Dim valcounter As Double


'retrieve start date and end date
startdate = CDbl(Me.Start_Date)
findate = CDbl(Me.Completion_Date)

'calculate evaluation periods
Dim valCount As Double
Dim valDays As Double

valCount = CDbl(Me.Valuation)


valDays = (startdate + ((findate - startdate) / valCount))


'Work on entering valuation dates

Do While valcounter < valCount

Select Case valcounter
Case "1"
Me.valuation1 = startdate
Case "2"
Me.valuation2 = startdate
Case "3"
Me.valuation3 = startdate
Case "4"
Me.valuation4 = startdate
Case "5"
Me.valuation5 = startdate
Case "6"
Me.valuation6 = startdate
Case "7"
Me.valuation7 = startdate
Case "8"
Me.valuation8 = startdate
Case "9"
Me.valuation9 = startdate
Case "10"
Me.valuation10 = startdate
Case "11"
Me.valuation11 = startdate
Case "12"
Me.valuation12 = startdate

End Select
valcounter = valcounter + 1
startdate = startdate + valDays

Loop
' this value is to test the interval period
Me.tdays = valDays

I am new to vb and so ths code is not going to be the best or anywhere near but any help would be great.

Thanks

View 1 Replies View Related

Calculations In Forms...

Apr 7, 2006

Hi Everyone:

I have a question that I can't seem to figure out. I am trying to create a form in which there are three major fields. 1) Value 1 2) Value 3) Sum of Value 1 & 2. I want it so that two fields are added up and then the sum, which will populate in the third field, will be stored in a table.

Does anyone have any suggestions on what I can do? I am fairly new to access and haven't had much luck finding anything on the net.

I appreciate any and all advice.

Thanks!
Darin

View 9 Replies View Related

Time Calculations

Jun 26, 2006

I have a form that includes two basic fields

1. USDateandTimeStarted
2. USDateandTimeFinished.

The fields include both date and time (date and time are in the same field) i.e. “6/26/2006 4:23pm”. The next field I need to calculate the duration, basically one field minus another…anything I’ve tried will only calculate the time difference will not factor in the days. For example, if it has been 52 hours (2 days, 4 hours) it will only say 4 hours. A few of what I’ve already tried are below. And I’ve also tried an elapsed time expression.

Can anyone provide any guidance.. ? Thanks!

=[USdateandtimestarted]-[USdateandtimefinished]

=DateDiff("n",USDateandtimestarted,USdateandtimefinished)

Glad tidings -

Claw

View 6 Replies View Related

Calculations In Access???

Feb 18, 2007

Hi

I am currently setting up a data base for my work and was wondering if there is any way to do the following calculation:

I have a database of automatic doors that are contantly being repaired and i want to keep a record of the accumulated costs involved with this.

I have set up a form to update each door and its accociated repair cost, the problem is when i enter the new repair cost access replaces the current cost with the new and i want it to be added to the current cost so the accumulated cost can be bulit up over time.

I hope this makes sense

Any help would be greatly appreciated!!:)

View 10 Replies View Related







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