Access database Inner Joins

  DongQuyCao 21:53 22 Sep 04
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 04

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 04

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.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

These are the Best Christmas Ads and Studio Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…