ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Evo-ERP and DBA Classic >> Sales >> Is there 5 years history anywhere
http://www.istechforum.com/YaBB.pl?num=1224009990

Message started by martel-carol on 10/14/08 at 11:46:30

Title: Is there 5 years history anywhere
Post by martel-carol on 10/14/08 at 11:46:30

In the GL I can see 6 years worth of data. Is there somewhere in Sales History (AR-A) that EVO would give me the total sales for each of the last 6 years? It would be nice to see those years in the Customer Menus, without having to go to SA and run reports. All I am looking for is grand totals by year for customers. Also, is there such a thing in AP to see total purchases by year?

Title: Re: Is there 5 years history anywhere
Post by Kelloggs on 10/21/08 at 11:02:14

Do you use MS Access?
If the answer is yes. I can tell you how to get that information

:P

Kelloggs

Title: Re: Is there 5 years history anywhere
Post by martel-carol on 10/21/08 at 11:12:48

Yes, I have Access

Title: Re: Is there 5 years history anywhere
Post by Kelloggs on 10/21/08 at 12:40:16

Create a Pass-through Query and connect it to the proper DSN.

enter the sql script, save it as DBA
SELECT
BKARHINV.BKAR_INV_CUSCOD,
BKARHINV.BKAR_INV_CUSNME,
Year(BKAR_INV_INVDTE) AS SYEAR,
IF(Year(BKAR_INV_INVDTE)= 2008,BKAR_INV_SUBTOT,'0') "T2008",
IF(Year(BKAR_INV_INVDTE)= 2007,BKAR_INV_SUBTOT,'0') "T2007",
IF(Year(BKAR_INV_INVDTE)= 2006,BKAR_INV_SUBTOT,'0') "T2006",
IF(Year(BKAR_INV_INVDTE)= 2005,BKAR_INV_SUBTOT,'0') "T2005",
IF(Year(BKAR_INV_INVDTE)= 2004,BKAR_INV_SUBTOT,'0') "T2004",
IF(Year(BKAR_INV_INVDTE)= 2003,BKAR_INV_SUBTOT,'0') "T2003"
FROM BKARHINV
ORDER BY BKAR_INV_CUSCOD

Create a Regular query (Query1) and enter the following sql:

SELECT DBA.* INTO Temp
FROM DBA;

Create a Regular query (Query2) and enter the followin sql:

SELECT
Temp.BKAR_INV_CUSCOD,
Temp.BKAR_INV_CUSNME,
Sum(Temp.T2008) AS 2008,
Sum(Temp.T2007) AS 2007,
Sum(Temp.T2006) AS 2006,
Sum(Temp.T2005) AS 2005,
Sum(Temp.T2004) AS 2004,
Sum(Temp.T2003) AS 2003
INTO MAIN
FROM Temp
GROUP BY Temp.BKAR_INV_CUSCOD, Temp.BKAR_INV_CUSNME;

Open/Run Query1, then Query2

You will have a table called "MAIN"

:P

Title: Re: Is there 5 years history anywhere
Post by martel-carol on 10/24/08 at 11:57:47

Thanks - But I'm back onto another project. Will probably get to try Access next week. Will let you know if I have trouble.