If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sum Values on weekends and total for Monday
I have a query which I am currently doing a huge workaround with an
export in Excel to sum up weekends values and total them on Monday. For ex... Purchases: 1/6/07 (Sat) $500 1/7/07 (Sun) $750 1/8/07 (Mon) $250 Will be seen as one purchases on 1/8/07 for $1,500. I work in an accounting department where we are only using weekday values for our accounting system although we still have purchases in our system on the weekend. I think that if I could do this in Access before exporting to Excel it would be much easier and quicker in the long run than making sure 15 columns of vlookups were always referring to the right cells and making updates throughout the year. Any help would be much appreciated. Thanks in advance, Brian |
#2
|
|||
|
|||
Sum Values on weekends and total for Monday
SELECT DatePart("yyyy", [wa_date], 7) AS TheYear,
DatePart("ww", [wa_date], 7) AS TheWeek, IIf(DatePart("w", [wa_date], 7)4, 3, DatePart("w", [wa_date], 7)) AS TheDOW, Max(tblDatesWeekends.wa_date) AS MaxOfwa_date, Sum(tblDatesWeekends.Purchases) AS SumOfPurchases FROM tblDatesWeekends GROUP BY DatePart("yyyy", [wa_date], 7), DatePart("ww", [wa_date], 7), IIf(DatePart("w", [wa_date], 7)4, 3, DatePart("w", [wa_date], 7)) ; You just need to put in the correct names for wa_date and tblDatesWeekends. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Bongard" wrote: I have a query which I am currently doing a huge workaround with an export in Excel to sum up weekends values and total them on Monday. For ex... Purchases: 1/6/07 (Sat) $500 1/7/07 (Sun) $750 1/8/07 (Mon) $250 Will be seen as one purchases on 1/8/07 for $1,500. I work in an accounting department where we are only using weekday values for our accounting system although we still have purchases in our system on the weekend. I think that if I could do this in Access before exporting to Excel it would be much easier and quicker in the long run than making sure 15 columns of vlookups were always referring to the right cells and making updates throughout the year. Any help would be much appreciated. Thanks in advance, Brian |
#3
|
|||
|
|||
Sum Values on weekends and total for Monday
Thanks for your reply Jerry. I have entered that SQL into my query and
it is getting hung up on the last character in the whole SQL the ; It gives me the message "The LEVEL clause includes a reserved word or argument that is misspelled or missing, or the punctuation is incorrect" Any ideas? I tried deleting it but it gives me the same message and points me to the line below Thanks! |
#4
|
|||
|
|||
Sum Values on weekends and total for Monday
Is your date field named "Date" by chance? That could run you into a reserved
word problem. Make sure that field and table names are surrounded in square brackets [ ] . -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Bongard" wrote: Thanks for your reply Jerry. I have entered that SQL into my query and it is getting hung up on the last character in the whole SQL the ; It gives me the message "The LEVEL clause includes a reserved word or argument that is misspelled or missing, or the punctuation is incorrect" Any ideas? I tried deleting it but it gives me the same message and points me to the line below Thanks! |
#5
|
|||
|
|||
Sum Values on weekends and total for Monday
Well I thought maybe my old field would be tripping it up [Dates} so I
changed the field name to [IncomeDate] and it is still not working.Here is the SQL ------ SELECT qry_IncomeItemsTotals.Portfolio, qry_IncomeItemsTotals.IncomeDate, qry_IncomeItemsTotals.SumOfTotal, datepart("yyyy",[IncomeDate],7) AS TheYear, DatePart("ww",[IncomeDate], 7) As TheWeek IIf(DatePart("w",[IncomeDate],7)4,3,Datepart("w", [IncomeDate],7)) As TheDOW, Max(qry_IncomeItemsTotals.IncomeDate) as MaxOfIncomeDate, Sum(qry_IncomeItemsTotals.SumOfTotal) As TotalIncome FROM qry_IncomeItemsTotals; GROUP BY DatePart("yyyy", [IncomeDate], 7), DatePart("ww", [IncomeDate], 7), IIf(DatePart("w", [IncomeDate], 7)4, 3, DatePart("w", [IncomeDate], 7)); ------ Maybe there is still something else that I am not doing right, I'm not sure but if you see anything else please let me know. It would be great if I could actually get this to work out of Access instead of Excel! |
#6
|
|||
|
|||
Sum Values on weekends and total for Monday
How about something like the following
SELECT Portfolio , DateAdd("d",Choose(WeekDay(IncomeDate),1,0,0,0,0,0 ,2),IncomeDate) as SumDate , Sum(SumOfTotal) as TheTotal FROM qry_IncomeItemsTotals As Q GROUP BY Portfolio , DateAdd("d",Choose(WeekDay(IncomeDate),1,0,0,0,0,0 ,2),IncomeDate) By the way, your posted SQL statement has a semicolon in the FROM line which will cause an error since the semicolon only goes at the very end of a query and is not really needed. '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Bongard wrote: Well I thought maybe my old field would be tripping it up [Dates} so I changed the field name to [IncomeDate] and it is still not working.Here is the SQL ------ SELECT qry_IncomeItemsTotals.Portfolio, qry_IncomeItemsTotals.IncomeDate, qry_IncomeItemsTotals.SumOfTotal, datepart("yyyy",[IncomeDate],7) AS TheYear, DatePart("ww",[IncomeDate], 7) As TheWeek IIf(DatePart("w",[IncomeDate],7)4,3,Datepart("w", [IncomeDate],7)) As TheDOW, Max(qry_IncomeItemsTotals.IncomeDate) as MaxOfIncomeDate, Sum(qry_IncomeItemsTotals.SumOfTotal) As TotalIncome FROM qry_IncomeItemsTotals; GROUP BY DatePart("yyyy", [IncomeDate], 7), DatePart("ww", [IncomeDate], 7), IIf(DatePart("w", [IncomeDate], 7)4, 3, DatePart("w", [IncomeDate], 7)); ------ Maybe there is still something else that I am not doing right, I'm not sure but if you see anything else please let me know. It would be great if I could actually get this to work out of Access instead of Excel! |
#7
|
|||
|
|||
Sum Values on weekends and total for Monday
Thanks for your input John, that looks like its working. I am not very
familiar with the date add function so I will have to play around with it a bit to make sure that I can answer questions/support this thing in the future. I looked the function up on msdn and the help but can you give me anymore of an explanation of how this thing works? I would much appreciate it! Thanks, Brian |
#8
|
|||
|
|||
Sum Values on weekends and total for Monday
DateAdd("d",Choose(WeekDay(IncomeDate),1,0,0,0,0,0 ,2),IncomeDate)
-- Weekday returns a number between 1 and 7 representing Sunday to Saturday based on the date -- Choose selects an item in the list of items following based on the number it sees as the first item in the list, so it is going to choose from 1 to 7 -- DateAdd adds an interval to a date. Since we specified "d" then it adds days to the date. One thing that may cause you a problem is if IncomeDate is ever blank(Null). Then you will get an error. So, if that happens use IIF and test for null before attempting the calculation. IIF([IncomeDate] is Null, Null, DateAdd("d",Choose(WeekDay(IncomeDate),1,0,0,0,0,0 ,2),IncomeDate) ) John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Bongard" wrote in message oups.com... Thanks for your input John, that looks like its working. I am not very familiar with the date add function so I will have to play around with it a bit to make sure that I can answer questions/support this thing in the future. I looked the function up on msdn and the help but can you give me anymore of an explanation of how this thing works? I would much appreciate it! Thanks, Brian |
Thread Tools | |
Display Modes | |
|
|