Linking two unrelated tables

[136jooseng]

I have 2 tables:
GLPOST (posted accounting entries)
GLPEND (pending accounting entries)

These tables are not linked. However, they do have some fields that are the same (since the pending entries will ultimately become posted entries, when posting procedure is activated)

Can I put these tables in one report so that it will show all entries?
If so, how?
Can I create a link btw the tables for the common fields?


One step further, can the report be grouped by common field of these tables?

Any help appreciated.

 

[IdoMillet]

Create a VIEW/Query or use SQL Designer to create
a UNION query as the source for your report.

Something like:
-----------------------
SELECT ...
FROM GLPOST
WHERE...

UNION ALL

SELECT ...
FROM GLPEND
WHERE...
-----------------------

hth,
- Ido
ixm7@psu.edu

 

[kenhamady]

If you are in SQL you can use a union. You can't link them because linking adds the columns, and what you want is to add the rows. What is your database? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[chelseatech]

Ido and Ken are correct.

If it's a Server type Db you need to create view using a UNION join. If it's MS-Access you can do the same there.

If it's Paradox or DBF, use a MS-Access MDB with links to the source tables and build a UNION join in Access (but it's vvvveeeerrrrryyyyy sssslllloooo

http://wwwww).

Otherwise a subreport solutiuon may be needed. Gte all the data from one table in your main report and your other data in the subreport. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[136jooseng]

Hi all,

I am not a VB user. I could only write report in CR (OEM version of ACCPAC).

Can I issue command like UNION in CR. If so, how???

Thanks.

 

[Ngolem]

I think you should describe your report more...give us a sample of what you would like the report to look like and a small sample of data from each table to show how they are related...

Yes it is possible to place both tables in one report....failing that the report can be a mainreport + a subreport is the linkage is too difficult.

Show us what you want for a report and let us see what we can do...I don't think complicated SQL is necessary based on the little that you have given us.

Jim

 

[kenhamady]

You can always use a subreport to bring info from an unlinkable table, however, this prevents you from intermingling the records. You can interleave the groups by putting a subrpeort for pending records into the group header or footer of your main report, which would read the posted records. Of course, subreports are a bit slower.

YOu could try to force a union if you have a SQL based back end and you are pretty good at SQL statements. Can you use the menu commands Database - Show SQL Query in one of your ACCPAC reports?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[136jooseng]

Hi all,

Give me some time to prepare more info so that you guys have a clearer picture.

Maybe tomorrow.

Thanks,

 

[Ngolem]

Ken

The tables have common fields...hence they are linkable...no?

...and I meant that a main report + subreport would NOT be difficult (stupid fingers!)

Jim

 

[kenhamady]

Jim,

With subreports you can link on formulas, so you can even make your own common fields when necessary. But, you still can't treat the subreport records as part of the main report's recordset - and not everyone understands that. The closest you can get is matching SubReport groups with MainReport Groups. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Ngolem]

Well we are really speculating here since we don't have enough information from 136jooseng

But I am speculating that he has 2 database tables that can be linked by some sort of EntryID....since he says there are common fields . If this is so then a subreport on GLPEND (pending accounting entries) linked by that field to GLPOST (posted accounting entries) in an outter join type senario should easily be possible.

But our friend is giving us no details...until we have the table structures there is nothing we can say.

Jim