Queries :: If Statement - Find Cumulative Forecast Up Until A Best Before Date

May 22, 2013

I want my query to find the cumulative forecast up until a best before date.

This is fine if a forecast goes past the best before date as the query picks it out but if the forecast doesn't continue then it won't match them up.

I need the IF statement to say that IF no forecast is present then look at the last cumulative forecast.

How would I go about this?

View Replies


ADVERTISEMENT

Query To Forecast The Vehicle Service Due Date

Feb 12, 2007

Hello,
I'm totally new to this forum. I have a database with the vehicle information in one table. The Vehicles' Odometer reading stored weekly in another table. I have the vehicles' service history with the Odometer reading of the last service in another table. The first two tables are linked by parent, child relationship. I now want to forecast the approximate vehicle service due date based on this information. All I want to do is extract the last 10 Odometer Readings from the Weekly Odometer Readings Table. Find the Average Kilometers with those 10 values. Come up with a figure. Determine the last value entered in the Weekly Odometer reading along with the date. Pull the last service history for the vehicle, with the odometer reading and date. Compare these 2 readings to see if the vehicle is due for service, and if so based on the average Kilometers run every week, determine the approximate date the vehicle is service for due. I badly need help on this one. So if someone could help me on this please let me know.
Thanks

View 3 Replies View Related

Queries :: Sum Cumulative By Id?

Aug 22, 2013

how can i do this in a query

Id---Q---Cu
1---10---10
1---10---20
1---10---30
2---10---10
2---10---20
2---10---30

View 2 Replies View Related

Queries :: How To Find 1st Date Customer Used

Dec 2, 2014

I have a database with 8 years of info on customers orders. I need to find out when each customer first used our services.

View 8 Replies View Related

Queries :: Find Latest Date

Jan 18, 2014

I have a list of project, each of which have dates which work were carried out on them. Each project can have more than one date. I want to be able to find the last date that any work was carried out, and then calculate how many days have passed since that happened.

View 6 Replies View Related

Queries :: IIF Statement With Date Functions

Feb 24, 2014

My goal is if the current date = 1st - 4th of the month to return the failure_date if it is >= 1st of the prior month and <= 4th of the current month (ie, 01/01/14 - 02/04/14)

this works:
>=DateSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),4)

But if the current date > 5th of the month to return the failure_date if it is >= 1st of the current month and <= 4th of the next month.(ie 02/01/14 - 03/04/14)

this works
>=DateSerial(Year(Date()),Month(Date()),1) And <=DateSerial(Year(Date()),Month(Date())+1,4)

But when I put it in the iif statement if will not work:

IIf(Date()>DateSerial(Year(Date()),Month(Date()),4 ),>=DateSerial(Year(Date()),Month(Date()),1) And <=DateSerial(Year(Date()),Month(Date())+1,4),>=Dat eSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),4))

SQL =
SELECT table_testing_dates.Failure_Date, table_testing_dates.Failure_Date, table_testing_dates.FailureGrouping
FROM table_testing_dates
WHERE (((table_testing_dates.Failure_Date)=IIf(Date()>Da teSerial(Year(Date()),Month(Date()),4),(table_test ing_dates.Failure_Date)>=DateSerial(Year(Date()),M onth(Date()),1) And (table_testing_dates.Failure_Date)<=DateSerial(Yea r(Date()),Month(Date())+1,4),(table_testing_dates. Failure_Date)>=DateSerial(Year(Date()),Month(Date( ))-1,1) And (table_testing_dates.Failure_Date)<=DateSerial(Yea r(Date()),Month(Date()),4))));

View 2 Replies View Related

Queries :: Cumulative Reduction Of Stock

Aug 20, 2013

queries cumulative reduction, basically i have 2 tables, with the follow data

stock table
idProduct
QuantityStock

[Code]....

View 4 Replies View Related

Queries :: Dataset - Cumulative Sum For A Month

Nov 27, 2013

I have a dataset like this

ABCDEFG
1.AccDateStartIntWLMV
2.SSAAB15028815/02/88Month1.25101000
3.SSAAB18028818/02/88Month020980
4.SSAAB25028825/02/88Month1.2510970
5.SSAAB29028829/02/88Month010960
6.SSXXB15028815/02/88Start 1.25101000
7.SSXXB18028818/02/88Month020980
8.SSXXB25028825/02/88Month2.2510970
9.SSXXB29028829/02/88Month010960

I used to to apply this logic in XL to do my job A-F = XL Columns & 1-9 XL Rows

ifA2=A1, E2+E1, E2

This is to have a cumulative figure for a month. Just bcoz the data is growing in large numbers, we were advised to use access for which I don't know how to perform this calculation.

View 1 Replies View Related

Queries :: Incorporate A Date In Output To Statement

Jun 11, 2013

I have a database that enables the banking details for a small club to be generated as an Excel spreadsheet that is then emailed as an attachment to the treasurer. The event procedure below works fine but I have been unable to incorporate the banking date from a control called BankDate to end up with the Excel file being in the form DGC Banking dd-mm-yy - is this possible?

DoCmd.OutputTo acOutputQuery, "Export Banking", "Excel97-Excel2003Workbook(*.xls)", "DGC Banking.xls", True, "", 0, acExportQualityPrint

View 5 Replies View Related

Queries :: Nested IIF Statement In Date Calculation

Jul 30, 2015

I am trying to calculate the time between two dates where one date field might be blank or not. Where the field is blank I want to use the current date to perform the calculation. So far I have the following but I keep receiving an error message saying that the expression has a function with too many arguments. Is there a simpler solution to this?

IIF([LastOfEnd_Date] IS NULL,(DateDiff(w,[LastOfDischarge_Date],NOW()))/4, IIF(Not isnull([LastOfEnd_Date], Abs(DateDiff("w",[LastOfDischarge_Date].[LastOfEnd_Date]))/4

View 5 Replies View Related

Queries :: Cumulative Total On Multiple Records?

Mar 28, 2014

I am looking for a way to get a progressive cumulative total from daily entries on Odometer records from multiple vehicles. (My current SQL query is not working) I will eventually run a report from these between two dates. I found the thread here, but am unsure of how to implement this - if this is indeed what I need in lieu of my current code..?

[URL]

My current problem is that, not all my previous entries are correct; Im not sure what is happening to the numbers about halfway through...

For clarification, here is my current code and an example of what is needed. The issue is highlighted in yellow. Excel shows the correct calculation

Code:
SELECT qry_ODO_Table.ID AS OdomAlias, qry_ODO_Table.ODate, qry_ODO_Table.VehicleNum, Nz(DFirst("Odometer","qry_ODO_Table"),0) AS StartOD, Nz(DLast("Odometer","qry_ODO_Table","[ID] < " & [OdomAlias]),0) AS Previous, qry_ODO_Table.Odometer, [Odometer]-[Previous] AS Difference
FROM qry_ODO_Table
ORDER BY qry_ODO_Table.ID;

View 12 Replies View Related

Queries :: Calculate Cumulative Values Stored In Field

Oct 7, 2013

I have several projects with different tasks for each. I have 3 fields [fkProjectsID], [TaskOrder] and [Duration] in a table for project tracking with that structure:

[fkProjectsID] [TaskOrder] [Duration]
1 /1 /5
1 /2 /8
1 /3 /15
1 /4 /6
2 /1 /8
2 /2 /30
2 /3 /25

I want to calculate cumulative values stored in [Duration] field (represent a number of days). I'm using the field [TaskOrder] to order different tasks within each project. With some testing, I was able to calculate cumulative [Duration] with 1 project using the DSum fucntion as following:

CumulDuration: DSum("[Duration]", "[tblProjectTracking]", "[TaskOrder]<=" & [TaskOrder])

I was having the sequence: 5, 13, 28, 34 for respectively Task 1,2,3,4. However, when I add a second project (and then a third...), I need to be able to filter based on [fkProjectsID] as well (i.e. a specific DSum by ProjectsID). I add this criteria but I get the sum of [Duration] on each row instead of the cumulative:

CumulDuration: DSum("[Duration]","[tblProjectTracking]","[TaskOrder]<=" & [TaskOrder] And "[fkProjectsID]=" & [fkProjectsID])

View 3 Replies View Related

Queries :: Find Result In A Table Containing Highest Date Value - Too Many Results

Jul 6, 2013

Setup a query to find the result in a table containing the highest date value.

The query is linked to two tables : Payment information containing the date, and tenant information containing the tenant.

In the query i have selected the tenant name from the payment table (which is linked to the tenant name in the payment table) and the payment terms - ie weekly / monthly etc. I've then selected the payment date from the payments table.

The query should return for each tenant the latest date they paid.

On the pay date i selected the Max option.

But it shows me more than one record.

SQL query is shown here

SELECT Max(tblPayments.DateDue) AS MaxOfDateDue, tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))) AS calcNextPayDueDate, tblPayments.cboTenant
FROM tblPayments INNER JOIN tblLease ON tblPayments.cboTenant = tblLease.cboTenant
GROUP BY tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))), tblPayments.cboTenant;

View 2 Replies View Related

Queries :: Calculated Field To Find Latest Date For Each Record

Jul 11, 2015

If I have four date Fields in a query, Astart, Bstart, Cstart, and Dstart and want to have a calculated field to find the latest date for each record how would I do that? I have tried things like:

LatestDate: MAX(Astart, Bstart, Cstart, Dstart).

View 2 Replies View Related

Queries :: WHERE Statement - Enter Dates Into Date Reported Fields For Results To Show

Sep 11, 2013

I use this

'WHERE ((OperationalRiskEventTable.DateReported)>=Forms!U pdateForm!UDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!Up dateForm!UDateEnd)'

in a query by form.

The problem is that you have to enter a date in the between values for results to show. If I don't enter information into a different field such as Full Name but I enter in 40 into Age then everyone that is 40 years old will show. On the other hand if I enter 40 into the Age field but I leave the Date Reported fields empty then no results will show.

How can I change it so that I don't have to enter dates into the date reported fields for results to show?

View 4 Replies View Related

Queries :: Find Latest Date In A Table Where Dates Are In 2 Separate Columns And Multiple Rows

May 19, 2015

I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)

I have a table "Assessment tracker" with the following structure

Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date

My Data:

Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15

I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.

Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit

[Code]....

Output:

CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015

It does this by using a function shamelessly copied from the web somewhere...

Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.

[Code]....

This is working well (I think)

I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.

Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;

But this is returning

Candidate Unit MaxOfAchdate
TH1 1025/05/2015

I expect it to return

Candidate UnitMaxOfAchdate
TH1 10 07/06/2015

It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)

View 14 Replies View Related

Queries :: Find Date In Table 1 Closest To Another Date In Table 2?

Mar 17, 2015

I have a table, tblVisits, holding patient's pre and post surgery visits:

Code:

PatientID VisitDate
1 1/5/12
1 3/10/12
1 9/1/13
2 ...

And another table holding patient's surgeries (each patient will have only one surgery)

Code:
PatientID SurgeryDate
1 4/1/12
2 ...

I need to compare these two tables and create a variable that indicates which pre-surgery visit date (i.e., VisitDate < SurgeryDate) is closest to the surgery date. In the above example, it would return:

Code:
PatientID VisitDate ClosestToSurgery
1 1/5/12
1 3/10/12 Yes
1 9/1/13
2 ...

I've tried various MIN and MAX approaches and can't seem to get it right.

View 2 Replies View Related

Forecast Units

Feb 7, 2008

I am drawing a blank on how to proceed with this requirement and was hoping someone out there could help me.

I am creating a graph that shows all of the orders we have taken, shipped, and what we have bid on. There is a table that holds the customer, no of units ordered, no of boxes per unit, number of units to ship each week and start date.

I need a formula that will calculate all of the week ending dates until the no of units ordered is 0.

For example, Capital ordered 100 units, 12 boxes per unit, 2 units to ship per week and shipments are scheduled to start on 2/19.


I would need it to display:

W/E Units Boxes
2/23 2 24
3/1 2 24
3/8 2 24 etc....

Until the total units ordered equals 0

This information is diplayed on a graph. The graph shows all of our open bids and based on shipments per week, displays a forecast of what's coming up.

Any help would be greatly appreciated.

View 9 Replies View Related

Modules & VBA :: Can't Find Field In Update Statement

Aug 19, 2014

My update statement is shown below. What this is to do is change the PGTIN record in PP TBL to the GTIN that is in the UPC TBL where the UPC Code on the form is the same as the UPC Code in the UPC TBL. I am getting the error message "can't find field..." I have double and triple checked the field names but cannot find my error. Both PGTIN and UPC Code are text fields.

strSQL = "UPDATE [PP TBL] SET [PGTIN] = '" & [UPC TBL].[GTIN] & "' WHERE ([UPC TBL].[UPC Code] = '" & Me.UPC & "');"

View 6 Replies View Related

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?

View 2 Replies View Related

Using Excel Function Forecast In Access?

Jul 12, 2013

I am trying to use the Excel worksheet function "Forecast" to calculate from existing data in my database. I have successfully gotten the data points into arrays, but when I try to call the forecast function I keep getting the following error: "Unable to get the Forecast property of the WorksheetFunction class". I have imported the object library. This is on a company computer and I do not have write privileges to the C: drive. The database resides on a network drive.

Am I facing some sort of security issue?

Public Function xlForeCast() As Double
Dim MyHeight As Variant 'Will be the point for which you are forecasting, in this case height
Dim MyRange() As Variant 'Will be the independent element of the forecast function
Dim MyRange1() As Variant 'Will be the dependent element of the forecast function
Dim MyArray() As Variant 'Temp array to hold the query result set values before being split into the two preceding arrays
Dim db As DAO.Database

[code]....

View 1 Replies View Related

Modules & VBA :: How To Find Record In Table And Use One Of Its Field For If Statement

Jun 6, 2014

I have a table called login and inside that table is three columns: username, password and admin.

I have the username saved in a global variable called GsUser. How can i find the record in that table with the same Username as the string stored in GsUser and use that record for an if statement which sees if the value of the admin column is "Yes". Im trying to do it using VBA. Im not using a form where everything is bounded.

View 2 Replies View Related

Find Max Random Date In Tbl1 Before Active Record Date In Tbl2

Jul 9, 2013

I'm reworking a db to make it web compatible. Right now I'm working on my Price and Sales tables.There are about 900 Sales records, 450 Price records (for about 45 Items).

I have re-done my Price table with an Autonumber Key field. (It had a multi-key which I understand web db does not support.) Each autonumber key represents a Date with new Price for a Company/Item. The Price change Dates are random.

I have put a Foreign Field in my Sales table for the Price key field.

My dilemma is matching the Sales with the Prices.

When the Price Date and Sales Date do not match (at least half of them don't match), I need to look back in the Price table to the max Date BEFORE the Sales Date in the Sales table for that Company/Item in order to select the correct Price key.

View 2 Replies View Related

Forms :: Create A Production Forecast Form Based On Previous Sales History?

Jul 19, 2014

I need to create a production forecast form based on previous sales history.The history is based a sales and grouped by month & year

So on the form, which needs to be a continuous form, I want products to show as rows and months as columns The sales history per month needs to be displayed as well as a field allowing to user to enter the production forecast.

I can write the sales history to a temp table.However I never know how many months history the user is going to want displayed at run time. Could be 3, 7, 12 or 15!

Attached spreadsheet shows what I am trying to achieve. Is this possible and if yes, how would I do it?

View 6 Replies View Related

Modules & VBA :: Find Date Which Is 6 Months Back From Today Date

Jan 27, 2015

I want to find out the last 6 months date from todays date. So as todays date is 27th january 2015 so the code should give me the date which is 6 months back from todays date so it will be something like 27th July 2014.

View 1 Replies View Related

Queries :: IIF Statement With And In True Statement

Oct 31, 2014

I have a query with the following criteria in one of the fields:

>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())

fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.

>=DateAdd("m",-12,fom()) And <=fom()

Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.

Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())

I have also added the column name to each expression and it still doesnt produce any results.

View 4 Replies View Related







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