Crystal table joins - General Ledger tables

The tables would be joined by the company, Chart, Division and
Department for each of the tables.



The GLOPNBAL will have a record if there was an opening balance at the
beginning of the year. These will only be Balance Sheet accounts because
there is never an opening balance for Income/Expense accounts.



My suggestion for putting a structure to it would be to LEFT JOIN both
files to the Chart of Accounts. Group the report by account and if
there is an opening balance display it. From that point Print the
details for the periods to display the balances.





Charlie Smith

Smith Business Services / 2W Technologies LLC

www.vistaconsultant.com <http://www.vistaconsultant.com/> /
www.2WTech.com







From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of debsmithmich
Sent: Tuesday, September 11, 2007 5:10 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Crystal table joins - General Ledger tables



I'm also trying to assist Babette with this issue. I have tried it with
no selection criteria. I've also tried to use selection criteria using
company = 'value' in each of the tables. Has anyone tried to join these
tables?

GLPerBal is chart information for each month for each year. GLOpnBal is
information containing yearly open balances for each chart. Babette has
explained these tables below.

It sure would be great if someone could help us out.

Thanks,

Debbie Smith

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Brian Stenglein" <bstenglein@...>
wrote:
>
> Do you have any selection criteria in the report?
>
> Brian Stenglein
> Systems Analyst
> Clow Stamping Co.
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Babette Welch" <bwelch@> wrote:
> >
> > To all the Crystal masters out there...
> > Maybe you can solve this.....
> >
> >
> > I have 3 tables to join.
> > Tab-1 - has one records for each item
> > Tab-2 - has one record for some items of Tab-1 but not all. Some
> items
> > from Tab-1 are not in this table.
> > Tab-3 - has many items for some items of Tab-1 but not all. Some
> items
> > from Tab-1 are not in this table.
> >
> > I set the Left-Outer join from Tab-1 to Tab-2 and
> > another Left-Outer join from Tab-1 to Tab-3
> >
> > Tried, version of "enforced" and No matter what I do,
> > Tab-1 items will not show unless there are records in Tab-2 AND
Tab-
> 3
> > !!!
> >
> > Any ideas?
> > The tables are GLacct - one records every chart-department
> > GLOpnBal - opening balance for each year for the Balance Sheet
> accounts
> > only!
> > GLPerBal - one record for each account with amount of the Debits
and
> > Credits for the account for that fiscal period.
> > Not all accounts have activity all months.
> >
> >
> > All assistance is appreciated.
> >
> >
> > Babette Welch
> > IT Director
> > bwelch@
> > (906)226-9747 ext: 235
> > ARGONICS, INC.
> > Performance Polyurethanes
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>





[Non-text portions of this message have been removed]
To all the Crystal masters out there...
Maybe you can solve this.....


I have 3 tables to join.
Tab-1 - has one records for each item
Tab-2 - has one record for some items of Tab-1 but not all. Some items
from Tab-1 are not in this table.
Tab-3 - has many items for some items of Tab-1 but not all. Some items
from Tab-1 are not in this table.

I set the Left-Outer join from Tab-1 to Tab-2 and
another Left-Outer join from Tab-1 to Tab-3

Tried, version of "enforced" and No matter what I do,
Tab-1 items will not show unless there are records in Tab-2 AND Tab-3
!!!

Any ideas?
The tables are GLacct - one records every chart-department
GLOpnBal - opening balance for each year for the Balance Sheet accounts
only!
GLPerBal - one record for each account with amount of the Debits and
Credits for the account for that fiscal period.
Not all accounts have activity all months.


All assistance is appreciated.


Babette Welch
IT Director
bwelch@...
(906)226-9747 ext: 235
ARGONICS, INC.
Performance Polyurethanes



[Non-text portions of this message have been removed]
Do you have any selection criteria in the report?

Brian Stenglein
Systems Analyst
Clow Stamping Co.

--- In vantage@yahoogroups.com, "Babette Welch" <bwelch@...> wrote:
>
> To all the Crystal masters out there...
> Maybe you can solve this.....
>
>
> I have 3 tables to join.
> Tab-1 - has one records for each item
> Tab-2 - has one record for some items of Tab-1 but not all. Some
items
> from Tab-1 are not in this table.
> Tab-3 - has many items for some items of Tab-1 but not all. Some
items
> from Tab-1 are not in this table.
>
> I set the Left-Outer join from Tab-1 to Tab-2 and
> another Left-Outer join from Tab-1 to Tab-3
>
> Tried, version of "enforced" and No matter what I do,
> Tab-1 items will not show unless there are records in Tab-2 AND Tab-
3
> !!!
>
> Any ideas?
> The tables are GLacct - one records every chart-department
> GLOpnBal - opening balance for each year for the Balance Sheet
accounts
> only!
> GLPerBal - one record for each account with amount of the Debits and
> Credits for the account for that fiscal period.
> Not all accounts have activity all months.
>
>
> All assistance is appreciated.
>
>
> Babette Welch
> IT Director
> bwelch@...
> (906)226-9747 ext: 235
> ARGONICS, INC.
> Performance Polyurethanes
>
>
>
> [Non-text portions of this message have been removed]
>
An outer join must be done in a query with only two tables in use.



You may want to try a UNION of two separate queries. The two separate
queries are tab1 left outer join tab2 -and- tab1 left outer join tab3.



The columns being returned from each must match.



John A. Hatcher

Manager of IS

Versa Products Co., Inc.

(201) 518-5948

(201) 843-2400 x4148

(201) 843-2931 (fax)





[Non-text portions of this message have been removed]
I'm also trying to assist Babette with this issue. I have tried it
with no selection criteria. I've also tried to use selection
criteria using company = 'value' in each of the tables. Has anyone
tried to join these tables?

GLPerBal is chart information for each month for each year. GLOpnBal
is information containing yearly open balances for each chart.
Babette has explained these tables below.

It sure would be great if someone could help us out.

Thanks,

Debbie Smith

--- In vantage@yahoogroups.com, "Brian Stenglein" <bstenglein@...>
wrote:
>
> Do you have any selection criteria in the report?
>
> Brian Stenglein
> Systems Analyst
> Clow Stamping Co.
>
> --- In vantage@yahoogroups.com, "Babette Welch" <bwelch@> wrote:
> >
> > To all the Crystal masters out there...
> > Maybe you can solve this.....
> >
> >
> > I have 3 tables to join.
> > Tab-1 - has one records for each item
> > Tab-2 - has one record for some items of Tab-1 but not all. Some
> items
> > from Tab-1 are not in this table.
> > Tab-3 - has many items for some items of Tab-1 but not all. Some
> items
> > from Tab-1 are not in this table.
> >
> > I set the Left-Outer join from Tab-1 to Tab-2 and
> > another Left-Outer join from Tab-1 to Tab-3
> >
> > Tried, version of "enforced" and No matter what I do,
> > Tab-1 items will not show unless there are records in Tab-2 AND
Tab-
> 3
> > !!!
> >
> > Any ideas?
> > The tables are GLacct - one records every chart-department
> > GLOpnBal - opening balance for each year for the Balance Sheet
> accounts
> > only!
> > GLPerBal - one record for each account with amount of the Debits
and
> > Credits for the account for that fiscal period.
> > Not all accounts have activity all months.
> >
> >
> > All assistance is appreciated.
> >
> >
> > Babette Welch
> > IT Director
> > bwelch@
> > (906)226-9747 ext: 235
> > ARGONICS, INC.
> > Performance Polyurethanes
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>