Results 1 to 5 of 5

Thread: Date Probs!

  1. #1
    Join Date
    Mar 2003
    Posts
    7

    Unanswered: Date Probs!

    Hello

    I have a table with a number of transactions in it, each with its own date. I have another table with Periods in it e.g.

    Period - 1
    Start Date - 20/02/03
    End Date - 20/03/03

    I want to be able to assign a period to the transactions using a query when they fall between the start and end dates of a period.

    Is this possible?

    Thanks in advance.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm not sure if this is an option for you or not, but the simplest way to do this would be to assign the period number (which I assume is a primary key on period table) to a int field on the transactions table. After that's done, it's a simple matter of a basic update query and you're good to go.

  3. #3
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    If you are using Access 2000 or higher, you can always use THETA JOINS (this means that you can establish relations between tables using any comparison operator, not only =):

    SELECT [Facts].[IdNo], [Facts].[Date], [Intervals].[Description]
    FROM Facts LEFT JOIN Intervals ON ([Facts].[Date]<=[Intervals].[DateEnd]) AND ([Facts].[Date]>=[Intervals].[DateSt]);


    I made it a OUTTER join in order to retrive records from the Facts table even if they do not match any interval that tou have defined in the intervals table.

    By the way, I presume that the key field of the Interval table is the DateSt field. It's not a good idea to define an integer filed in intevals table to be its primarykey, because you have no nedd for it (you always have DateSt), but if you do so then you will complicate your life:

    Imagine that the foreign key in facts table would be "so called" IntervalID field. Then when you will enter a new record (or update the value of Date field for an existing record) in you facts table you have to retrive the appropiate value for IntervalID foreignkey based upon the value of you Date field.


    IONUT

  4. #4
    Join Date
    Mar 2003
    Posts
    7
    Magic Stuff!! Works Like a dream... Thanks Ionut!

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Wow!! that's a nice little nugget of info to have. I redesigned quite a few of my parameter queries.

    While I didn't post the original problem, thanks!!

Posting Permissions

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