Re: [MS_AccessPros] help with my query

 

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@schemesoftware.com [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

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (8)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___