Board index Database Programming

Combining Tables (ie. Virtual Table)

Combining Tables (ie. Virtual Table)

Post by Kevin Blac » Sun, 31 Dec 1899 09:00:00



Hi,

I know it's relatively easy to filter a dataset, but I want to do the
opposite.  I have several (paradox) tables that are identical in structure.
I want my user to see these tables as a single table and to use the DB
navigatior to wander through what appears to them to be a single table.  The
primary key is a date/time field and this is unique through all tables.

I can easily combine tables if there is a Master/Detail relationship, but
this is not the case.  Is there a simple way I can create a virtual table
from several 'real' tables??????


posting to this newwsgroup.

Thanks,
Kevin
----------------------------------------------------------------------------
----------
Name:                  Kevin Black
Company:           MEGAbit Systems Pty Ltd
Address:             17 Forsythe Pde, BLACK HILL  2322, Australia
Tel/Fax:              +61 2 49663346

----------------------------------------------------------------------------
----------

 
 
 
Top

Combining Tables (ie. Virtual Table)

Post by Yury V. Kamsh » Sun, 31 Dec 1899 09:00:00


Hi!


>   I know it's relatively easy to filter a dataset, but I want to do the
>   opposite.  I have several (paradox) tables that are identical in structure.
>   I want my user to see these tables as a single table and to use the DB
>   navigatior to wander through what appears to them to be a single table.  The
>   primary key is a date/time field and this is unique through all tables.

>   I can easily combine tables if there is a Master/Detail relationship, but
>   this is not the case.  Is there a simple way I can create a virtual table
>   from several 'real' tables??????

There is 3 possible ways I can think of:
 1) You can create another table, insert all data from your tables into it
and link DataSource and DBNavigator to it.
 2) You can use DBNavigator's BeforeAction event to check whether current
record is first/last for current DataSet (if Prev/Next button is pressed)
or whether current DataSet is first/last (if First/Last button is pressed)
and change DataSource of DbNavigator (or DataSet of DataSource)
programmatically.
 3) You can write your own TTable or rather TDataSet with multiple TableName
properties, which will combine this tables. Although, this way is not so easy.

Hope this helps.

Best wishes,
---
Yury V. Kamsha

You can't win, you can't even lose, you even can not quit the game.

 
 
 
Top

Combining Tables (ie. Virtual Table)

Post by Bruce Swar » Sun, 31 Dec 1899 09:00:00


I would use a TQuery

Regards


> Hi,

> I know it's relatively easy to filter a dataset, but I want to do the
> opposite.  I have several (paradox) tables that are identical in structure.
> I want my user to see these tables as a single table and to use the DB
> navigatior to wander through what appears to them to be a single table.  The
> primary key is a date/time field and this is unique through all tables.

> I can easily combine tables if there is a Master/Detail relationship, but
> this is not the case.  Is there a simple way I can create a virtual table
> from several 'real' tables??????


> posting to this newwsgroup.

> Thanks,
> Kevin
> ----------------------------------------------------------------------------
> ----------
> Name:                  Kevin Black
> Company:           MEGAbit Systems Pty Ltd
> Address:             17 Forsythe Pde, BLACK HILL  2322, Australia
> Tel/Fax:              +61 2 49663346

> ----------------------------------------------------------------------------
> ----------

--
 
 
 
Top

Combining Tables (ie. Virtual Table)

Post by Marek Bis » Sun, 31 Dec 1899 09:00:00



>Hi,

>I know it's relatively easy to filter a dataset, but I want to do the
>opposite.  I have several (paradox) tables that are identical in structure.
>I want my user to see these tables as a single table and to use the DB
>navigatior to wander through what appears to them to be a single table.
The
>primary key is a date/time field and this is unique through all tables.

>I can easily combine tables if there is a Master/Detail relationship, but
>this is not the case.  Is there a simple way I can create a virtual table
>from several 'real' tables??????

IMHO it's wrong aproach.
Consider making one real table and acces it through several queries
returning subsets of records (using additional field "SubsetNo" for
instance).
 
 
 
Top

Combining Tables (ie. Virtual Table)

Post by Shaun McArthu » Sun, 31 Dec 1899 09:00:00


I agree, one table, a TQuery and the dynamic joins.

Shaun



>>Hi,

>>I know it's relatively easy to filter a dataset, but I want to do the
>>opposite.  I have several (paradox) tables that are identical in
structure.
>>I want my user to see these tables as a single table and to use the DB
>>navigatior to wander through what appears to them to be a single table.
>The
>>primary key is a date/time field and this is unique through all tables.

>>I can easily combine tables if there is a Master/Detail relationship, but
>>this is not the case.  Is there a simple way I can create a virtual table
>>from several 'real' tables??????

>IMHO it's wrong aproach.
>Consider making one real table and acces it through several queries
>returning subsets of records (using additional field "SubsetNo" for
>instance).

 
 
 
Top

Combining Tables (ie. Virtual Table)

Post by Robert Kapla » Sun, 31 Dec 1899 09:00:00



>I agree, one table, a TQuery and the dynamic joins.

>Shaun

Just curious, if you have the same field names in multiple tables, would you
get a combined table if you select the same fields off the multiple tables?
And why does he need joins? Would something like

SELECT T1.FIELD1, T1.FIELD2, T2.FIELD1, T2.FIELD2 FROM  T1,  T2 be enough to
create the combined result set?

Robert



>>>Hi,

>>>I know it's relatively easy to filter a dataset, but I want to do the
>>>opposite.  I have several (paradox) tables that are identical in
>structure.
>>>I want my user to see these tables as a single table and to use the DB
>>>navigatior to wander through what appears to them to be a single table.
>>The
>>>primary key is a date/time field and this is unique through all tables.

>>>I can easily combine tables if there is a Master/Detail relationship, but
>>>this is not the case.  Is there a simple way I can create a virtual table
>>>from several 'real' tables??????

>>IMHO it's wrong aproach.
>>Consider making one real table and acces it through several queries
>>returning subsets of records (using additional field "SubsetNo" for
>>instance).

 
 
 
Top

Combining Tables (ie. Virtual Table)

Post by Gwen » Sun, 31 Dec 1899 09:00:00


Consider using a UNION statement in your TQuery SQL.



>>Hi,

>>I know it's relatively easy to filter a dataset, but I want to do the
>>opposite.  I have several (paradox) tables that are identical in
structure.
>>I want my user to see these tables as a single table and to use the DB
>>navigatior to wander through what appears to them to be a single table.
>The
>>primary key is a date/time field and this is unique through all tables.

>>I can easily combine tables if there is a Master/Detail relationship, but
>>this is not the case.  Is there a simple way I can create a virtual table
>>from several 'real' tables??????

 
 
 
Top

1. combining data into one table from multiple tables

Hello fellows,
I would appreciate if somebody can help me out. I am very new to SQL and
seems that can not find solution to my problem:

I have a single database with say three tables:

Table1:

Table1ID (int) , Description (varchar) - contains two letter codes space
separated, different number of codes for each row (row1: AB CD, row2: CD EF
GH)..., and other columns not relevant

Table2:

Table2ID (int) - two digit ids (10, 20, 30) which will represent those
letter codes from above table1 (10=AB, etc.). However there are no
relationship in a database yet, I just KNOW what those digit codes suppose
to match from letter codes
Description (varchar) - just a description

 I need to create Table3 which will contain keys (Table1ID, Table2ID), where
I need to combine those tableids according that information that I have,
something like:

IF
Table1
Table1ID: 100 , Desc: AB, BC

AND
Table2
Table2ID: 10 ( and I know it means AB), Desc: area01
Table2ID: 20 (BC), Desc: area02

THEN
Importing into Table3:
Row1: 100, 10
Row2: 100, 20

So far I was able only to find a way to separate description codes by two in
each column (one by one)  in temp table such us:

SELECT Table1ID,
 [description info],
 substring([description info],1,2) as test1,
 substring([description info],4,2) as test2,
 substring([description info],7,2) as test3,
 substring([description info],10,2) as test4,
 substring([description info],13,2) as test5,
 substring([description info],16,2) as test6,
 substring([description info],19,2) as test7,
 substring([description info],22,2) as test8,
 substring([description info],25,2) as test9,
 substring([description info],28,2) as test10,
 substring([description info],31,2) as test11,
 substring([description info],34,2) as test12,
 substring([description info],37,2) as test13

into #mytemp
from Table1



SELECT*
into #mytemp2
from #mytemp where












And from here I stuck. I know that I am probably suppose to use INSERT
statement, but all my tests failed so far. Please advise how I can proceed
next, if it possible with a code example. Thank you.

BTW, I am working with MSDE 2000 (winXP) and SQL 2000 enterprise manager.

George

2. Accessing DB2 on OS/390 via MS Access

3. How to convert an Universe Hashed data file to be an ASCII flat file?

4. combining data into one table from multiple tables - please disregard a previous one, wrong email

5. putting bitmaps INTO a dbase blob field?

6. How do I tell if an existing informix table is a virtual table

7. Sql Server 2000 Cluster Question - Virtual Names

8. Pivot Table on IE

9. how to print pivot table in IE

10. SQL Problem - combine flat file and table rows

11. combining same field from 2 tables

12. combining two tables


All times are UTC
Board index