Trust ID PBCEX logo 300x102

No Key Field in dropdown box after entering SQL

12 years 10 months ago
Posts: 64
Topic Author
No Key Field in dropdown box after entering SQL #13
After entering the SQL for an internal view the next button does not become active as no primary key can be chosen. The SQL was copied directly from the MS Access Query created and the Query worked fine in MS Access.
[attachment=0:1ri2cjg2]<!-- ia0 -->Internal View.jpg<!-- ia0 -->[/attachment:1ri2cjg2]
The problem with this SQL was that Names is a reserved word by MS Access (it did warn me when I created the table) but it still worked fine in MS Access.

To solve the issue place square brackets around Names so that it is interpreted as the table name and not as a reserved word.

SELECT [Names].StudentKey, [Names].FirstName,
[Names].Surname, Studentno.[Student Card No]
FROM [Names] INNER JOIN Studentno ON
[Names].StudentKey = Studentno.StudentKey

Other Internal View Tips:
Always remove the ; at the end of copied SQL code as CardExchange puts its own in place.
The SQL of the internal view is the native SQL syntax of the originating Data Source.

Support information originally supplied by Rutger Koperdraad from ExchangeIT

Jason Hicks MBA
National Service & Projects Manager
[Practical Peripherals Corporation Pty Ltd (Australia) ]
"No we don't really eat much Kangaroo meat here, Cows are much easier to catch"