Jump to content

sql in access


rabbit

Recommended Posts

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

  • 2 weeks later...
Guest cbaldwin3

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...