Query help

[Cheeze83]

Hi All

Building a query in Access and could do with some assistance.

I have two tables. Company and Contact. In the company table we have a list of company information, and in the contact table the contacts at those companies, and their information.

There will only ever be 1 record for each company in the Company table, but there could be, for example, up to 5 contacts assigned to that company.

So the tables are linked, Company ID to Contact Company ID - these numbers are the same for each contact linked to the company.

I have the fields:

Company ID
Contact First Name
Contact Last Name
Status

in my query. The status is set by the person calling the record, and can be a number of things.

What I am looking to do, is identify where someone has booked an appointment for someone at a company, but I want it to show me the contacts left that haven't be booked for an appointment.

So the Status would show as 'Appointment' where it has been booked, but:

Company A

We have booked an appointment with Mr Smith, so his status shows as Appointment.

The other 4 contacts haven't had an appointment booked, but there status could be anything (Voicemail, Not Called, Not Interested etc etc).

So I want to see Company A, but all records apart from Mr Smith - is this possible?

Thanks in advance!

 

[Micron]

If I get the drift, yes. Your query WHERE clause would be something like ...WHERE tblCompany.Status <>'Appointment';
However, status does not belong in tblCompany. I think you would benefit from a review of database normalization. Each aspect of a database that can be considered as its own entity virtually always should be in its own table (I cannot think of a case where it should not be). You should also read up on naming objects since spaces and reserved words (Name) should not be part of an object name. Strongly suggest you take a look at these links:
Microsoft Access tips: Problem names and reserved words in Access
MS Access Naming Conventions - Access World Forums
Autonumbers - Access wiki - Access Help and How-to - Microsoft Office by UtterAccess.com
there are a lot of topics here in the left nav pane: Microsoft Access and SQL Server Database Normalization Tips