query help
query help
(OP)
I'm new to the MAS 90 data structure and use access to look at the data. Does any one have a simple query that summarizes the GL? I would like to extract a useful datacube to a MS SQL server and use a report writer. I am having trouble understanding where and how the data is stored and linked together. Are there any good resources?
RE: query help
Dawn
Visit Sage's Online Community mmunity.sa gemas.com/ sagemas/?c ategory.id =SageMAS90 and200Disc
http://co
RE: query help
RE: query help
RE: query help
Thanks in advance for your help.
RE: query help
SELECT
"GL_FinancialReportHeaderWrk"."ReportTitle",
"GL_FinancialReportHeaderWrk"."ReportTitle2",
"GL_FinancialReportHeaderWrk"."ReportTitle3",
"GL_FinancialReportHeaderWrk"."ReportTitle4",
"GL_FinancialReportDetailWrk"."AccountDesc",
"GL_FinancialReportDetailWrk"."ValueColumn01",
"GL_FinancialReportDetailWrk"."InNetIncomeBeforeTax",
"GL_FinancialReportDetailWrk"."InNetIncomeFromOp",
"GL_FinancialReportDetailWrk"."InGrossProfit",
"GL_FinancialReportDetailWrk"."AccountGroup",
"GL_FinancialReportDetailWrk"."ReportSequenceNo",
"GL_FinancialReportDetailWrk"."PrintGroup",
"GL_FinancialReportDetailWrk"."ReportPrintSequence",
"GL_FinancialReportDetailWrk"."Account",
"GL_FinancialReportHeaderWrk"."PrintAccountNo",
"GL_FinancialReportDetailWrk"."AccountGroupDesc",
"GL_FinancialReportHeaderWrk"."FormatColumn02",
"GL_FinancialReportHeaderWrk"."DollarSignType",
"GL_FinancialReportHeaderWrk"."PercentSymbol",
"GL_FinancialReportHeaderWrk"."NegativeValueFormat",
"GL_FinancialReportHeaderWrk"."DecimalSeparator",
"GL_FinancialReportHeaderWrk"."ThousandSeparator",
"GL_FinancialReportHeaderWrk"."FormatColumn01",
"GL_FinancialReportDetailWrk"."MainAccountCode",
"GL_FinancialReportHeaderWrk"."SummaryType",
"GL_FinancialReportDetailWrk"."MainAccountDesc",
"GL_FinancialReportDetailWrk"."ValueColumn02",
"GL_FinancialReportHeaderWrk"."FormatColumn03",
"GL_FinancialReportHeaderWrk"."FormatColumn04",
"GL_FinancialReportHeaderWrk"."CenterFootnote",
"GL_FinancialReportHeaderWrk"."HeadingColumn01",
"GL_FinancialReportHeaderWrk"."HeadingColumn02",
"GL_FinancialReportHeaderWrk"."HeadingColumn03",
"GL_FinancialReportHeaderWrk"."HeadingColumn04",
"GL_FinancialReportHeaderWrk"."Heading2Column01",
"GL_FinancialReportHeaderWrk"."Heading2Column02",
"GL_FinancialReportHeaderWrk"."Heading2Column03",
"GL_FinancialReportHeaderWrk"."Heading2Column04",
"GL_FinancialReportHeaderWrk"."Column03Calculated",
"GL_FinancialReportDetailWrk"."ValueColumn03",
"GL_FinancialReportHeaderWrk"."FootnoteOption",
"GL_FinancialReportHeaderWrk"."FirstAmountOnly",
"GL_FinancialReportHeaderWrk"."FootnoteText"
FROM
"GL_FinancialReportDetailWrk" "GL_FinancialReportDetailWrk",
"GL_FinancialReportHeaderWrk" "GL_FinancialReportHeaderWrk"
WHERE
("GL_FinancialReportDetailWrk"."ReportSequenceNo"="GL_FinancialReportHeaderWrk"."ReportSequenceNo") AND "GL_FinancialReportDetailWrk"."ReportSequenceNo"='00000000000002'
RE: query help
For financial reports, you should use FRx.
Dawn
Visit Sage's Online Community mmunity.sa gemas.com/ sagemas/?c ategory.id =SageMAS90 and200Disc
http://co
RE: query help
RE: query help
1. You should be posting on the Sage Community. See the link in Dawn's post. It is free and open to the public. Sage employees post as well as any number of end users and reseller.
2. Add the databases you mentioned are fine databases but they are not an integrated accounting system. Once you have taken classes and learned the MAS data structure it makes a lot more sense and is pretty easy to figure out.
3. You can actually extract the data from the works table. It's explained over on the Sage Community. When the report is run another table is created with a time stamp. To extract the data you first preview the report then go find the GL_FinancialReportDetailWrk and rename it for later, then find the GL_FinancialReportDetailWrk with the time stamp. It will look something like this: GL_DailyTransactionRegisterWrkBRL011311372385.M4T
Delete the BRL011311372385 part. You can now extract the data using ODBC. When finished delete the works take with data and rename the empty one to prevent printing problems (you always want to start with an empty works table.)
4. See you over on the Sage Community.