Search for a value in 12 columns

I am currently building an Access database for booking and invoicing at the small business where I work. One of the tables I have built contains an employee name and then a number of columns which range from Popup_Date_1 to Popup_Date_12, which each hold a date value.

I would like to query all 12 fields at one time to look for one date and then pull that record if the date is contained in one of the twelve fields, but I am not able to get Microsoft Access to do this.

Is there an SQL query I could use to accomplish this? I'm sorry to be a bit more ignorant than most people on this site. This is the last obstacle to getting the whole thing working so I would love any advice you could offer!

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

First of all, here is the query that you need:

select employee_name
     , Popup_Date_1 
     , Popup_Date_2 
     , Popup_Date_3 
     , Popup_Date_4 
     , Popup_Date_5 
     , Popup_Date_6 
     , Popup_Date_7 
     , Popup_Date_8 
     , Popup_Date_9 
     , Popup_Date_10
     , Popup_Date_11
     , Popup_Date_12
  from bookings
 where Popup_Date_1 = [enter search date]
    or Popup_Date_2 = [enter search date]
    or Popup_Date_3 = [enter search date]
    or Popup_Date_4 = [enter search date]
    or Popup_Date_5 = [enter search date]
    or Popup_Date_6 = [enter search date]
    or Popup_Date_7 = [enter search date]
    or Popup_Date_8 = [enter search date]
    or Popup_Date_9 = [enter search date]
    or Popup_Date_10 = [enter search date]
    or Popup_Date_11 = [enter search date]
    or Popup_Date_12 = [enter search date]

But more importantly—and thank you for asking for advice—your table should probably be normalized. Do a web search for the terms "database normalization" and "first normal form." You will find many useful articles.

This was first published in January 2007

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top