Looking for solution for "or (but not both)" in query criteria

[mikenelena]

Our industry has recently discovered mail forwards, and is starting to make great use of them. In response, I changed our database to include client e-mail addresses in a separate table. A descriptor is also included for those e-mail addresses. (Personal, Acknowledgements, Status Reports, Invoices, etc.)

Here is the issue. When a client has multiple e-mail addresses, our reports generate an additional identical page for each e-mail address. I could handle the logic if we were to add every type of e-mail for every client, but many use only 1 e-mail address.

I need someway to write the query criteria such that if there is an "Acknowledgement" e-mail for the client, we use that, and only that. If there isn't one, we use "Individual" e-mail. Placing these criteria on different lines of the query builder is treating them, logically, as OR. I need the logic to be more like OR, but not both.

Thanks for any help this group can offer!

 

[Joe4]

I am not quite clear on your structure.
Can you post the SQL code of your query, along with a sampling of what your data looks like, so that we know exactly what we are working with?

 

[mikenelena]

Joe4, thanks for your willingness to help.

Here are the relevant fields from the data returned by the query for a single invoice number:

Invoice_Number	Adjuster Name	Email	EmailType
123456-01	Adjuster Name	alternate@client.com	Acknowledgement
123456-01	Adjuster Name	alternate@client.com	Invoices
123456-01	Adjuster Name	alternate@client.com	Misc.
123456-01	Adjuster Name	alternate@client.com	Team
123456-01	Adjuster Name	adjuster.name@client.com	Individual (Office)

So when we want to send a report such as an acknowledgement of assignment, the PDF document generates with an extra identical page for each of the e-mail addresses.

And here is the SQL code:

SELECT ClaimInfo1.Date_Received, ClaimInfo1.File_Number, ClaimInfo1.[Date Of Loss], ClaimInfo1.[Claim Number], ClaimInfo1.[Field Work Appraiser], ClaimInfo1.[Report Appraiser], ClaimInfo1.[Vehicle Owner], ClaimInfo1.[Client Name], ClaimInfo1.[Adjuster Name], ClaimInfo1.Invoice_Number, ClaimInfo1.[Adjuster Name], Appraisers.email3, Appraisers.Appraiser_Phone, Appraisers.Cell_Phone, ClaimInfo1.Zip, tblZip_State.State_Match, ClaimInfo1.chkTransfer, ClientInfoQuery2.ClientName, ClientInfoQuery2.AdjusterFirst, ClientInfoQuery2.AdjusterLast, ClientInfoQuery2.AdjusterSuffix, ClientInfoQuery2.Street, ClientInfoQuery2.Suite, ClientInfoQuery2.City, ClientInfoQuery2.State, ClientInfoQuery2.Email, ClientInfoQuery2.ZipCode, ClientInfoQuery2.AdjusterInactive, tblEmailTypes.EmailType
FROM tblEmailTypes RIGHT JOIN ((ClientInfoQuery2 INNER JOIN ((((ClaimInfo1 LEFT JOIN ClaimInfo2 ON ClaimInfo1.[Invoice_Number] = ClaimInfo2.[Invoice_Number]) LEFT JOIN tblItems ON ClaimInfo2.Item_Code = tblItems.Item_Code1) LEFT JOIN tblZip_State ON ClaimInfo1.Zip = tblZip_State.Loss_Zip) LEFT JOIN Appraisers ON ClaimInfo1.[Field Work Appraiser] = Appraisers.[Appraiser Name]) ON (ClientInfoQuery2.[Full Name] = ClaimInfo1.[Adjuster Name]) AND (ClientInfoQuery2.ClientName = ClaimInfo1.[Client Name])) INNER JOIN tblEmail ON ClientInfoQuery2.Email = tblEmail.Email) ON tblEmailTypes.EmailType_ID = tblEmail.EmailTypeID
GROUP BY ClaimInfo1.Date_Received, ClaimInfo1.File_Number, ClaimInfo1.[Date Of Loss], ClaimInfo1.[Claim Number], ClaimInfo1.[Field Work Appraiser], ClaimInfo1.[Report Appraiser], ClaimInfo1.[Vehicle Owner], ClaimInfo1.[Client Name], ClaimInfo1.Invoice_Number, ClaimInfo1.[Adjuster Name], Appraisers.email3, Appraisers.Appraiser_Phone, Appraisers.Cell_Phone, ClaimInfo1.Zip, tblZip_State.State_Match, ClaimInfo1.chkTransfer, ClientInfoQuery2.ClientName, ClientInfoQuery2.AdjusterFirst, ClientInfoQuery2.AdjusterLast, ClientInfoQuery2.AdjusterSuffix, ClientInfoQuery2.Street, ClientInfoQuery2.Suite, ClientInfoQuery2.City, ClientInfoQuery2.State, ClientInfoQuery2.Email, ClientInfoQuery2.ZipCode, ClientInfoQuery2.AdjusterInactive, tblEmailTypes.EmailType, ClaimInfo2.Appraiser
HAVING (((ClaimInfo1.Date_Received)>Now()-100) AND ((tblEmailTypes.EmailType)="Acknowledgement")) OR (((ClaimInfo1.Date_Received)>Now()-100) AND ((tblEmailTypes.EmailType)="Individual (Office)"))
ORDER BY ClaimInfo1.File_Number;

 

[Joe4]

Here is one way you can do this, using a series of queries.

First, create a query from your table above, with all of the fields, adding a calculation field like this:

EmailRank: IIF([EmailType]="Acknowledgement",2,IIF([EmailType]="Individual (Office)",1,0))

Then, create another query from this query, which is an Aggregate (Totals) Query.
Group by the "Invoice_Number" field, and take the Max value of your "EmailRank" calculated field.

Then, create a third query from the two above, joining them on both "Invoice_Number" and "EmailRank", and return all the fields you want from the first query.

This will give you one record for each Invoice Number (assuming that every Invoice has at least one of "Acknowledgement" or "Individual" email), with your desired email address.

Of course, if you really want to, you can roll all of this into one query using nested queries, but there is no reason you have to do that (actually doing that makes it a little harder to maintain, as you would have to maintain it manually instead of using the Query Builder).

 

[mikenelena]

Joe4, this looks like a brilliant solution. I'm going to try it now and will let you know how it goes! Thanks!

 

[Joe4]

Joe4, this looks like a brilliant solution. I'm going to try it now and will let you know how it goes! Thanks!

You are welcome.

Let me know if you run into any issues implementing it.

 

[mikenelena]

Joe4,

The only issue I had was an ambiguity in which table the emailtype was coming from, but I solved that without too much difficulty. This does indeed work, and I very much appreciate it.

Is it possible, and might I be better off assigning rank to the e-mails in the email table, rather than handling in the SQL? My thinking is that additional e-mail types may come along, and it would be easier if end users could assign rank, impossible for them to re-write SQL. What do you think?

 

[Joe4]

Is it possible, and might I be better off assigning rank to the e-mails in the email table, rather than handling in the SQL? My thinking is that additional e-mail types may come along, and it would be easier if end users could assign rank, impossible for them to re-write SQL. What do you think?

Sure. Here is how I would go about it.

Create a separate table that has two fields:
- EmailType
- EmailRank

So, each type would be listed in there exactly once, with how you want it ranked. You can then add this to your query, linking on the "EmailType" field to get the "EmailRank" whenever you like.

It is then easy to manage this, as you add additional "EmailType" records in the future.