If the partition functions gives you what you want (every time with the same
fields), then I would probably use that to build the report with one change
to the query. That change would be listing the field names that are created
by the partition function. You will have to be careful to make sure you
exactly match the column titles.
PIVOT Partition(Now()-[CntryRecvdDate],1,120,30) In ("1: 30","31: 60","61:
90","91: 120", "121:")
The reason to do that is performance when you are setting up the design of
the report. PLUS by designating the column titles you will guarantee that
the column will be returned when the query is executed.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
acss said:
Thanks John,
That was great. I did some experimenting with your sql and a crosstab
using
partition and that worked as well. Could you give me your professional
opinion as to the most logical or efficient use of these two sql usage
since
i need the query to create a report ?
TRANSFORM Sum(VendorInv.InvAmt) AS SumOfInvAmt
SELECT VendorInv.VendorID, VendorInv.InvoiceID, VendorInv.CntryRecvdDate
FROM (VendorExpCtr INNER JOIN Vendor ON VendorExpCtr.VendorExpCtrID =
Vendor.VendorExpCtrID) INNER JOIN VendorInv ON Vendor.VendorID =
VendorInv.VendorID
GROUP BY VendorInv.VendorID, VendorInv.InvoiceID, VendorInv.CntryRecvdDate
ORDER BY VendorInv.VendorID
PIVOT Partition(Now()-[CntryRecvdDate],1,120,30);
John Spencer said:
Perhaps something like the following UNTESTED SQL
SELECT [Invoice Number]
, [Invoice Date]
, 30 * (DateDiff("d", [Invoice Date], Date()) \30 ) as AgeBucket
FROM [Your Table]
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
I have records with dates. How do I set up a query so when a report is
run
it
would show records older the 30 days, records older that 60 days,
etc.The
fields i have are Date received ,Invoice date ,Invoice Number which
are
from
the invoice table. There is also vendor number field from vendors table
to
identify the invoices from each vendor. Can this be done?