Access database Inner Joins

  DongQuyCao 21:53 22 Sep 2004
Locked

In Access I have a table of Company names which business advisors have worked with. This is a linked table to an Excel spreadsheet which in turn is an output of an external data source. I have another table in Access where I log all companies where I have received an eligibility form.

What I need is a query which would list all companies where I have not received an eligibility form. An inner join between the 2 company name fields would return the opposite ie. a list of companies that are eligible ie. where a form has been received.

Similarly, I also have a linked table containing all invoices outstanding for a particular project, and another table where I log the invoice numbers of payments made under that project. I need a query that would return all unpaid invoices.

  Gingermum 22:14 22 Sep 2004

You can use the unmatched query wizard. This works only if you have designed your tables correctly. The 2 tables should be linked by an id field. Where you have received a form, you will have an entry in the eligibility form table. So if you run the unmatched query wizard what you will have is a left outer join and the filter criteria in the elibility form table primary key is set to "Is Null" But the wizard does it all and you don't need to worry about what kind of join it is.
I am a bit worried. The overriding principle of a relational database is that no piece of information is ever stored twice. In other words you shouldn't be linking tables via comapany names. They should be joined via a primary key. You will have problems linking tables via names. The slightest typo makes them different.
Check out the Northwind database and the examples there.

  DongQuyCao 22:42 22 Sep 2004

Thanks Gingermum. I've used all the queries before apart from the "unmatched" one and always thought it would come in use one day! The company names relate to a unique company ID number so I'm not actually typing them but copying and pasting so I hope this should be OK.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

30 UK artists have created vibrant posters celebrating Pride London

iPad Pro 10.5in (2017) review

Comment connecter un MacBook à une TV ?