Welcome, Guest. Please Login.
11/17/24 at 10:38:18
News:
Home Help Search Login


Pages: 1
Send Topic Print
Is there 5 years history anywhere (Read 636 times)
martel-carol
Member
**




Posts: 75
Is there 5 years history anywhere
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?
Back to top
 
 
  IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 785
Gender: male
Re: Is there 5 years history anywhere
Reply #1 - 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
 
 
 Tongue
 
Kelloggs
Back to top
 
 

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)
Email WWW   IP Logged
martel-carol
Member
**




Posts: 75
Re: Is there 5 years history anywhere
Reply #2 - 10/21/08 at 11:12:48
 
Yes, I have Access
Back to top
 
 
  IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 785
Gender: male
Re: Is there 5 years history anywhere
Reply #3 - 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"
 
 
 Tongue
Back to top
 
 

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)
Email WWW   IP Logged
martel-carol
Member
**




Posts: 75
Re: Is there 5 years history anywhere
Reply #4 - 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.
Back to top
 
 
  IP Logged
Pages: 1
Send Topic Print