Returning a list of records in the parent that don't contain certain child data

[Anchoress]

I have a simple contact management data maintaining records of activities by salespeople with individual accounts.

The child table activities being tracked for each account may be performed by one of several different staff members.

I have (with the help of this board) mastered creating lists of accounts (from the parent table) that contain activities by particular staff members, but I can't figure out how to generate a list of accounts that have *not* been contacted by a particular staff member.

The DB has about 4000 parent records, 10,000 child records and 6 sales staff contacting the accounts.

Attached is a Word doc that contains a screencap of my (simple, humble) table structure, some sample data from my child table and a description of what I'm trying to do.

Thanks in advance from a very basic Access user!

View attachment Sales DB for help with AccessWorldForums.doc

 

[Anchoress]

Anyone? If my question is too basic, I would welcome a shove in the direction of info I can read and research myself to answer my own question.

 

[Chaz]

Anyone? If my question is too basic, I would welcome a shove in the direction of info I can read and research myself to answer my own question.

Im probably not qualified / experienced to answer and might be guessing but I would consider doing a query with a criteria in the one field of 'Is Null' for any blanks.

 

[Scooterbug]

What is the criteria for an account that has not been contacted? If it's a blank entry for the SalespersonID, then as Chaz said, you could set the criteria for that field to Is Null. (Assuming that the SalespersonID is datatype Number)

 

[Anchoress]

Im probably not qualified / experienced to answer and might be guessing but I would consider doing a query with a criteria in the one field of 'Is Null' for any blanks.

Thanks very much for the reply, but that wouldn't fit with my data.

 

[Anchoress]

What is the criteria for an account that has not been contacted? If it's a blank entry for the SalespersonID, then as Chaz said, you could set the criteria for that field to Is Null. (Assuming that the SalespersonID is datatype Number)

Thanks very much for the reply! I guess my attachment wasn't clear enough, sorry about that. I already know how to do lists of records in the parent table with no associated child records, but what I need to do is to show a list of parent records that haven't been contacted by a certain salesperson, irrespective of whether or not they have been contacted at all.

If I'm expressing myself poorly or if my attachment is unclear, I apologise and ask readers to tell me, so I can try to do better.

Also, the "Is Null" function works with other data types besides numbers.

 

[Scooterbug]

Thanks very much for the reply! I guess my attachment wasn't clear enough, sorry about that. I already know how to do lists of records in the parent table with no associated child records, but what I need to do is to show a list of parent records that haven't been contacted by a certain salesperson, irrespective of whether or not they have been contacted at all.

Ok, a bit clearer now. If you are looking to generate a list of parent records that have not been contacted by a certain salesperson all you need to do is set the criteria to not equal the particular salespersonID. So for example if you wanted to see the list of parent records that havent been contacted by the person who's salespersonID is 6, the criteria in TContacts.CID would be: <>6

Also, the "Is Null" function works with other data types besides numbers.

I know but if you were using a text for the SalesPersonID, I would suggest that you use nz([SalesPersonID],"") <> "". The reason for that is an text field can either be Null (if nothing has ever been entered) or a zero length string (For example, someone begins entering data then erases it).

 

[Anchoress]

Ok, a bit clearer now. If you are looking to generate a list of parent records that have not been contacted by a certain salesperson all you need to do is set the criteria to not equal the particular salespersonID. So for example if you wanted to see the list of parent records that havent been contacted by the person who's salespersonID is 6, the criteria in TContacts.CID would be: <>6.

Thank you very much for thinking about my problem and replying with suggestions.

OK, just to make sure we understand each other: the table that contains the data I want to query on is in the child table, “T Visit Info”, not the parent table, “T CONTACTS”. Also, the CID field is the CustomerID in the parent table, it has nothing to do with salespeople, who are mentioned by name in the relevant records of the child data.

So are you suggesting that I create a simple two table select query, where I include whatever fields from the parent table I want, and include the relevant field from the child table (in this case “By (person)”), in order to use the “Not” operator to exclude certain records?

If so, I’ve already done so, and the result isn’t accurate, because it includes parent table records that HAVE been contacted by the salesperson I wish to exclude, as long as that record has also been contacted by other salespeople. From what I understand, the query is returning all the records where the salesperson is anyone but the person I exclude.

In other words, I run the query to exclude salesperson Z:

*ABC company is included because that parent record has no child records [GOOD]

*DEF company is included because the record has associated child records by salespeople X and Y but Z has not contacted the company [GOOD]

*GHI company is included because it has associated records by X and Y salespeople – even though salesperson Z has also contacted this record. [NOT GOOD]

Again, any help is very welcome!

 

[Scooterbug]

In other words, I run the query to exclude salesperson Z:

*ABC company is included because that parent record has no child records [GOOD]

*DEF company is included because the record has associated child records by salespeople X and Y but Z has not contacted the company [GOOD]

*GHI company is included because it has associated records by X and Y salespeople – even though salesperson Z has also contacted this record. [NOT GOOD]

Again, any help is very welcome!

How is that you know that salesperson Z has also contacted GHI? Is there another table where each contact with said company is recorded?

Edit: Took a closer look at your diagram. Is the TVisitInfo the table where you store the individual contact records? So there should be at least 2 entries in tVisitInfo for Company DEF, since both salesperson X and Y contacted them?

If that is the case, then the criteria (<>6) should be on the tVisitInfo.CID. You can also stick a DISTINCT statement in there so that you only get a list of the Companies once.

 

[Anchoress]

How is that you know that salesperson Z has also contacted GHI? Is there another table where each contact with said company is recorded?

I just looked, to double-check if the query was working. The "T Visit Info" table records every contact with customers. When I view my data in the data entry form or in the parent table view, all the related child records are visible.

 

[Anchoress]

How is that you know that salesperson Z has also contacted GHI? Is there another table where each contact with said company is recorded?

Edit: Took a closer look at your diagram. Is the TVisitInfo the table where you store the individual contact records? So there should be at least 2 entries in tVisitInfo for Company DEF, since both salesperson X and Y contacted them?

If that is the case, then the criteria (<>6) should be on the tVisitInfo.CID. You can also stick a DISTINCT statement in there so that you only get a list of the Companies once.

Correct. When I run the query as a plain select query, I get (using the example I checked on) two returns for the record "ABACO" used as an example in the preceding screencap, one for Lukah and one for Nat. As you can see from the screencap, Zona has also contacted that record.

 

[Scooterbug]

Alright, got it figured out for ya. I've attached an crude example. Here is what I did.

Step 1. Create a query to generate a list of the Companies that the Salesperson has contacted. This can be done by creating a query based off of T_Visits. Save the query (example is saved as: qselContactsNotVisited)

Step 2. Create a query using the above query and the T_Contact. Create a RIGHT JOIN between the two (Include ALL records from T_Contact and only records from query where join fields are equal) Add the Company Name from Contacts and SalespersonID from the query. If you now view it, you will see entries that have no SalespersonID. These are companies that do not have a visit by the Salesperson. Set the criteria for the SalespersonID to Is Null and you should have your results.

 

[Anchoress]

Alright, got it figured out for ya. I've attached an crude example. Here is what I did.

Step 1. Create a query to generate a list of the Companies that the Salesperson has contacted. This can be done by creating a query based off of T_Visits. Save the query (example is saved as: qselContactsNotVisited)

Step 2. Create a query using the above query and the T_Contact. Create a RIGHT JOIN between the two (Include ALL records from T_Contact and only records from query where join fields are equal) Add the Company Name from Contacts and SalespersonID from the query. If you now view it, you will see entries that have no SalespersonID. These are companies that do not have a visit by the Salesperson. Set the criteria for the SalespersonID to Is Null and you should have your results.

Unfortunately, I can't open your example because I'm using Office 2000 (I know... I use what I'm given). But I will try to follow your instructions and see what happens. Thanks so much for your help!

 

[Scooterbug]

Saved it in 2000 format. Here ya go.

 

[Anchoress]

Alright, got it figured out for ya. I've attached an crude example. Here is what I did.

Step 1. Create a query to generate a list of the Companies that the Salesperson has contacted. This can be done by creating a query based off of T_Visits. Save the query (example is saved as: qselContactsNotVisited)

Step 2. Create a query using the above query and the T_Contact. Create a RIGHT JOIN between the two (Include ALL records from T_Contact and only records from query where join fields are equal) Add the Company Name from Contacts and SalespersonID from the query. If you now view it, you will see entries that have no SalespersonID. These are companies that do not have a visit by the Salesperson. Set the criteria for the SalespersonID to Is Null and you should have your results.

It totally, totally worked and I so appreciate your help!!

 

[Scooterbug]

Glad it worked out for ya