How to use If, Then, Else inside of a query

[brat33]

I have a customer table (user created and tech support took over) which holds basic customer data - names, address, etc. There is no primary key on this table and no data validations (YET). I have been asked to create a label report that will gather info from table and make labels. Easy to do.....BUT they want the labels to read Mr. & Mrs if the customers are married. There is a field which is home to "spouse name", and a duplicate address check field.
I need my code to look something like this:

IF duplicate address is false AND spouse name is spaces, then print out fname, lname, address, etc.... on labels
ELSE if the duplicate address is true then match the addresses fields to each other, AND match the spouse name on one record to the first name on the other record and only then print out Mr. & Mrs. lname, address, etc...

If the duplicate address is false, and the spouse name field has data in it, I do not want to do anything with it. That record should be covered in the If, Else statement. Providing the users are doing data entry correct.

Thank you for assisting me on this on. I have been playing with the IIF statement and if then statement in the query and am getting nowhere. I assume my coding placement is off somewhere.

[Delerna]

It sounds to me like you need three queries
One for all the single people
James Barrit,Address, etc

One for all the married people with their 2 records merged into 1
by joining the table to itself so that the spouse record matches to the partner record
Mr and Mrs Smith,Address,e tc....

and one union query that combines the above two into one for your report
James Barrit ,Address,etc
Mr and Mrs Smith,Address,e tc....

You will need to provide table structure and some sample data
if you want an answer that is more specific

[brat33]

OK - that makes things so much easier - so I though. Here are my two queries:
Married:

SELECT Customers.[First Name], Customers.[Last Name], Customers.Title , Customers.Addre ss, Customers.City, Customers.State , Customers.Zip, Customers.[Duplicate Address]
FROM Customers INNER JOIN (Trips INNER JOIN [Sales & Payments] ON Trips.[Trip Number] = [Sales & Payments].[Trip Number]) ON Customers.[Customer Number] = [Sales & Payments].[Customer Number]
WHERE (((Customers.[Duplicate Address])=False) AND (([Sales & Payments].[Trip Number])=[Enter Trip Number]) AND ((Customers.Dec eased)=False) AND (([Sales & Payments].Canceled)=Fals e) AND ((Customers.Mar ried)=True));

Single:

SELECT Customers.[First Name], Customers.[Last Name], Customers.Title , Customers.Addre ss, Customers.City, Customers.State , Customers.Zip, Customers.[Duplicate Address]
FROM Customers INNER JOIN (Trips INNER JOIN [Sales & Payments] ON Trips.[Trip Number] = [Sales & Payments].[Trip Number]) ON Customers.[Customer Number] = [Sales & Payments].[Customer Number]
WHERE (((Customers.[Duplicate Address])=False) AND (([Sales & Payments].[Trip Number])=[Enter Trip Number]) AND ((Customers.Dec eased)=False) AND (([Sales & Payments].Canceled)=Fals e) AND ((Customers.Mar ried)=False));

Now my problem is this...I am using 2 tables to get my data (customers and trips). In Customer, there are two checkbox fields - Married and Duplicate address. I was using them to distinguish married vs. single people. In Trips, there are just basic text fields for trip info and the customer number, referring to who is going on a trip. The customer number is how the tables are related.

My problem appeared when I have only 1 person going on a trip (and not the couple). The duplicate address field is listed on only 1 record, not both records. So when I check to see if the dup-address is false and the person attending the trip has this marked it is not being pulled. BUT on the other hand, if I remove this check then all the other couples who are going on the trip will both show up in the report, and we only want one per household to show.

What would be a good way to rectify this issue, so that I have all people accounted for who are attending the trip, both married and single? Therefore labels will be either:
Mr. & Mrs. Jim-Bob Smith, blah blah blah… OR
John Jones, blah blah blah…………

[Delerna]

difficult to say because I can't see the whole design.
Also not sure I completely understand the question.
I presume that if only jim smith of mr & Mrs Jim-Bob Smith
was going on the trip then the lable should read "Jim Smith"

Whatever the solution my guess would be it will involve a check to
see if only one partner in a marriage is going on a trip and treat them as if
they were single

[DrJarmin]

Ok, I am not sure I fully understand the problem but I think I had a similar issue. Maybe this will help.

In my contact table for a travel agent they stored both their home and work address. One address would be flagged as the 'mailing address' with a check box .

This was bound to a yes/no field in the table called 'UseBusinessAdd ress' (can't remember exact field name but not relevant).

So, in my query for the mail merge for their trip letters, the query had to pull the home address for some people on the trip and the business address for others depending on the value of this field.

I didn't want to pull both home and business addresses as the field names had to be static for the Word mail merge to work.

So, I needed a sort of if ... then in my query.

The way I did this was to create some calculated fields in the query called:

MailingAddressS treet1
MailingAddressS treet2
MailingAddressC ity
MailingAddressC ountry

etc

I created them like this (in the query builder):

MailingAddressS treet1: iif([UseBusinessAddr ess],[BusinessAddress Street1],[HomeAddressStre et1])

MailingAddressS treet2: iif([UseBusinessAddr ess],[BusinessAddress Street2],[HomeAddressStre et2])

MailingAddressC ity: iif([UseBusinessAddr ess],[BusinessAddress City],[HomeAddressCity])

Therefore the output was always the correct address based on which address the client had opted to use.

Hope this helps? Might not be the exact solution but the idea might be what you are looking for.

[brat33]

DrJarmin - exactly what I was looking for! Hit a lightbulb for me! Thank you everyone who replied!!

[brat33]

I thought I had it figured out. I have modified it so many itmes I am stuck again... :(

Currently I have the following SQL code being used to gather the correct data.

SELECT Customers.Salutation, Customers.[First Name], Customers.[Last Name], Customers.Title, Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Customers INNER JOIN (Trips INNER JOIN [Sales & Payments] ON Trips.[Trip Number] = [Sales & Payments].[Trip Number]) ON Customers.[Customer Number] = [Sales & Payments].[Customer Number]
WHERE (((Customers.[Duplicate Address])=False) AND (([Sales & Payments].[Trip Number])=[Enter Trip Number]) AND ((Customers.Deceased)=False) AND (([Sales & Payments].Canceled)=False) AND ((Customers.Married)=False));
UNION 
SELECT DISTINCT Customers.Salutation, Customers.[First Name], Customers.[Last Name], Customers.Title, Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Customers INNER JOIN (Trips INNER JOIN [Sales & Payments] ON Trips.[Trip Number] = [Sales & Payments].[Trip Number]) ON Customers.[Customer Number] = [Sales & Payments].[Customer Number]
WHERE ((([Sales & Payments].[Trip Number])=[Enter Trip Number]) AND ((Customers.Deceased)=False) AND (([Sales & Payments].Canceled)=False) AND ((Customers.Married)=True));

The problem with this code is I still end up with duplicate address data. Here is a sample of the data:

Salutation: Fname: Lname: Address: City: State: DuplicateAddres s: Married:
SALLY MAY 2344 CORAL COVE Somewhere somestate 12345- Y
ESTER SUE 2015 MONDAY Somewhere somestate 12345- Y Y
mr JOHN DOGOOD 809 DEBORAH AVENUE Somewhere somestate 12345- Y Y
mrs JANE DOGOOD 809 DEBORAH AVENUE Somewhere somestate 12345- Y
mr Duke ITOUT 788 Diane Avenue Somewhere somestate 12345- Y Y
mrs Nancy ITOUT 788 Diane Avenue Somewhere somestate 12345- Y
RUTH ROTH 815 W BURR OAK DRIVE Anywhere Anotherstate 98765-

As you can (hopefully) see I have both married and single individules on trip. Sometimes the married couple are together on trip and sometimes not. IF the married couple is on the trip together, I need to have the label read Mr. & Mrs. BUT if the married couple is not on the trip together, then they just need to read firstname last name. Of course that is the same way with any single person.

I have been given this code to work with also, but can not get it to run as I receive error message about "Specified field: Trips.[Trip Number] could refer to more than one table listed in the FROM clause of SQL". I am not sure how to get around this.

SELECT First((Customers.Salutation + " ") & Customers.[First Name] &
Customers.[Last Name]) As CustName,
Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Customers INNER JOIN (Trips INNER JOIN [Sales & Payments] ON Trips.[Trip
Number] = [Sales & Payments].[Trip Number]) ON Customers.[Customer Number] =
[Sales & Payments].[Customer Number]
WHERE (((Customers.[Duplicate Address])=False) AND (([Trips].[Trip
Number])=[Enter Trip Number]) AND ((Customers.Deceased)=False) AND (([Sales &
Payments].Canceled)=False))
GROUP BY Address, City, State, Zip

Anyone have any wonderful ideas for me to try now? I don't think this should really be this dificult to complete, and I feel kinda stupid for not figuring it out on my own, but the brain is just not working the past few days for this issue! :) Thanks again to anyone who may be able to help me out.