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 Replies
ADVERTISEMENT
Aug 5, 2013
I run a service related company and I am trying to develop a service database that will automatically calculate the next service due based on the previous service date, the catch is that the service need to happen on the same week day. i.e. if the last service was on Friday 02-08-2013 and it is a monthly service, the next service is due on Friday 06-09-2013. Is there any way that this can be done? PS there are various service periods such as bi-weekly, weekly, 2 weekly, monthly, 2 monthly etc.
View 2 Replies
View Related
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 4 Replies
View Related
Dec 26, 2005
Hi
I use the Date and Time Picker. i know that i can get many other calendars, however, my theory is that i would rather use something that is directly supported by microsoft. but as i found out now that may not be the case.
on of my users updated his Office 2003 after he was having problems using my database (created on 2002 sp3). i know that the same database works on 2003 sp1. however, now microsoft is serving 2003 sp2. so after the update he he can't use the database.
so my questions:
1. how do i generally prevent users from getting update errors. i would like to atleast alert them with a meaningful message that their version of Office needs to be updated.
2. is this true that the Date and Time Picker doesn't work anymore? and if so, why isn't backwards compatible? and how do i prevent this from happening with other controls or scripts?
thanks,
sam
View 2 Replies
View Related
Dec 4, 2012
I have a database that monitors vehicle maintenance, servicing schedules and stuff like that.
My problem is we purchase items for the vehicles and I am trying to link the items purchased against the vehicles, the purchased items need to show description, cost, invoice number our purchase number, supplier.
The problem comes when we have just stock items recieved that do not have any relation to the vehicles in the main table.
View 1 Replies
View Related
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
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
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
Aug 18, 2005
Hi,
I'm trying to make a database where a Date and a Service Number is entered into a form.
I have 2 buttons, one to automatically enter the Current Date and the second to automatically enter the Service Number.
The service number is incrimented by 1 each time and reset for every day.
eg.
Date Service Number
January 1, 2005 1
January 1, 2005 2
January 1, 2005 3
January 2, 2005 1
January 2, 2005 2
January 5, 2005 1
January 5, 2005 2
I already have the button that enters the Current Date. However, I am having trouble with the second button.
How can I code the button so that it searches the table for the Current Date and finds the record with the greatest service number for that date? After this is done, I want to create code that will automatically enter the next service number into the Service Number Textbox on the form.
eg. Using the Example Table Above
The date January 5, 2005 is entered into the Date Text Box and the Search Next Service Number button is pressed. The number '3' should show up in the Service Number Textbox
Sorry if its hard to understand, but thanks anyways.
I just realized that I placed this in the wrong forum, Sorry for the inconvenience.
View 2 Replies
View Related
Feb 28, 2008
A child in our company can have up to 4 different service providers offering different services at a time. I have to do a report for each individual service provider and there delivered services. The problem is, all the providers and delivered services are listed on one record (the child's record). How do I make a query that will only pull up their delivered services in a record and not the services of the others attached to it? Should I use IIF? If so, what would be a good statement to use? If anyone can help me out, I'd be thankful. Let me know if additional information is needed.
View 1 Replies
View Related
Oct 30, 2005
Hi All,
When I open a query in design view it can take anything upto 20 seconds to open. Tables are also slow to show, with a marked "slowness" over all. I tried the same database on an old laptop & the tasks were almost instant.
My main machine links to a file server, but the database files are not server based.
After much troubleshooting I have dicovered that it is the XP Workstation Service that is dragging it down. If I stop the service, then Access is rapid!
I have read all I can find on the subject, but nothing that helps. With all the parameters in the Workstation Service in the registry, is there a setting that I can change to speed things up?
System details:
XP Home (SP2)
Access 2002 (SP3)
Many thanks!
Dave
View 4 Replies
View Related
Dec 15, 2005
I'm trying to build a table structure for this database. Heres some background information about the business:
The business is a service business. We visit the customer's location and run tests on whatever water systems they have. Each customer is unique in that they could have any combination of systems at their site. They can also have more than one of the same type of system. The test results are the data that I need to record and store for future access. Each customer is visited on average once or twice a once a month. So there should not be more than 1-2 entries of data for each system for each customer per month.
For example
customer RUTGERS might have two systems labeled HWS and CHWS.
customer BMSQUIB might have three systems labeled HWS1, HWS2, and CHWS.
What I need to do with this information is go into the records of service visits, and retrieve for example, the last 4 visits of a specific system and prepare that information to be printed in a report along with their contact information.
I have come up with three tables to do this:
Customers Table contains:
Customer ID (pk), contact information data.
Systems Table contains:
System ID (pk), Customer ID, System Name
Service Records Table contains:
Record ID(pk), Date, System ID, Data
My thoughts were to have a table to contain customer information(each customer with a unique ID), a table to contain system information for each customer (each system has a unique ID), and a table to store the results of every service visit for each system(each individual visit has a unique ID)
Please critique this table design. If you think its sufficient, perhaps you could lead me in a direction pertaining to how to retrieve data on the most recent 4 visits (last 4 entries) for a specific SINGLE system from the Service Records table. I would assume that you would need to use a query and then get the data from that and put it into a form.
View 10 Replies
View Related
May 7, 2013
Simply put, I have a front end that does a bunch of file moving and if it encounters an error and stops processing (which is intentional) I am the only that can login to the machine and fix it.
Is there any way to run it that would allow others to login on their own user profile, solution the problem, and start the processing again or am I stuck to a "per profile" instance?
I can't just let others login as me
View 2 Replies
View Related
Jan 16, 2015
I've been trying to solve this problem for the past month and at the verge of destroying my PC! I've trawled through numerous web sites but just cant seem to figure this one out.I'm trying to get access to tell me what the next service moth is based on the start date, end date, and the frequency of service.
For example:
Start date: 01/01/2014
End date: 31/12/2015
service frequency: Every 4 months
Using the above information the service months are:
1 - April 2014
2 - August 2014
3 - December 2014
4 - April 2015
5 - August 2015
6 - December 2015
View 6 Replies
View Related
Aug 28, 2005
WindowsNT 4.0 Service Pack 6 error in access runtime install
I have an access runtime install package that has worked numerous times
installing on XP Home. It was built using Office XP developer's packaging
wizard.
I tried installing it on a brand new PC running XP Home but it generates the
error:
Visual Basic 6.0 Setup Toolkit
The office system pack cannot be installed on this system because it
requires Windows NT 4.0 Service Pack 6 or later.
I cannot find any info on this - any ideas?
View 2 Replies
View Related
Mar 1, 2014
I have a client table.
I have a client product table for ski's
Each Client has 1 or more products (skis)
I have a Service Order table and form that I use a drop down control in the Service order to select the customer.In the Service Order Form a I have a continuous Subform for detailed service.
This is where I'm having the problem.In the detail subform I want to select from a drop down box the customers particular ski that I want to service. how to have only that one customers ski's to show up.
View 14 Replies
View Related
May 20, 2014
In a query I'm trying to return a list of rows sorted by Service Type Ascending and then the last item in the list should be a row called "Add Edit Value".If I 'ORDER BY 2' then the "Add/Edit" row appears at the top which is not what i want.
My SQL:
SELECT '' As ServiceTypeID, 'ADD/EDIT VALUES' As ServiceType FROM ServiceTypes UNION SELECT ServiceTypes.ServiceTypeID, ServiceTypes.ServiceType FROM ServiceTypes
ORDER BY 1 DESC;
View 5 Replies
View Related
Aug 22, 2014
How do I make quotes and invoices submittable with no product and service details? We use sale book tags and have a few tags that are voided. I want to keep them in our records as voided. I've create new quotes for these tags, labelled them as void through products and services, but I can not submit the quote. I think I can not submit the quotes because there is no Total Price amount. How do I submit quotes and then invoices for my records with no Total Price amounts?
View 2 Replies
View Related
Aug 19, 2015
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
View 13 Replies
View Related
Nov 3, 2014
running 3 queries together.
1 qry has a date parameter of start/end date(running before the 3 are together)
Have a field in 1 of the 3 queries with a date field that I need to show being <= the date parameter...
How/where do I use this criteria?
View 3 Replies
View Related
Mar 10, 2006
I have a date and time stamp in a Date/Time field of General Date format (3/1/2006 7:52:25 AM).
I wish to select query on the table's Date/Time field by date portion only (3/1/2006) and not include the time portion (7:52:25 AM) of the field.
Using this expression in the query's criteria - "Between [Enter Start Date: (MM/DD/YY Format)] And [End Date: (MM/DD/YY]" will not return the date ranges as desired without also typing in the full time string.
How can the date integer be parsed out and the query properly expression ed on the criteria field without using VB?
View 7 Replies
View Related
Jul 11, 2014
I currently have a query set up which responds and filters by the date.
I use the current code in the criteria on the query for todays date less 7 days
Code:
DateAdd("d",-7,Date())
This works perfectly and I had no problem with it. However I need to adjust the criteria so it is less than 7days ago OR OLDER....
How would I go about doing that?
View 4 Replies
View Related
Apr 26, 2013
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
View 11 Replies
View Related
Jun 21, 2013
A few months ago I created a report that displays the results of a long union query comprising a dozen or so individual queries, each containing an expression that yields a date (or sometimes date and time). I set the report to group by query and then sort by the date expression. Now for some reason that I can't fathom the report has always only ever offered me the option to sort the date "A to Z", I infer it thinks the date is text, but this misunderstanding has never actually stopped it sorting by date perfectly well. It worked. No problems.
However I have recently added formatting to some of the queries so that they just display date, not date and time e.g. Format([dateandtime],"dd/mm/yyyy"), and now the sort by date in the report no longer works. None of the sorting or grouping options have changed, but it now sorts just by the "dd" component of the date - so it thinks 21st June is later than 20th July. why?
View 14 Replies
View Related
Mar 26, 2013
I've attached a stripped down version of a small order database I'm working on.
A user would enter an order, the amount and the date the order is required by.
As you can see from tbl_seasons, the business has financial periods that match the first and last 6 months of each year. Each season has a start date and end date.
What I'm trying to build are two queries:
1. A query which lists all orders and has an extra field which shows the "season_id" that the order (date) relates to (based one the start date and end date in tbl_seasons)
2. A totals query which shows the total order amounts by season
how I might build these 2 queries.
View 2 Replies
View Related
Aug 17, 2006
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
View 10 Replies
View Related
