[AccessD] Table vs View Recordsource

[Stuart McLachlan]

On 25 Jun 2021 at 4:48, Anita Smith wrote:

> Paul,
> Interesting. I have never used the Resync or Unique table command. I
> will have to play around with that, although I think the unique table
> is only needed if the view has multiple tables. I always have the view
> return from one table only so as to avoid fiddling around with that
> and I think it may be more efficient as the sql runs on the server.
> 

AFAIK, UniqueTable is only applicable to ADPs 

> SELECT
>     CustomerID,
>     (Select CustomerType FROM CustomerTypes Where CustomerTypeID = 
>     Customers.CustomerTypeID) as CustType, CustomerName, Address
> FROM
>     Customers
> 

ISTM, that that would be sub-optimal compared to using standard relational joins along the 
lines of:

SELECT
    CustomerID,,CustomerType, CustomerName, Address
FROM
   Customers c
INNER JOIN CustomerTypes ct ON ct.CustomerTypeID = 
    c.CustomerTypeID

(personally I avoid using a suffx  such as ID for both parent and child fields.  it can cause 
problems wher you have both fields in the result set.  I prefer something like 
Table - Field
Customers - CustomerTypeFK     (foreign key)
CustomerTypes - CustomerTypePK.   (primary key)