asked on

Make access query to show full table results

Hi,
I have two tables, one is a list of hospitals, and the other table is a main table which has entries, which are produced from a simple form. So user can enter details on form and they appear in table - main table.

What i need to do is generate a query which shows ALL hospitals from the hospital table, but then also shows any entries from main table, against any of those hospitals. There will only be one entry per hospital.

Can anyone help

Cheers

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

damianb123

ASKER

Hi,
I tried the above, but it was pulling out the values by the ID on both tables, which won't work as they're not commonly linked, however what is linked are the Hospital names, so I wrote this:

SELECT [Hospital's].FIELD1, [Main Table].*
FROM [hospital's] LEFT JOIN [Main Table] ON [hospital's].FIELD1 = [Main Table].hospital's
ORDER BY [hospital's].FIELD1;

So it should (I think) select field1 - hospital name from hospital's table, and everything from main table....

Then we join the hospital's table and main table where hospital's table.field1 (hospital name) = main table hospital name....

and order by the hospital's - but it doesn't work.... I get:

Syntax Error (missing operator) in query expression

Any ideas?

SOLUTION

regmigrant

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial