Results 1 to 6 of 6

Thread: Queries

  1. #1
    Join Date
    Sep 2002
    Posts
    79

    Unanswered: Queries

    I have 4 fields in my table called “due date 1”, “due date 2”, “due date 3” and “due date 4”.

    I want to create a query to get a list of due dates in a specific time fame. For example, once I run the query, I want 2 questions to pop up – First one says “enter start date” and I enter a beginning date and the second to say “enter end date” and I enter the end date of the timeframe. And once I enter those two dates, it gives me a list of all the records that have the due dates of those two specified dates.

    The dates should be contained in any of the above 4 fields.

    Any help is greatly appreciated.,

  2. #2
    Join Date
    Jan 2003
    Location
    Midwest
    Posts
    138
    my first question is why would you have a table with 4 due date columns?

  3. #3
    Join Date
    Sep 2002
    Posts
    79
    payment terms for contracts varies - 1 contract can have multiple payment options...I put 4 because some contract have up to 4 payments options and each payment has its own due date...

  4. #4
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67
    Originally posted by alex8111
    payment terms for contracts varies - 1 contract can have multiple payment options...I put 4 because some contract have up to 4 payments options and each payment has its own due date...
    The inquiry as to "why" was alluding to splitting the [due date] data off as a 1 to many relationship table.

    Contract Table
    | ContractID | Title | …

    New Table
    | DueID | ContractID | Due Date | ForTask |

    This way you only need to search one field with your criteria.

    To do it the way you have it:

    ... where ([DueDate1] >= date() and [DueDate1] <= date()+7) or ([DueDate2] >= date() and [DueDate2] <= date()+7) or ([DueDate3] >= date() and [DueDate3] <= date()+7) or ([DueDate4] >= date() and [DueDate4] <= date()+7)

  5. #5
    Join Date
    Sep 2002
    Posts
    79
    I dont understand that. Could you please be more specific

  6. #6
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67
    Originally posted by alex8111
    I dont understand that. Could you please be more specific
    Which part?

    Your Contract table is what is called a flat table, which is more difficult to work with than a relational database. When ever data repeats, it is easier to create a table that holds the repeating data seperately. In your case the Due Date. It saves space, allows for expansion without redesigning the table, and in your case allows information to be retrieved more easily.

    If you don't want to redesign the tables, then your query statement would look something like the following:

    Select Contract.*
    From Contract
    Where ([DueDate1] >= forms!main!StartDate and [DueDate1] <= forms!main!EndDate) or ([DueDate2] >= forms!main!StartDate and [DueDate2] <= forms!main!EndDate) or ([DueDate3] >= forms!main!StartDate and [DueDate3] <= forms!main!EndDate) or ([DueDate4] >= forms!main!StartDate and [DueDate4] <= forms!main!EndDate)

Posting Permissions

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