Join two tables

[BBK]

I have two tables as shown below

Im trying to create a join between the two tables to use in a form

I have tried a lot of code but cant seem to get it to work

My latest attempt been:

SELECT [Payment].[PaymentID], [Payment].[LeaseID], [Payment].[DateDue], [Payment].[DatePaid], [Payment].[PaymentTypeID], [Payment].[Amount], [Payment].[PaymentCategoryID], Payment.Notes FROM Payment INNER JOIN (Tenant ON Tenant.TenantID=Payment.TenantID)

( no laughing please )

Am i anywhere near the correct syntax for this join or am i as far out as a lighthouse.

Once again any help provided to lead me in the right direction would be greatly appreciated. Thank you

 

[boblarson]

Why not just use the QBE (Query by example) grid, instead of trying to write the SQL by hand? It will do just what you need when you select the tables and drag and drop the field from one table onto the field that needs to be linked on the other table. Then you can go into the SQL view and see what the syntax was.