Want to count number of organisations with date range in crosstab query



I have a table suppliers that have the following fields

Organisation ID
Contract ID
Date Purchasable.

I want to put in row headings for date between [end date]- 7, previous
week, year, and cumulative.

I have tried to run a query something like below but is not working.
Could someone give me suggestions? I have been using the partition

TRANSFORM Count([Contract and Supplier].Date_Supplier_Purchasable) AS
SELECT [Contract and Supplier].Supplier_Purchasable, Count([Contract
and Supplier].Date_Supplier_Purchasable) AS [Total Of
Date_Supplier_Purchasable], (Count([Contract and
Supplier].[Organisation ID]) AS [CountOfOrganisation ID]
FROM [Contract and Supplier]
GROUP BY [Contract and Supplier].Supplier_Purchasable
PIVOT [Contract and Supplier].Date_Supplier_Purchasable;


What I mean is, is there a way of counting the values of
Date_Supplier_Purchasable so that it will count the number of
organisation that are purchasable / not purchasable for the current
week, the previous week, the current year and cumulative?


I just want to know is it is possible to count organisations that for
in custom date ranges for the date_purchasable field. Partition can
only set even ranges and not uneven ones. I wonder if a PIVOT can
achieve what I want. Either way if someone can verify whether this is
possible or not possible that would be great.

Please bear in mind that the SQL posted above is not working

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads