RecordSource from 2 tables (1 Viewer)

FuzMic

DataBase Tinker

Local time

Today, 17:30

Joined

Sep 13, 2006

Messages

719

• Oct 31, 2018

• #1

Hi Folks

A quickie i hope . I have 2 tables eg Tbl1 & Tbl2, both having a similar / same LinkIDFld. I would like to UNION them to be the recordsource of FormTarget. How should the recordsource string be?

I have a few scattered fields in this FormTarget that is from another Table eg tbl3. How then should the ControlSource since the recordSource in this form does not include Tbl3; ... or unless i too UNION to Tbl1 & Tb2. However my mental block is Tbl3 does not have a field LinkIDFld.

That's it,

 

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 19:30

Joined

Jan 20, 2009

Messages

12,851

• Oct 31, 2018

• #2

A UNION query only uses the field names from the first subquery. The original field names in the subsequent subqueries are ignored.

BTW Best use UNION ALL. This saves time by not checking for duplicates which is done with a plain UNION.

 

FuzMic

DataBase Tinker

Local time

Today, 17:30

Joined

Sep 13, 2006

Messages

719

• Oct 31, 2018

• #3

How to UNION ALL? How does it go with JOINS?
BTW does a UNION whatever allows select of fields, i think it do, right?

Friend a bit more singing from Down Under! Thanks always.

PS: If you are busy, i just look up at W3School. Cheerio!

 

Last edited: Oct 31, 2018

June7

AWF VIP

Local time

Today, 01:30

Joined

Mar 9, 2014

Messages

5,466

• Oct 31, 2018

• #4

Something like:

SELECT fieldnames, "T1" AS Source FROM table1
UNION ALL SELECT fieldnames, "T2" FROM table2;

You can build another query that joins the UNION query to another table. Or each SELECT line in the UNION can include a JOIN with another table.

Either way, the resulting query will not be editable. So why do you want to do this on a form?

 

FuzMic

DataBase Tinker

Local time

Today, 17:30

Joined

Sep 13, 2006

Messages

719

• Nov 1, 2018

• #5

June Thanks

I thought i got the it spot on but i was wrong. I have fair experience to use JOIN (INNER & OUTER) and that is editable.

In my UNION ALL query this message pops up "Union number of columns don't match". Is this a condition?

And again, if the UNION ALL is a recordsource of a form, can the fields in the form editable?

 

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 19:30

Joined

Jan 20, 2009

Messages

12,851

• Nov 1, 2018

• #6

The number of columns (fields) and the datatype of the corresponding columns must match.

Union is not editable.

Sounds like you need to change the table structure to get both into one table.

 

FuzMic

DataBase Tinker

Local time

Today, 17:30

Joined

Sep 13, 2006

Messages

719

• Nov 1, 2018

• #7

Got you mate