rabbit Posted April 28, 2006 Share Posted April 28, 2006 OK I have to find the total income per month over the financial year for a dog kennels business.This is what I thought would work: SELECT DatePart(m,tblBOOKING.START_DATE), SUM((tblBOOKING.END_DATE-tblBOOKING.START_DATE)*tblCOST.COST_PER_DAY)FROM tblBOOKING, tblCOSTWHERE (((tblBOOKING.START_DATE)>=[input year start]) And ((tblBOOKING.END_DATE)<=[input year end]))GROUP BY DatePart(m,tblBOOKING.START_DATE)ORDER BY DatePart(m,tblBOOKING.START_DATE); and whilst it saves when I run the query I get the very useful error message stating its either typed wrong or is to complex to be evaluated.Now, I'm seriously new to sql, have only been doing it a few days, so any help would be very appreciated.I'm also having a problem with this code, which is supposed to display a walking a feeding schedule, but its treating tblWALK_FEED. PM_FEED as a parameter. SELECT tblANIMAL.ANIMAL_NAME, tblWALK_FEED.AM_WALK, tblWALK_FEED.PM_WALK, tblWALK_FEED.AM_FEED, tblWALK.PM_FEED AS Expr1FROM tblANIMAL, tblWALK_FEEDWHERE (((tblWALK_FEED.CURRENT_DATE)=[enter today's date])); Thanks for any help! Link to comment Share on other sites More sharing options...
Guest cbaldwin3 Posted May 8, 2006 Share Posted May 8, 2006 Hello All. I'm new here too. I recently finished up a semester of intro to sql concepts so I figured I might take a stab at this problem.YoursSELECT DatePart(m, tblBOOKING.START_DATE), SUM...MineSELECT DatePart(mm, tblBOOKING.START_DATE),you need 2 m's in your datepart to tell it months---Yours((tblBOOKING.END_DATE - tblBOOKING.START_DATE)*tblCOST.COST_PER_DAY)This looks ok accept the Sum function should be idented on the line below the SELECT. it just makes it a little easier to read. SUM((tblBOOKING.END_DATE - tblBOOKING.START_DATE)*tblCOST.COST_PER_DAY)---YoursFROM tblBOOKING, tblCOSTWhy 2 tables in the from? Are you really trying to join two tables? If so then use a JOIN. (Again I'm new to SQL as well so maybe that way works too, however we learned to use JOIN, Left JOIN, etc...---YoursWHERE (((tblBOOKING.START_DATE)>=[input year start]) And ((tblBOOKING.END_DATE)<=[input year end]))Again I am not familar with [input year start/end] so i would try using a literal or min max functions for those dates.---YoursGROUP BY DatePart(m,tblBOOKING.START_DATE)ORDER BY DatePart(m,tblBOOKING.START_DATE);Add the second m to your datepart and that should work.I hope I helped a lil. I know very basic SQL stuff so I could share what I've learned thus far. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now