Sum Values on weekends and total for Monday

[Bongard]

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

 

[Guest]

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.

 

[Bongard]

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!

 

[Guest]

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 [ ] .

 

[Bongard]

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!

 

[John Spencer]

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]

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

 

[John Spencer]

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
..