Board index » Visual Studio » how to create a set with combined tables?

Visual Studio51
Hi All,



I have a database with different tables. Each table has a different

amount of fields.

I want to create Sets in which I save data from different tables.

So instead of saving a Query which saves the SQL I want to save the

actual records.

for example:



I have two tables: Biographies and Events.

I want to save some of both in a Set. Since the fields in tables are

not identical I cannot

create a new table and store the data in this table.



How can I save the combined data of these two tables in one Set?



Regards



Marco


-

"vonClausowitz" <vonclausowitz@gmail.com>wrote in message

When it comes to an implementation you need to be a bit clearer of what you

mean by a "Set". Also it isn't clear why you think you can't save the return

in a new table. (I can guess, but would probably be wrong.)



Here is one guess...

For example you could easily store diverse 'records' from two tables that

are related by simply creating a cross-reference table...

' TableOne

t1PKey Long

t1Value Long

t1Desc Text



' TableTwo

t2PKey Long

t2Date Date

t2Option Boolean



' TableOneTwo_xrf

xSet Long

t2PK Long

t2FK Long



hth

-ralph









-

Ralph,



That would mean that you save the link to the original table I guess.

But I want to save the complete record because I want to save data from

the database

as it was at that specific moment. In one month the records can be

changed a lot.



Am I right with a cross ref table?



Marco



-

"vonClausowitz" <vonclausowitz@gmail.com>wrote in message



Again, can you please define what you mean by "Set"?





-

Ok,



I will try but I'm not a native speaker.



I have a table with some records(persons):



lastname firstname gender hair length

Johnson John Male brown 1.80

Franklin Peter Male black 1.75



And another table with some records (organisations):



name origin type

IRS USA Political

FGD CDN Civil Rights

DDQ RUS Political



Now I want to save for example Johnson from the persons table and

the FGD from the organisations table to a set, so the set will have the

actual records:



My Set1:

Johnson John Male brown 1.80

FGD CDN Civil Rights



Marco



-

"vonClausowitz" <vonclausowitz@gmail.com>wrote in message



And this represents two rows in one table?



You appear to know what a table is, can you define your "set" in terms of

tables, rows, and columns?





-

Sure,



Table Persons:

Fields: lastname, firstname, gender, hair, length



1. Johnson, John, Male, brown, 1.80

2. Franklin, Peter, Male, black, 1.75



Table Organisations:

Fields: name, origin, type



1. IRS, USA, Political

2. FGD, CDN, Civil Rights

3. DDQ, RUS, Political



For example record 1 of Table Persons and record 3 of Table

Organisations:



1. Johnson, John, Male, brown, 1.80

3. DDQ, RUS, Political



Marco



-

"vonClausowitz" <vonclausowitz@gmail.com>wrote in message



But what kind of structure do you want the two records to be stored in? What

you show above has one row with five columns and a second row with three

columns. You can't have a varying number of columns in a table, so you don't

appear to be describing a table. That's what I'm asking: please define the

STRUCTURE of the OUTPUT you're looking for. I understand the input.





-

"Jeff Johnson" <i.get@enough.spam>wrote



It was pretty clear that he wanted an accumulation of all the data from

both records to be saved as a record in another table:



"But I want to save the complete record because I want to save data from

the database as it was at that specific moment."



HTH

LFS





-

That's right Larry but I don't think that' possible,

so that leaves me with making Sets which consist only of one kind of

Table and

not a mix of Persons and Organisations.



MArco



-

"vonClausowitz" <vonclausowitz@gmail.com>wrote

I don't see why not. After you have the new table created with all

the appropreate fields defined, a carefully constructed INSERT INTO

statement should do the work you want done...



See INSERT INTO in VB Help....



LFS





-

"vonClausowitz" <vonclausowitz@gmail.com>wrote in message

i still dont understand fully what you want

in your example you have 2 records in Persons

and 3 records in Organisations

do you want a new table that has 6 records in it ? (6 'sets')

if so,the following will create a new table called 'test' with those 6

records in it



SELECT Organisations.*, Persons.* INTO test

FROM Organisations, Persons





-

I tried your example and end up with a table with 144217 records.



Can't I make a table with all the fields from Organisations and all the

fields from

Persons and then add the data depending from which table the data is

coming?



Marco



-

"vonClausowitz" <vonclausowitz@gmail.com>wrote in message



My problem is that I can't figure out how you're connecting the data. In the

two sample tables you provided, Persons and Organizations, there is nothing

that connects a person to an organization, so I have no idea how you're

pulling these two together. Can you post a more complete example of the

input tables and then show us, from the sample data in those input tables,

EXACTLY how you'd LIKE the output table to look?





-

Ok look it is simple,



Sometimes you run a query on a database like:



SELECT * FROM MyTable WHERE MyField = ' & MyValue & '



and then another one:



SELECT * FROM MyOtherTable WHERE MyField = ' & MyOtherValue & '



The queries can be saved for later use.

But sometimes you don't want to save the query string but the records

it produces.



That is what I'm trying to do.

The problem is that I want to put the results of these both queries in

one table (And I call this a Set, could be any name...).



Regards

Marco



-

"vonClausowitz" <vonclausowitz@gmail.com>wrote in message

Try

Insert Into newtablename select ...





--

Reply to the group so all can participate

VB.Net: "Fool me once..."



-

"Bob Butler" <tiredofit@nospam.ever>wrote in message

shouldn't post so early... "insert into" is good for adding to a table but

to create a new table use

select fieldlist into newtable from....



--

Reply to the group so all can participate

VB.Net: "Fool me once..."



-

"Bob Butler" <tiredofit@nospam.ever>wrote in message



BUT!



Does this create a *SET*?

"Set-only" solutions are accepted.



Perhaps renaming "newtable" to "newset" would meet the requirement.



-ralph





-

"Bob Butler" <tiredofit@nospam.ever>wrote



I already mentioned that 4 days ago. I wonder if he tried it?



???

LFS





-

I did,



but I ended up with a table containing some 144000 records.



SELECT Organisations.name, Biographies.family_name, first_name INTO

test

FROM Organisations, Biographies;



Every single record in Organisations was added so many times as there

were

records in the Biographies table.....



I don't think that is what I want.



Marco



-

"vonClausowitz" <vonclausowitz@gmail.com>wrote

What did you want? I didn't follow the whole thread, and the last I saw

you wanted to combine one record in one tabe with another record in another

table and save all of that data to a new table. But no mention was made on

the selection requirements. How was it you picked the first and third

records from those tables?



I have a feeling a better constructed SQL statement is in order....



LFS





-

"Larry Serflaten" <serflaten@usinternet.com>wrote in message



THIS is the question! And it's the question he STILL hasn't answered!





-

"Jeff Johnson" <i.get@enough.spam>wrote in message



My interpretations is that he wants one table, where each row in the table

may have its own distinct columns, independant of any other rows. And thus

the answer to his question is no, the columns are defined for the table as

an entity, not for each row, and thus you cannot store two bits of

information of a differing structure within the same table.



Solution: - Create a backup database, and then mirror the tables that you

wish to store.







-

"Jeff Johnson" <i.get@enough.spam>wrote in message



Egads! How much simplier can he state it?



The OP wants the SET of two SELECTs from two tables that have no fields in

common. He further notifies us that the Cartisian Product that inevitably

results from such a Select IS NOT ACCEPTABLE.



The OP is obviously looking for a non-Cartisian solution based on warp

matrix engineering. However, I don't believe the TPLD (Tachyon Probablity

Language Definition) will be available until invented by Jordi LaForge in

the 24th century.



Therefore the OP is advised to go the next best route and seek advice in

newsgroups with "dotnet" in their title.



-ralph









-