473,320 Members | 1,946 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

36
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.
Apr 7 '10 #1
6 4190
Delerna
1,134 Expert 1GB
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,etc....

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

You will need to provide table structure and some sample data
if you want an answer that is more specific
Apr 8 '10 #2
brat33
36
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.Address, 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.Deceased)=False) AND (([Sales & Payments].Canceled)=False) AND ((Customers.Married)=True));
Single:
SELECT Customers.[First Name], Customers.[Last Name], Customers.Title, Customers.Address, 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.Deceased)=False) AND (([Sales & Payments].Canceled)=False) AND ((Customers.Married)=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…………
Apr 8 '10 #3
Delerna
1,134 Expert 1GB
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
Apr 8 '10 #4
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 'UseBusinessAddress' (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:

MailingAddressStreet1
MailingAddressStreet2
MailingAddressCity
MailingAddressCountry

etc

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

MailingAddressStreet1: iif([UseBusinessAddress],[BusinessAddressStreet1],[HomeAddressStreet1])

MailingAddressStreet2: iif([UseBusinessAddress],[BusinessAddressStreet2],[HomeAddressStreet2])

MailingAddressCity: iif([UseBusinessAddress],[BusinessAddressCity],[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.
Apr 9 '10 #5
brat33
36
DrJarmin - exactly what I was looking for! Hit a lightbulb for me! Thank you everyone who replied!!
Apr 12 '10 #6
brat33
36
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT Customers.Salutation, Customers.[First Name], Customers.[Last Name], Customers.Title, Customers.Address, Customers.City, Customers.State, Customers.Zip
  2. 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]
  3. 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));
  4. UNION 
  5. SELECT DISTINCT Customers.Salutation, Customers.[First Name], Customers.[Last Name], Customers.Title, Customers.Address, Customers.City, Customers.State, Customers.Zip
  6. 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]
  7. WHERE ((([Sales & Payments].[Trip Number])=[Enter Trip Number]) AND ((Customers.Deceased)=False) AND (([Sales & Payments].Canceled)=False) AND ((Customers.Married)=True));
  8.  
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: DuplicateAddress: 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.

Expand|Select|Wrap|Line Numbers
  1. SELECT First((Customers.Salutation + " ") & Customers.[First Name] &
  2. Customers.[Last Name]) As CustName,
  3. Customers.Address, Customers.City, Customers.State, Customers.Zip
  4. FROM Customers INNER JOIN (Trips INNER JOIN [Sales & Payments] ON Trips.[Trip
  5. Number] = [Sales & Payments].[Trip Number]) ON Customers.[Customer Number] =
  6. [Sales & Payments].[Customer Number]
  7. WHERE (((Customers.[Duplicate Address])=False) AND (([Trips].[Trip
  8. Number])=[Enter Trip Number]) AND ((Customers.Deceased)=False) AND (([Sales &
  9. Payments].Canceled)=False))
  10. GROUP BY Address, City, State, Zip
  11.  
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.
Apr 14 '10 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Chris Tilley - HPC:Factor | last post by:
Hi, I'm utterly confounded by this one. There must be some sort of rule that I don't know of. I'd consider myself a Newbie+1, so be gentle. I have a database connection (working A-Ok) and a...
1
by: avital | last post by:
Hi, I have a sql query with cases. I need to add a condition that if hasamafactor=5 then display only cases m11-m14 else display the rest. Of course sum ( kamut) as total4mosad has to be only...
2
by: Greg Stark | last post by:
I find I often want to be able to do joins against views where the view are aggregates on a column that has an index. Ie, something like SELECT a.*, v.n FROM a JOIN (select a_id,count(*) as n...
64
by: yossi.kreinin | last post by:
Hi! There is a system where 0x0 is a valid address, but 0xffffffff isn't. How can null pointers be treated by a compiler (besides the typical "solution" of still using 0x0 for "null")? -...
2
by: Daniel Di Vita | last post by:
I have created an ASP.NET page that allows the user to page through a result set. I need to expand on this. On that same page I a filed where the user can type in a search string. When they click...
14
by: tbird2340 | last post by:
I want to write an if / then statement and have tried using this: var MyVarMailto; if (Request.Form("LoanRequest") == "Under $250,000") { if (Request.Form("Organization") == "1") { MyVarMailto...
5
by: MIUSS | last post by:
I tried many things for several hours to repair this code but I didn't get success:-( The code below is from some learning site which is little old. I didn't change anything in there, I know it's...
4
by: dougans | last post by:
Hey there, Hope someone can help me, completely stuck with immigrating from php4 to OOP based php5. == index.php -- include(database.inc.php);
1
by: digidave | last post by:
I am keenly aware that my coding skills are extremely noob but please indulge me a second.. Take a look at these queries.. $sql = "SELECT DISTINCT year FROM _current_floats_config WHERE active =...
4
by: lutzkac | last post by:
Hi Everyone, I am in a bit of a pickle and need some expert advice. I have a db in access. I am trying to create a query with fields from the tables. In my query I need to take the value of one...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.