ACCESS FEED: Re: [MS_AccessPros] help with my query

Selasa, 19 Mei 2015

Re: [MS_AccessPros] help with my query

John,

It does not work, because it is using the month as the column, so for example all October invoices would be in column 10, regardless. I think I need to expand the statement, if <365 then month as the column etc.. if >364 and <730 then month+12 as the column, but I am having trouble creating the 2 conditions in the statement.
Sarah
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Sarah-

I think the key is here:

PIVOT IIf(Forms![aging sched]![aging date]-(IIf(Forms![aging sched]!Sortby1="Service Date",[a/r open]![invoice date],[a/r open]![sdate from]))<365,Month(IIf(Forms![aging sched]!Sortby1="Service Date",[a/r open]![invoice date],[a/r open]![sdate to])),0) In (1,2,3,4,5,6,7,8,9,10,11,12,0);

Does it work if you do:

PIVOT IIf(Forms![aging sched]![aging date]-(IIf(Forms![aging sched]!Sortby1="Service Date",[a/r open]![invoice date],[a/r open]![sdate from]))<730,Month(IIf(Forms![aging sched]!Sortby1="Service Date",[a/r open]![invoice date],[a/r open]![sdate to])),0) In (1,2,3,4,5,6,7,8,9,10,11,12,13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 0);

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On May 19, 2015, at 9:29 PM, sarahk@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have the following query that ages my invoices into 12 month and prior column based on the age.(The age can be based on the invoice or start date). I need to expand the query to 24 month and prior column. this is the current query:

TRANSFORM airtemp2!amount AS amount
SELECT [a/r open].[patient id], Patient.[last name], Patient.[first name], [a/r open].[invoice date] AS [invoice date], airtemp2.amount AS total, [a/r open].[invoice#], [a/r open].[sdate to] AS [sdate to], [a/r open].[sdate from] AS [sdate from], airtemp2.discount, Patient.[tel #1], [a/r open].insrcd, [a/r open].[assignment#], airtemp2!amount AS balance
FROM airtemp2 LEFT JOIN (Patient RIGHT JOIN [a/r open] ON Patient.[patient id] = [a/r open].[patient id]) ON (airtemp2.[assignment#] = [a/r open].[assignment#]) AND (airtemp2.[invoice#] = [a/r open].[invoice#])
WHERE (((airtemp2.amount)<>0))
GROUP BY [a/r open].[patient id], Patient.[last name], Patient.[first name], [a/r open].[invoice date], airtemp2.amount, [a/r open].[invoice#], [a/r open].[sdate to], [a/r open].[sdate from], airtemp2.discount, Patient.[tel #1], [a/r open].insrcd, [a/r open].[assignment#]
ORDER BY Patient.[last name], Patient.[first name], [a/r open].[patient id], [a/r open].[invoice#]
PIVOT IIf(Forms![aging sched]![aging date]-(IIf(Forms![aging sched]!Sortby1="Service Date",[a/r open]![invoice date],[a/r open]![sdate from]))<365,Month(IIf(Forms![aging sched]!Sortby1="Service Date",[a/r open]![invoice date],[a/r open]![sdate to])),0) In (1,2,3,4,5,6,7,8,9,10,11,12,0);

If the invoices are between 365 and 730 days, then I need to add the columns for 13,14 ... 24 months.
I am having great difficulty trying to-do this.
As always all help is greatly appreciated.

Sarah