Query with Daily Fields based on Parameters

[AccessQuestions]

Hello,

I am trying to create a query that will provide a field for each day of a month. However, I want the query to be able to work for any month that I want to run on based on a parameter. Basically I want this:

Day 1: Sum(IIF([ReleaseDate]=#[# of Month]/1/[# of Year]#,[GamesSold],0)
Day 2: Sum(IIF([ReleaseDate]=#[# of Month]/2/[# of Year]#,[GamesSold],0)
and so on for 31 fields.

This is not currently working. If you know of a way this can be written correctly please let me know. I appreciate the help!

 

[Mile-O]

Crosstab query?

 

[plog]

This would do it:

Day1: Sum(IIf([ReleaseDate]=CDate([# of Month] & "/1/" & [# of Year]),[GamesSold],0))

I'm on the fence about a cross-tab. This and a cross-tab are both clunky, so by comparison, this isn't a horrible way to do it.

 

[Mile-O]

I'm on the fence about a cross-tab. This and a cross-tab are both clunky, so by comparison, this isn't a horrible way to do it.

I'm not the biggest fan of crosstabs (although the implementation of them in Crystal Reports is, as you would expect from a reporting tool, so much better), however there is the fact, at least, that crosstabs are pre-programmed elements of Access and should therefore be used, where needed and possible, instead of created multiple formulas. Get a typo in one of those formulas, are you going to spot it straight it away? That's a risk.

 

[plog]

I think the risk for error is mitigated by the complexity of setting up a cross tab for this instance--thus my fence sitting. To meet his stated goals a Cross-tab will require a table created to hold 31 values, a sub-query where he LEFT JOIN his actual results to that new table so that all 31 days appear, then some sort of hack to make those 31 values appear in the correct order, because once they get cross-tabbed they become strings and will appear in this order: "1, 10, 11...".

His solution is to copy, paste and change the day portion of his formula 31 times. There's downside to both methods, I think the copy and paste is the preferred in this situation, especially when you factor in maintainability/extendibility. He will be more easily be able to make changes to this way as opposed to a cross-tab.

 

[AccessQuestions]

I did indeed give up on the crosstab previous to my post, and believe the fields to be better suiting my purpose. It is a copy/paste with just changing the day each time. Hopefully I can do this without errors. Expression works, no issues, Plog. Thank you for helping.

 

[AccessQuestions]

OK, I spoke too soon.

The query will not run for all of February if I include the formula for days 29-31. I'm guessing access can't understand 2/29/2014, so it just won't allow the query to run. I obviously don't expect to get data for this timeframe, but i would prefer zeros showing on the report if it means I can use the same query every month.

I tried: IIf([# of Month]=2,0,Sum(IIf([ReleaseDate]=CDate([# of Month] & "/29/" & [# of Year]),[GameSold],0)))

But access is still apparently trying to solve for the illogical date. Any suggestions?

FYI I'm getting the "This expression is typed incorrectly, or is too complex..." error.

 

[plog]

I would move the month and year criteria to its own fields such as:

ReportMonth: Month([ReleaseDate])
ReportYear: Year([ReleaseDate])

Then underneath each of those, in the criteria section put [# of Month] and [# of Year] respectively. That limits the data.

Then for your 31 fields I would use the Day function(http://www.techonthenet.com/access/functions/date/day.php) to determine the day number of the month like so:

Day1: Sum(IIf(Day([ReleaseDate])=1,[GamesSold],0))
Day2: Sum(IIf(Day([ReleaseDate])=2,[GamesSold],0))
Day3: Sum(IIf(Day([ReleaseDate])=3,[GamesSold],0))

That way you avoid creating invalid dates.

 

[AccessQuestions]

Problem solved. Thanks again