Lets say you need to pull data from two or more joined tables to show on the List/View/Edit page. The data should be searchable and editable (except for the joined fields that cannot be updated).
In this example we use two tables:
Cars
ID |
Make |
Model |
UserID |
1 |
Acura |
NSX-T |
1 |
2 |
Ford |
Crown Victoria |
2 |
3 |
Volkswagen |
Passat |
1 |
4 |
Toyota |
Avalon |
2 |
5 |
Audi |
TT |
3 |
Users
ID |
Username |
1 |
Bob |
2 |
Admin |
3 |
Bill |
4 |
Tina |
Here is the default SQL query ASPRunnerPro builds for the Cars table:
select [ID],
[Make],
[Model],
[UserID],
From [Cars]
Modify it like so:
select Cars.[ID],
[Make],
[Model],
[UserID],
UserName
From [Cars]
inner join users on cars.userid=users.id
If you don't specify which table the ID field belongs to, an error message similar to this one appears:
Error message:
[Microsoft][ODBC Microsoft Access Driver] The specified field '[ID]' could refer to more than one table listed in the FROM clause of your SQL statement.
See also:
•Open Database Connectivity (ODBC)
