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

Sarah-

Ah, I think I got the arguments backwards. If you remove the IN clause, I bet you see negative numbers. Try this:

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]))<730,DateDiff("m", IIf(Forms![aging sched]!Sortby1="Service Date",[a/r open]![invoice date],[a/r open]![sdate to], Forms![aging sched]![aging date])),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 21, 2015, at 9:12 PM, sarahk@schemesoftware.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-

I tried this. this does not do 'anything'. The query runs, but all the columns 0-24 are empty.

This is what the query looks like now:

TRANSFORM airtemp2!amount AS amount

WHERE (((airtemp2.amount)<>0))

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]))<730,DateDiff("m", Forms![aging sched]![aging date], 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);

Sarah

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Sarah-

The problem is you're displaying the month the item occurred, not the length of time ago. Try this:

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,DateDiff("m", Forms![aging sched]![aging date], 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 20, 2015, at 7:50 PM, sarahk@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I am using the Crosstab to assign the invoice balance to the appropiate age column: 1,2 etc.. Currently I am using 12 columns, but I would like to expand it to 24.
Sarah

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Sarah-

One record per unique combination of what?

If all you want is a list, why use a Crosstab?

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 20, 2015, at 3:02 PM, sarahk@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
You are right: it is not a conventional aging. I want to show based either on the invoice or sdate the last 24 months. The aging date is not todays date, it is entered on a form:Forms![aging sched]![aging date].

I also want one record per unique combination. The report then summarizes etc.
Thanks for your help
Sarah

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Sarah-

Ah, right. I knew it couldn't be THAT simple! Let's give it another whack, but first I have some questions:

Your current formula is simply returning the month number for anything that is less than one year old using either the [invoice date] or the [sdate to] field depending on what's in Sortby1. That in my mind is not "aging" because anything in May a year ago will show up in the "5" column. That simply shows which ones are less than a year that occurred in the May month.

I also don't see any Sum or Count, so you'll get one line per unique combination of patient Id, last name, first name, invoice date, amount, invoice#, date to, state from, discount, tel #1, insrcd, and assignment#. That gets you one line per record with the amount tossed out in a "month" column that is either the invoice date or the sdate to. Is that what you really want?

I would think what you really need is the number of months ago (up to 24) based on today's date.

Please explain to me a bit more what you're trying to do.

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 20, 2015, at 5:02 AM, sarahk@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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