Professional Web Applications Themes

Access query for same parameter in multiple columns - ASP Database

Hello all: I have an Access 2003 database and a set of ASP pages. The database contains very basic information consisting of first and last names and 1-10 dates. I would like to create a query in ASP or inside of Access that I can use the SQL from to call data from the webpage that allows the user to search one date and find all instances of that date in all records. Example: Field names in Access: LunchDutyDate1, LunchDutyDate2, LunchDutyDate3...etc. User searches for 10/1/2004 This value could appear in multiple records, but in different fields. User ID 1 could ...

  1. #1

    Default Access query for same parameter in multiple columns

    Hello all:

    I have an Access 2003 database and a set of ASP pages.

    The database contains very basic information consisting of first and last
    names and 1-10 dates.

    I would like to create a query in ASP or inside of Access that I can use the
    SQL from to call data from the webpage that allows the user to search one
    date and find all instances of that date in all records.

    Example:

    Field names in Access: LunchDutyDate1, LunchDutyDate2, LunchDutyDate3...etc.

    User searches for 10/1/2004

    This value could appear in multiple records, but in different fields. User
    ID 1 could have this value in the LunchDutyDate1 field, while UserID 2 could
    have the same value in LunchDutyDate3.

    I would like to return both User's records.

    Thanks,
    Rich


    Rich Guest

  2. #2

    Default Re: Access query for same parameter in multiple columns

    WHERE LunchDutyDate1=#10/1/2004# OR LunchDutyDate2=#10/1/2004# OR
    LunchDutyDate3=#10/1/2004#

    Or you could design your DB correctly and have a seperate table with the
    LunchDuty dates.

    Bob Lehmann

    "Rich Palarea" <com> wrote in message
    news:phx.gbl... 
    the 
    LunchDutyDate3...etc. 
    could 


    Bob Guest

  3. #3

    Default Re: Access query for same parameter in multiple columns

    Rich Palarea wrote: 

    This is not the best database design. (somewhat of an understatement).

    this query would be child's play if you broke these fields out into a
    separate table, arranged vertically instead of horizontally:

    Original Users table:
    UserID
    other fields
    Primary key = UserID

    UserDates table
    UserId Same as UserID in Users table
    DateType Text - included in case there are other categories
    besides LunchDuty
    DateNumber Number
    UserDate Date/Time
    Primary key = UserID,DateType, DateNumber


    So the data in Users would look like this:
    UserID LastName etc
    1 Smith
    2 Jones


    and the data in UserDates would look like this:

    Userid DateType DateNumber UserDate
    1 LunchDuty 1 8/1/2004
    1 LunchDuty 2 9/1/2004
    1 LunchDuty 3 10/1/2004
    2 LunchDuty 1 9/21/2004
    2 LunchDuty 2 10/1/2004


    The query would simply look like this:

    Select u.UserID, u.LastName,d.DateNumber
    FROM Users u INNER JOIN UserDates d ON
    u.UserID = d.UserID
    WHERE DateType='LunchDuty' AND UserDate=#2004-10-01#


    If for some reason you cannot modify the design of this database, you can
    create a saved query that will provide the data in the necessary format. It
    will not perform as well, but it will work if your users do not mind wating
    for results. Provide some more details about your current structure (how
    many date fields are involved and how many date categories) and we will
    provide some guidance.

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  4. #4

    Default Re: Access query for same parameter in multiple columns

    What a bonehead table design that was. Of course I needed two tables and a
    join. Thanks for the advice!
    Rich
    "Rich Palarea" <com> wrote in message
    news:phx.gbl... 
    the 
    LunchDutyDate3...etc. 
    could 


    Rich Guest

Similar Threads

  1. Specify Query Columns from From
    By SincityViper in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: April 27th, 07:43 AM
  2. MS Access d/b saved parameter query problem
    By Larry Rekow in forum ASP Database
    Replies: 3
    Last Post: August 3rd, 07:38 PM
  3. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  4. Parameter query Help
    By Mark A. Sam in forum Microsoft Access
    Replies: 3
    Last Post: July 30th, 04:00 PM
  5. parameter query in formulars
    By Matthias Reichelsdorfer in forum Microsoft Access
    Replies: 2
    Last Post: July 16th, 03:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139