Starting mileage on 1st day of month, ending mileage on last day of month

Hello!
I have a table set up with Date, Vehicle Number, Start Mileage and End
Mileage.

I want to be able to pull up the start mileage on the 1st of the month and
the end mileage on the last day of the month.

I have read through other posts and can't seem to put my finger on what I'm
looking for.   
Thank you in advance for the help!!

-- 
Message posted via http://www.accessmonster.com

0
Renaesout
8/3/2007 2:19:38 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
599 Views

Similar Articles

[PageSpeed] 49

The structure you described (Date, Vehicle, Start, End) implies that any 
date, vehicle, start and/or end can be entered.

What do you mean by "pull up the start mileage on the 1st of the month"? 
The way I interpret that, I'd create a query that returns the [StartMileage] 
where Day([MileageDate]) = 1.

By the way, the word "Date" is a reserved word in Access.  If that is 
actually the name of your field, you will only confuse both you and Access 
about which "date" you mean.  Consider renaming the field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Renaesout via AccessMonster.com" <u32422@uwe> wrote in message 
news:76253889c50a1@uwe...
> Hello!
> I have a table set up with Date, Vehicle Number, Start Mileage and End
> Mileage.
>
> I want to be able to pull up the start mileage on the 1st of the month and
> the end mileage on the last day of the month.
>
> I have read through other posts and can't seem to put my finger on what 
> I'm
> looking for.
> Thank you in advance for the help!!
>
> -- 
> Message posted via http://www.accessmonster.com
> 


0
Jeff
8/3/2007 2:52:30 PM
Karl,
thank you so much!  I have never done the join thing, I learned alot!  

Is there a way to set it up so the user gets to pick which month to view, or
which period of time to view, such as Jan 1 - Jan 31, or Jan 1 - Dec 31?  LOL
The fun never stops!  Thank you again for you help thus far!!


KARL DEWEY wrote:
>Try using these three queries.   It pulls last months mileage even if there 
>is no entry on first or last day of the month.
>
>  Renaesout_Start ---
>SELECT Renaesout.[Vehicle Number], Min(Renaesout.[Start Mileage]) AS Start, 
>Min(Renaesout.TripDate) AS [Start Date]
>FROM Renaesout
>WHERE (((Renaesout.TripDate)>=DateAdd("m",-1,Date()-Day(Date())+1)))
>GROUP BY Renaesout.[Vehicle Number]
>ORDER BY Renaesout.[Vehicle Number], Min(Renaesout.[Start Mileage]) DESC;
>
>  Renaesout_End ---
>SELECT Renaesout.[Vehicle Number], Max(Renaesout.[End Mileage]) AS [End], 
>Max(Renaesout.TripDate) AS [End Date]
>FROM Renaesout
>WHERE (((Renaesout.TripDate)<=Date()-Day(Date())))
>GROUP BY Renaesout.[Vehicle Number]
>ORDER BY Renaesout.[Vehicle Number], Max(Renaesout.[End Mileage]) DESC;
>
>    Renaesout_Mileage ---
>SELECT Renaesout_Start.[Vehicle Number], Renaesout_Start.[Start Date], 
>Renaesout_Start.Start, Renaesout_End.[End Date], Renaesout_End.End
>FROM Renaesout_Start INNER JOIN Renaesout_End ON Renaesout_Start.[Vehicle 
>Number] = Renaesout_End.[Vehicle Number];
>
>> Hello!
>> I have a table set up with Date, Vehicle Number, Start Mileage and End
>[quoted text clipped - 6 lines]
>> looking for.   
>> Thank you in advance for the help!!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1

0
Renaesout
8/3/2007 7:35:34 PM
This seems like the long way.

If there is program logic that makes sure that End Mileage is always equal 
to or greater than Start Mileage, and that Start Mileage is always equal to 
or greater than the previous End Mileage, then he should be able to do 
something like:

SELECT [Vehicle Number],
            Year([DateDriven]) as Yr,
            Month([DateDriven]) as Mnth,
            MIN([Start Mileage]) as StartMonth,
            MAX([End Mileage]) as EndMonth
FROM yourTable
WHERE [DateDriven] BETWEEN DateSerial([What year?], [What month (##)?], 1) 
AND DateSerial([What year?], [What month (##)?] + 1, 0) 

HTH
Dale

-- 
Email address is not valid.
Please reply to newsgroup only.


"KARL DEWEY" wrote:

> Try using these three queries.   It pulls last months mileage even if there 
> is no entry on first or last day of the month.
> 
>   Renaesout_Start ---
> SELECT Renaesout.[Vehicle Number], Min(Renaesout.[Start Mileage]) AS Start, 
> Min(Renaesout.TripDate) AS [Start Date]
> FROM Renaesout
> WHERE (((Renaesout.TripDate)>=DateAdd("m",-1,Date()-Day(Date())+1)))
> GROUP BY Renaesout.[Vehicle Number]
> ORDER BY Renaesout.[Vehicle Number], Min(Renaesout.[Start Mileage]) DESC;
> 
>   Renaesout_End ---
> SELECT Renaesout.[Vehicle Number], Max(Renaesout.[End Mileage]) AS [End], 
> Max(Renaesout.TripDate) AS [End Date]
> FROM Renaesout
> WHERE (((Renaesout.TripDate)<=Date()-Day(Date())))
> GROUP BY Renaesout.[Vehicle Number]
> ORDER BY Renaesout.[Vehicle Number], Max(Renaesout.[End Mileage]) DESC;
> 
>     Renaesout_Mileage ---
> SELECT Renaesout_Start.[Vehicle Number], Renaesout_Start.[Start Date], 
> Renaesout_Start.Start, Renaesout_End.[End Date], Renaesout_End.End
> FROM Renaesout_Start INNER JOIN Renaesout_End ON Renaesout_Start.[Vehicle 
> Number] = Renaesout_End.[Vehicle Number];
> 
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "Renaesout via AccessMonster.com" wrote:
> 
> > Hello!
> > I have a table set up with Date, Vehicle Number, Start Mileage and End
> > Mileage.
> > 
> > I want to be able to pull up the start mileage on the 1st of the month and
> > the end mileage on the last day of the month.
> > 
> > I have read through other posts and can't seem to put my finger on what I'm
> > looking for.   
> > Thank you in advance for the help!!
> > 
> > -- 
> > Message posted via http://www.accessmonster.com
> > 
> > 
0
Utf
8/3/2007 8:02:03 PM
Reply:

Similar Artilces:

CMonthCalCtrl get month
Hi, how can I get the currently displayed month from the CMonthCalCtrl? Can't find the answer anywhere. They all are talking about the selected date. I need to know just the month. Thanks in advance. ...

can't sort on last name-outlook 2003
copied my 2000 contact list into 2003. when I create a new message and hit the "To" button, the names in the "Select names" dialogue box come up alphabetically, but by first name. I want it by last like they were in 2000. I've tried every setting in the tools, options, contact - no luck. any suggestions? thanks, Larry Try this: Start Outlook 2003. On the Tools menu, click E-mail accounts. Click View or change directories or address books, and then click Next. Click Outlook Address Book, and then click Change. Under Outlook Address Books, click the addr...

Showing only the last x entries?
Hi I have a graph with a moving average trendline, however the amount o data is so much that the graph is not quite readable. Is it possible to only show the last x entries in a graph. This shoul be done automatically. Basically to rephrase the question: I need the graph to be zoomed t the last x entries. Any help would be appreciated Thanks Tali -- Message posted from http://www.ExcelForum.com Hi see your other post -- Regards Frank Kabel Frankfurt, Germany > Hi > > I have a graph with a moving average trendline, however the amount of > data is so much that the graph is...

starting value for autonumber in access 2007
I need help adding a table. But I am being asked to use autonumber for the key but they want the key to start at 100 and increase by 5 for each new record. Please note I am a novice and don't have programming experience. I was told that in prior version of access I could specify the start number. How can I do this in access 2007 The Access interface doesn't do this for you. You will need to set the Seed and Increment properties of the column programmatically. Simplest way is to: 1. Create a new query. 2. In first dialog, choose Design view. 3. Cancel the second dialog (Add T...

cannot start Microsoft office. Cannot open the Outlook window. ol 2007
Computer info: Vista sp1 Office Pro 2007 sp2 MSO 12.0.6424.1000 attached to a AD Win 2003 network running Exchange 2003 sp2 What I was doing prior to receiving error: I was attempting to install Vista sp2, when I was notified that I must install a service pack version that was compatible with the languages installed on the system or remove the unneeded ones (only need US english). I decided to uninstall the unneeded languages. After completing the required reboot, I attempted to check my e-mail and got the error message... I get the this error message whenever I attempt to open outlo...

Can Start Information Store
Dear , I Have using Window 2000 with Exchange 5.5 SP4 And today My IS auto Stop and can't restart , I need to restart my Server to fix it . And it Display beblow Error on Event Log Do anyone know why and how to prevent it Regards, Source : MS ExchangeIS Event ID : 1120 Description Error 0xfffffbf8 initializing the Microsoft Exchange Server Information Store database. & Source : MS ExchangeIS Event ID : 5000 Description Unable to initialize the Microsoft Exchange Information Store service. Error 0xfffffbf8. Regards, Sean Sean You might want to take a look at this and see if i...

Windows XP Start, Program menu question
Am running Windows XP with the Start menu set to classic setting. Problem is that when I click on Start, Programs only the catagories that I have used lately come up such as Accessories - Games - etc. They all used to come up which is what I want it to be. How do I get Programs under Start, Programs to show again when I move the mouse over them and not just the ones I used lately?? They all used to show until the setting got switched somehow. TIA On Jan 28, 7:43=A0pm, kraut <NewsGroupsPle...@NewsGroupsPlease.org> wrote: > Am running Windows XP with the Start ...

My Application has suddenly started playing up
Hi I have been working on a project for 6 weeks now. Suddenly, a form which was working perfectly well is doing strange thing. Suddenly I can't input data into any of the fields. I have checked all the .enabled and .locked properties, the .AllowAdditions and .AllowEdits I have tried recompiling, compact and repair, even rebooted my PC. What the flaming heck is going on? Stapes On Fri, 14 Mar 2008 07:59:15 -0700 (PDT), Stapes <steve.staple@gmail.com> wrote: >Hi > >I have been working on a project for 6 weeks now. Suddenly, a form >which was working perfectly well is d...

Omega Speedmaster Day Date Mens Watch 323.53.40.44.01.001, Best Wristwatch World
Omega Speedmaster Day Date Mens Watch 323.53.40.44.01.001, Best Wristwatch World Click Here To Website : http://www.watchess.net/Omega_Speedmaster_Day_Date_Mens_Watch_323.53.40.44.01.001.html Wristwatch World: http://www.watchess.net/ Omega Speedmaster Day Date Mens Watch 323.53.40.44.01.001 Information : Brand : Omega Watches ( http://www.watchess.net/Omega_Watches.html ) Gender : Mens Code : 323.53.40.44.01.001 Also Called : 32353404401001 Case Material : 18k Rose Gold Case Thickness : Dial Color : Black Bezel : 18K Rose Gold Movem...

Disappearing last name
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: intel With Entourage, Word, Power Point and Excel, after typing my last name and hitting the space bar it disappears. You can imagine how inconvenient this is. Any ideas how I can fix this? Weird. Try looking in Tools | AutoCorrect, preferably in Word, and see if there is an AutoCorrect entry set up to replace your name with anything else. Lois_in_VT@officeformac.com wrote: > Version: 2004 > Operating System: Mac OS X 10.4 (Tiger) > Processor: intel > > With Entourage, Word, Power Point and Excel, after typing...

Custom number format always defaults last number to 0.
I am trying to format cells to input credit card numbers in Excell 2000. When I set up a custom number format for four sets of four numbers (####-####-####-####) the result always changes the last number to a 0. How do I overcome that? Excel only lets 15 digits be used in numbers To have the 16th number most people use text. "scubadave" wrote: > I am trying to format cells to input credit card numbers in Excell 2000. > When I set up a custom number format for four sets of four numbers > (####-####-####-####) the result always changes the last number to a 0. How...

Watching Threads I Start
Running Live Mail under W7 I have 'Mark threads I start as watched' checked. My first post when I start a thread shows up red but replies to that thread do not. If I have view set to show 'hide read messages' they replies are not in red and I have missed some replies. How do I get, if possible, all the replies to my thread to show as watched? Thank you. wysiwyg -- ....winston ms-mvp mail "Jack Gillis" wrote in message news:uD1NZJtTLHA.4980@TK2MSFTNGP04.phx.gbl... Running Live Mail under W7 I have 'Mark threads I start as w...

How do you schedule meetiings with customers, so it ends under history ?
Hey, were just beginning working with CRM in my company, but im not sure which workaround is the best for meetings with customers. Case: We are running a oublic calender for the people in our house. We want to register if a salesperson having a meeting, so we have it in the customers history. First i tried to be in the public calender, inviting, everyone from the house, and also the client, but im not sure some of the things in the calender entry, the client need to see that. So, how is your workarounds in your companys ? Best regards Kim Hi Kim, Is your public calendar a CRM...

Calculate number of days
I am using Excel 2000. I need help with a formula. In A1 I have # of years, in A2 I have # of months and in A3 I have # of days. In A4 I want to add A1 through A3 and show the total as number of days. Thanks for your help. That can't be done... the number of days in the month depends on the start date for the calculation. For example, two months starting January 1st has a different number of days in it than two months starting on March 1st. -- Rick (MVP - Excel) "Linda P." <Linda P.@discussions.microsoft.com> wrote in message news:F7398AB5-4FC6-4...

Start Up Problem 06-20-10
I'm using XP home SP3 When I start the computer it gets to the Windows XP screen and shuts down. When I restart I get a range of options including start normally, start using last known working configuration and starting in safe mode. The first two usually cause a repeat of the problem though sometimes it starts normally. Starting in safe mode then restarting the computer seems to work every time. Going back to a system restore point before the problem occurred just produces a message that the system cannot be restored. I thought about re-installing windows but when I found...

displaying the months on a graph by fiscal year not calendar year
I have a query in which I have included the tblmonth, which has a number and its corresponding month according to fiscal year...i.e., Month, MonthID October, 1 Novemer, 2 December, 3 and so on. I am creating chart off of this query and need the chart to show display the actual month but sort it in the order of MonthID. For example my graph has the months listed at the bottom but they go in alpha order versus fiscal year order which is what I need. How can I correct this so the graph will display the appropriate month in its appropriate order? I got this one figured out. Thanks &quo...

Access Front end with MySQL or SQL backend (multi-user)
I have a huge excel spreadsheet with many worksheets tabs that I need to convert to a data entry database. I'm considering using Access as the front end with maybe MySQL or I think SQL which is free with MS Office. The problem is 1. We can have more than 75 concurrent users 2. budget is extremely limited, so I'm looking at Access front and/or a free SQL backend Will this combination support 75+ concurrent users? and if not, does anyone have any other suggestions? Thanks! Jorge -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-conve...

Last Position
Hello Access Boys and Girls Everywhere,Does anyone know what the shortcut keys (I think these are sometimesknown as 'hot keys') is for last position in the code window. It isaccessable by right clicking and selecting the bottom option. Howeverwhen you are programming and you just want to look something up it isquicker by far to hit a key combination rather than reach for themouse, right click, move to the bottom option, and move your hand backto the keyboard.Nick Ctrl+Shift+F2 (it's on the View menu).-- Brendan ReynoldsAccess MVP"Nick 'The Database Guy'" <nic...

Front End Services Question
I was going through one of my books and came across advise that Exchange Management, MTA Stack, and Routing Engine should be set to disabled on then front end server. I had never heard of this. Has anyone else? Fred T2 http://www.microsoft.com/technet/prodtechnol/exchange/guides/E2k3SecHardGuide/e466094f-c287-4498-aa2b-8845d107f8f3.mspx -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "FredT2" <Fredt2@mail.com> wrote in message news:%23OJ5hSZAGHA.3496@TK2MSFTNGP11.phx.gbl... >I was going through one of m...

Office X and home directory slowness on 1st login
Hello world. After a quick search of the group here, I do not see anything that matches so here goes... A friend is a new netadmin at a local school - xserves host user home directories. Unfortunately, I am not there and do not have any further specifics but here is the question. When my friend installs Office X on a machine, reboots and then logs in to it, it takes a long time for Office apps to start (10 min+). Logout, login again, Word starts in 3 seconds. He logs into ANOTHER machine which Office is installed on with the SAME user account as before, launch time is 10 min+. Logs out, log...

I would like to make a schedule for my dept. Who works what days .
I am supervisor for a nursing dept. I need to make a schedule for them to follow as to who works when and what shift. I need to be sure they all get the hours that they are required to work. Is there a template that would help me and be able to sort things out in the schedule as well? ...

HELP! Procedure or Program suggestion for finding multiple - the last 4 occurances
This is a multi-part message in MIME format. ------=_NextPart_000_0009_01C67D09.E7F4DE00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I need help in finding a procedure or program suggestion for finding = multiple - the last 4 occurances - of recurring appointments in Outlook. The standard "find" seems to have a bug in that it will not find all = occurances of recurring appointments, it will not find the last series = of a recurring appointments -i.e. the last months worth that has already = occured - and in general returns ...

Calandar invite for two days to choose from
Can I create a meeting invitation that recurs on Tuesday and Thursday, but the invitees can select one or the other that they want to attend? In other words, I'll be having the same meeting twice a week and some will attend one and some the other but I want it in one invite. Any thoughts? Thanks! "Amy" <Amy@discussions.microsoft.com> wrote in message news:6824F50D-8E5F-4DD0-826A-6C01547BB1D8@microsoft.com... > Can I create a meeting invitation that recurs on Tuesday and Thursday, but > the invitees can select one or the other that they want to attend?...

If Statement
I'm trying to develop a formula to calculate a sales team incentive bonus. A bonus amount is calculated based on sales made, but then is adjusted 3 months later based on the criteria below. Less than 50% of sales still on the books 0% payable 50% to 60% of sales 25% 61% to 70% of sales 50% 71% to 80% of sales 75% 81% to 90% of sales 100% Over 90% of sales 125% I'm afraid I don't know where to start - can anyone point me in the right direction? Thanks Look at the VLOOKUP() function Here's an excellent tutorial: http://www.contextures.com/xlFunctions02.html -- ...

Event ID 474 while online defrag starts
Hi, server w2k3 enterprise , exchange 2k3 enterprise private stores 10 gig and 8.5 gig I started seeing event id 474 daily when my exchange online defrag starts up at 1am. I looked it up in knowledgebase q327334. I've ran hardware diags on the hard drive subsystem and found that there were problems with the raid controller etc. I've since replaced them (under warranty). Anyways, because of a string of hardware/software problems etc I don't have a backup that goes back before this error. So I can't (am very reluctant to) restore from backup. I've been thinking of...