Hi all,
My requirement is to graph month1,month2,month3,quarter1, quarter2,year in X access, I developed my according to the bellow to capture individual values and appending them, but somehow these are not appending, I’m getting last table file result instead of all 3 in columns,
Please advise me to full fill my requirement!

 FILEDEF COMP_RPT DISK COMP_RPT (APPEND
TABLE FILE CAR
SUM
CNT.DST.BODYTYPE AS 'OCT'
BY COUNTRY NOPRINT
WHERE DEALER_COST GE '2000' AND DEALER_COST LE '4000'
ON TABLE HOLD AS COMP_RPT FORMAT ALPHA
END
TABLE FILE CAR
SUM
CNT.DST.BODYTYPE AS 'NOV'
BY COUNTRY NOPRINT
WHERE DEALER_COST GE '5000' AND DEALER_COST LE '7000'
ON TABLE HOLD AS COMP_RPT FORMAT ALPHA
END
TABLE FILE CAR
SUM
CNT.DST.BODYTYPE AS 'NOV'
BY COUNTRY NOPRINT
WHERE DEALER_COST GE '8000' AND DEALER_COST LE '10000'
ON TABLE HOLD AS COMP_RPT FORMAT ALPHA
END
TABLE FILE COMP_RPT
PRINT *
END
-EXIT 

Thanks
Krish

This message has been edited. Last edited by: Krishna.edara,

You cannot concatenate hold files with different fields - each hold file must have the same number of fields and format.

This should work:

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY

FILEDEF COMP_RPT DISK COMP_RPT (APPEND
TABLE FILE CAR
SUM
COMPUTE FI/I1 = 1;
CNT.DST.BODYTYPE/D10 AS 'OCT'
COMPUTE NOV/D10 = 0;
BY COUNTRY NOPRINT
WHERE DEALER_COST GE '2000' AND DEALER_COST LE '4000'
ON TABLE HOLD AS COMP_RPT FORMAT ALPHA
END
TABLE FILE CAR
SUM
COMPUTE FI/I1 = 2;
COMPUTE OCT/D10 = 0;
CNT.DST.BODYTYPE/D10 AS 'NOV'
BY COUNTRY NOPRINT
WHERE DEALER_COST GE '5000' AND DEALER_COST LE '7000'
ON TABLE HOLD AS COMP_RPT FORMAT ALPHA
END
TABLE FILE CAR
SUM
COMPUTE FI/I1 = 3;
COMPUTE OCT/D10 = 0;
CNT.DST.BODYTYPE/D10 AS 'NOV'
BY COUNTRY NOPRINT
WHERE DEALER_COST GE '8000' AND DEALER_COST LE '10000'
ON TABLE HOLD AS COMP_RPT FORMAT ALPHA
END
TABLE FILE COMP_RPT
PRINT *
END

You need ASNAMES=ON to ensure that the field will be named OCT or NOV...

I added field FI to prove that data comes from three different hold commands.

Hi Francis,
Thank you for your reply,
BEGIN_DATE2 is doing great but BEGIN_DATE3 is getting 0?001200 and BEGIN_DATE4 is empty , Please let me know where I`m going wrong.
I want to capture all the dates betwee the range i given and sum the distinct count of 'Y1' in the form of graph

 SET ASNAMES=ON
SET HOLDLIST=PRINTONLY

DEFINE FILE TRANSMIS
BEGINDATE/YYMD = SUPER_DATE;
BEGIN_DATE/A8YY|M|D = BEGINDATE ;
END

FILEDEF COMP_RPT DISK COMP_RPT.FTM (APPEND

TABLE FILE TRANSMIS
PRINT
COMPUTE FI/I1 = 1;
COMPUTE BEGIN_DATE2/A8YY|M|D = BEGIN_DATE;
Y1
WHERE SUPER_DATE GE '&M10START_DATE' AND SUPER_DATE LE '&M10END_DATE'
ON TABLE HOLD AS COMP_RPT FORMAT ALPHA
END

TABLE FILE TRANSMIS
PRINT
COMPUTE FI/I1 = 2;
COMPUTE BEGIN_DATE2/A8YY|M|D = 0;
COMPUTE BEGIN_DATE3/A8YY|M|D = BEGIN_DATE;
Y1
WHERE SUPER_DATE GE '&M11START_DATE' AND SUPER_DATE LE '&M11END_DATE'
ON TABLE HOLD AS COMP_RPT FORMAT ALPHA
END
TABLE FILE TRANSMIS
PRINT
COMPUTE FI/I1 = 3;
COMPUTE BEGIN_DATE2/A8YY|M|D = 0;
COMPUTE BEGIN_DATE3/A8YY|M|D = 0;
COMPUTE BEGIN_DATE4/A8YY|M|D = BEGIN_DATE;
Y1
WHERE SUPER_DATE GE '&M12START_DATE' AND SUPER_DATE LE '&M12END_DATE'
ON TABLE HOLD AS COMP_RPT FORMAT ALPHA
END
TABLE FILE COMP_RPT
PRINT
BEGIN_DATE2
BEGIN_DATE3
BEGIN_DATE4
Y1
WHERE RECORDLIMIT EQ 10
-*ON TABLE HOLD AS GRAPH_DATA
END
 

quote:

You cannot concatenate hold files with different fields - each hold file must have the same number of fields and format.

you need to COMPUTE BEGIN_DATE2, BEGIN_DATE3 and BEGIN_DATE4 in all HOLD files.

Getting “Error code is: 32027Could not get error message “ error message, for the following code, I don`t know where I`m going wrong,
please help me

 SET &ECHO = ALL;
SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET BYDISPLAY = ON
SET EMPTYREPORT =ON

DEFINE FILE TRANSMIS
BEGINDATE/YYMD = SUPER_SHEET_DATE;
BEGIN_DATE/A8YY|M|D = BEGINDATE ;
MONTH/M = SUPER_SHEET_DATE;
QUARTER/Q = SUPER_SHEET_DATE;
YEAR/YY = SUPER_SHEET_DATE;
DUMMY/A1 = '';
END

FILEDEF COMP_RPT DISK COMP_RPT.FTM (APPEND
TABLE FILE TRANSMIS
SUM Y1
COMPUTE FI/I1 = 1;
COMPUTE MONTH1/M = MONTH;
BY MONTH NOPRINT
BY DUMMY 
WHERE MONTH IN (10, 11,12)
WHERE YEAR EQ '2013'
ON TABLE HOLD AS COMP_RPT FORMAT ALPHA
END
TABLE FILE TRANSMIS
SUM Y1
COMPUTE FI/I1 = 2;
COMPUTE MONTH1/M = MONTH1;
COMPUTE QUARTER1/Q = QUARTER;
BY QUARTER NOPRINT
BY DUMMY
WHERE YEAR EQ '2013'
ON TABLE HOLD AS COMP_RPT FORMAT ALPHA
END
TABLE FILE TRANSMIS
SUM Y1
COMPUTE FI/I1 = 3;
COMPUTE MONTH1/M = MONTH1;
COMPUTE QUARTER1/Q = QUARTER1;
COMPUTE YEAR1/YY = YEAR;
BY YEAR  NOPRINT
BY DUMMY
WHERE YEAR EQ '2013'
ON TABLE HOLD AS COMP_RPT FORMAT ALPHA
END

TABLE FILE COMP_RPT
SUM Y1 
BY YEAR1 NOPRINT
ACROSS MONTH1 AS ''
COLUMNS MONTH1 AND  QUARTER1 AND YEAR1
END
-EXIT
 

Thanks,
Krish

Hi Krishna,

When you use FILEDEF to append the records, format and field names must be same.

I don't see QUARTER1 field in your first HOLD and NOPRINT Sort fields in every hold file are in different format & name.
Convert your MONTH,QUARTER,YEAR to ALPHA format which will then be in common across all of your HOLD files.

Here, I'm posting the sample code using CAR.See, if it helps.

TABLE FILE CAR
PRINT COUNTRY SEATS
ON TABLE HOLD AS MAIN
END
-RUN

FILEDEF F1 DISK F1.FTM
-RUN
DEFINE FILE MAIN
SEAT1/I3 = SEATS;
CNTY/A25 = COUNTRY;
END
TABLE FILE MAIN
SUM  SEAT1
BY CNTY
ON TABLE HOLD AS F1
END

FILEDEF F1 DISK F1.FTM (APPEND
-RUN

DEFINE FILE MAIN
SEAT1/I3 = SEATS;
CNTY/A25 = 'TotalSeatsWithoutENGLAND';
END
TABLE FILE MAIN
SUM SEAT1
BY CNTY
WHERE COUNTRY NE 'ENGLAND';
ON TABLE HOLD AS F1
END

TABLE FILE F1
PRINT
CNTY AS 'COUNTRY'
SEAT1 AS 'SEATS'
END
-EXIT

PS:you may have missed the hyphen in environment variable -SET &ECHO=ALL;