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

mikenelena

Board Regular
Joined
Mar 5, 2018
Messages
68
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!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 5, 2018
Messages
68
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_NumberAdjuster NameEmailEmailType
123456-01Adjuster Namealternate@client.comAcknowledgement
123456-01Adjuster Namealternate@client.comInvoices
123456-01Adjuster Namealternate@client.comMisc.
123456-01Adjuster Namealternate@client.comTeam
123456-01Adjuster Nameadjuster.name@client.comIndividual (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:

SQL:
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;
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
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:
Rich (BB code):
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).
 
Solution

mikenelena

Board Regular
Joined
Mar 5, 2018
Messages
68

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 5, 2018
Messages
68
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top