Query on dates

[Guest]

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?

 

[Guest]

In that example I used the filter on the Date received field

Select * From invoice Where [Date received] < DateAdd("d",-30,Date())

The DateAdd function will give you the date 30 days before the current date.

 

[Guest]

Thanks Ofer,

Would i add additionals columns in query for the 60 and 90 periods since
this statement returns the 30 aging?

In that example I used the filter on the Date received field

Select * From invoice Where [Date received] < DateAdd("d",-30,Date())

The DateAdd function will give you the date 30 days before the current date.

--
Good Luck
BS"D

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?

 

[John Spencer]

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
..

 

[Guest]

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);

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?

 

[John Spencer]

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
..

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);

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?

 

[Guest]

Thanks John,

I tried the additional statement but when i ran the query, it actually
removed data from the results. In any event, the age bucket suggestion looks
to be adaptable for additonal fields and i may use that as well. Thanks for
the great support!

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
..

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);

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?