Calculated Field to dispaly the value of one field based on another

[David Ball]

Hi Forum,

I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).
I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.
So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).
I tried to do it by setting up the 4 calculated fields like:
10Jul15: Sales
and then adding Criteria like:
“EndDate” = #10/07/2015#
It doesn’t work.
How could I do this?
I have attached a sample database.

Thanks very much
Dave

 

[CJ_London]

you would use a crosstab query

create a calculated column to calculate the week from the end date - this would be your column header. Your sales value would be the Value column (and counted). All the other columns you want to display would be your row columns.

Your end date can be calculated from the sales date as follows

(salesdate)-weekday((salesdate),vbSaturday)+7

 

[David Ball]

Thanks, I don't really understand any of that. But thanks anyway.

Dave

 

[CJ_London]

try creating a crosstab - should become clearer