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

  • Thread starter Thread starter philip.au
  • Start date Start date
P

philip.au

I have a table suppliers that have the following fields

Suppliers
Organisation ID
Contract ID
Purchasable
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
function.

TRANSFORM Count([Contract and Supplier].Date_Supplier_Purchasable) AS
CountOfDate_Supplier_Purchasable
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]
Partition(Date_Supplier_Purchasable,
DateDiff(mm/dd/yyyy,02/06/2005,02/13/2005))
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
 
Back
Top