Concatenating Two Records

[SweetDot]

Hi,
I have two tables table1 and table2, they have identical fields, but the data is different. I want to select 1 record each from table1 and table2 using two separate criteria (criteria for table1 is different than criteria for table2). After selecting those two records, I want to be able to concatenate them together so the fields from table1 come first, followed by the fields of table2. Then, that one record will be inserted into another big table. Can anyone help me with it?

thanks

 

[mndrlion]

Create a new query. Add both tables to the query. But do not join them.

Add the fields that you want to retrieve form each table, and set the criteria as appropriate.

 

[BNPMike]

mndrlion
If you make a query with two tables but do not join them you will get a cartesian cross product.

sweetdot
It seems you want to collect pairs of records - one from table 1 and one from table two. To do this you need to join them. What worries me is you mention different criteria so I guess there is some complication. Maybe you should show some detail? Tell us in English how you want to identify which t1 record goes with which t2 record.

 

[SweetDot]

Thanks mndrlion and BNPMike...

I want to select 1 row of record from table1, by matching the the ID of the record with the ID passed in through the parameter of the query. And similarly with table2, I want to select 1 row of record from table2, but with a different ID also passed in through the parameter.

After selecting the 2 distinct records, I want all fields of table1 concatenated with all fields of table2. Then insert it into table3.

I need to repeat this process many times, so i'll need to do insert, and not select into. I'm thinking of doing a union of the two records.....select that into a temporary table, and then select from the temporary table to insert into table3. Does this make sense?

 

[mndrlion]

BNPMike wrote: If you make a query with two tables but do not join them you will get a cartesian cross product.

You are correct, but if the criteria for each table returns only a single record, then the cross product will also only be a single record. This is what SweetDot is after, I think.

 

[SweetDot]

sorry...one stupid question...
can you show me the syntax of selecting those two records and have them inserted into a table as one single record?

 

[Golom]

Try
[blue][tt]
INSERT INTO tblTarget

(Select tbl1.*, tbl2.* From tbl1, tbl2
Where ... tbl1 selection conditions ...
AND
... tbl2 selection conditions ...)
[/tt][/blue]
Assuming that "tblTarget" has fields that are the concatenation of the fields from tables "tbl1" and "tbl2" in the same order (i.e. tbl1 fields followed by tbl2 fields).

 

[mndrlion]

INSERT INTO tblConcatenated ( Letter, [Number], ID, Name )
SELECT tblAlphabet.Letter, tblAlphabet.Number, tblID.ID, tblID.Name
FROM tblAlphabet, tblID
WHERE (((tblAlphabet.Number)=3) AND ((tblID.ID)=10));

 

[SweetDot]

thanks so much everyone.