Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    England
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Range Problem (Access 2000)

    I am having a problem with date ranges in a large database. (Millions of records).

    I have a table containing various data but the dates are in accounting period formats eg. 2000/2001 P01 or 2000/2001 P02 etc.
    I have another table with prices of products for a given date range in in a different format eg. 01/04/2000, 30/12/2000 etc.

    I have now created a "bridge" table to allow Access to match the two tables ie. 2000/2001 P01 (column 1) = 01/04/2000 (column 2= effective from), 30/04/2000 (column 3= effective until). I have created the relationship between the tables by linking Accounting Period (2000/2001 P01) with column 1 in the "bridge" and another relationship linking the Price List date effectivities (01/04/2000-31/03/2001) with column 2 & column 3.

    The problem lies with the fact that the prices could have an effectivity of more than one accounting period. So if the product was sold in Accounting Period 2000/2001 P03 but the price was effective from 01/04/2000 to 31/03/2000, Access will not find the price for the product in the price list because 2000/2001 P03 = 01/06/2000-30/06/2000!
    Issues are further complicated by product prices changing during the year as well.

    How do I build an expression to make Access find the correct price for the correct period? If I use a standard "between" expression, I end up with many duplicates records with different prices.

    Please can you help with this problem?

    Thanks

    Tok

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Range Problem (Access 2000)

    What you need is a table of dates--ALL the dates--from the earliest you might need through some period in the future that seems reasonable. Each date should have a unique ID, which can, in fact, be the date itself, but the table also needs a column that will hold the accounting period key the date falls into. This is not the kind of table you usually build in relational databases, but it is the kind of table you need in data warehouses because data there is often selected by date ranges. Then you can join the dates tables as needed on the appropriate field, which will allow you to filter accounting data using actual date ranges or prices by accounting periods. The dates table wouldn't be a join table as such. It's more of a lookup table that can work with either of your other two tables rather than creating a permanent join between them. The reason for that is that you need to include dates that may not *be* in either of the other two tables but which fall into the range of dates required. You would need to write some code to generate the table and populate the dates themselves, but you could populate the accounting period field with an update query if you didn't do it in code as you created the record.

    If this isn't clear, post back and I'll try to explain it better. Somewhere I have some code to build date tables but they were for a datawarehouse type app, so the date records involved included fields like quarter, month, etc., as well as the actual date. If you decide to go that direction, I can probably dust it off for you.
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Range Problem (Access 2000)

    Hi Tok,

    Charlotte has already given you useful tips. I'd like to add some remarks. I have attached a zipped Access 97 database to illustrate them. You'll have to unzip and convert it.

    (1) You can compute the date range from the accounting period and vice versa. Have a look at the qryPeriodToMonth and qryMonthToPeriod queries. So perhaps, you can do without a translation table.

    (2) You stated that a price could be effective for more than one accounting period. I don't know if the reverse situation occurs - a price change occurs halfway an accounting period. This would complicate things. (I'm not into accounting - maybe this is an absurd idea)

    To get the price for a product for a given accounting period, select the price for which the EffectiveFrom date is before or on the start of the accounting period AND the EffectiveUntil date is after (or on) the start of the accounting period.

    Have a look at the qrySinglePrices query to see this illustrated. I have assumed that you want to get the price for a product that is effective at the start of the accounting period. If you want to use the end of the accounting period, it's easy to change the criteria accordingly.

    HTH,
    Hans
    Attached Files Attached Files

  4. #4
    New Lounger
    Join Date
    Jul 2002
    Location
    England
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Range Problem (Access 2000)

    Hi Charlotte,

    Many thanks for the response. I have created the "date range table" for all possible dates and applied this to my existing database. It works fine BUT because there are a quite a few instances where the Accounting Period is covered by different date ranges, I end up with a lot of duplication. In effect, the record is duplicated but the price that is returned is different as there are let's say 2 or 3 possible prices that were within the date range lookup table. I have attached the date range table for you to get a rough idea of what's going on.

    Thanks for getting me started on this. Your assistance is appreciated.
    Cheers

    Tok

  5. #5
    New Lounger
    Join Date
    Jul 2002
    Location
    England
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Range Problem (Access 2000)

    Morning Hans,

    Thanks for the attachment. I'm, having a look at it now.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Range Problem (Access 2000)

    Your attachment didn't come through. You can't preview or spell check after specifying an attachment. Try again.

    I don't understand what you mean about an accounting period being covered by different date ranges. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> By definition, an accounting period can only cover a continuous range of dates and a date can belong to only one accounting period. Your table could be something like: ActualDate (not a range), Year, Month, Week of Year, Day of Year, Day of Month, Day of Week, Quarter, Accounting Period. Only the ActualDate would contain unique values. All the other fields would contain non-unique values. This is a first normal form table at best, which is what you need for this purpose.
    Charlotte

Posting Permissions

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