Query to pivot table

[Guest]

I am doing a query on a Access db and going straight to a pivottable. The query includes dates, however I want to add a field that I can sort in the page field for weekending dates. So if i have dates of 2/1/04, 2/2/04, and 2/8/04 I want a field that calculates the last day of the week for each date so that I can filter my data based on the weekending date. the field would calculate the above data as 2/7/04, 2/7/04 and 2/14/04. I tried editing the query and add a column to calculate each dates weekending date but I cannot figure out how to do that. I am using office 2000

I am hoping I do not have to paste the data into the excel worksheet and add a formula becuase the data could conceivably exceed the 65000 data limit and it's soooo much easier to set up the table and then just click refresh. Thanks for the help.

 

[Debra Dalgleish]

You can calculate the week ending date in your Access query, and use
that field in the Page area. For example:

WeekEndDate: DateAdd("d",7-Weekday([InvDate]),[InvDate])

 

[Guest]

Forgive my ignorance. How do I add that column and then add that calculation to the column? I've looked and tried several different things but to no avail.

 

[Debra Dalgleish]

Open the Access query in design view.
In the grid, at the bottom of the query window, you'll see the field
names for fields that are in the query output.
In the first blank column, type a name for the calculated field, then a
colon, then the formula, e.g.:

WeekEndDate: DateAdd("d",7-Weekday([InvDate]),[InvDate])

Replace InvDate with the name of your date field.
Run the query, to test it, then close and save.

 

[Guest]

I've tried to do this but it has not worked. I was successful when I was in ACCESS and did a query, but I am doing this query from excel and querying an access db. It won't let me add an equation/formula column.

 

[Debra Dalgleish]

If you're using Microsoft Query to import the data, you can add the
calculation there:

invdate+7-weekday(invdate)