Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005
    Posts
    18

    Unanswered: Query Looks at Table for Dates

    I have been doing some research to find out if there's a classic process/example of how to create a table or query that has a data range, say Between 12/01/2005 and 06/30/2006 that another query will go and look at to pull the data out or not?

    For example, I have a table called Allowances that has product numbers and discounts only, but I would like to add a date range to this table. I then have a query that uses this table to pull out discounts and apply them to the products in this query.

    The discounts in the Allowance table are not forever and do expire. So if I'm able to add a date range to the discount table, the query that looks into this table will see that the date has expired and will not apply the discount--the query will actually ignore it. I want to do this in case I have to revisit the query again and put a past date range, those discounts will still apply 'cause they fall within the discounts date range. And also I don't have to manually be removing and entering new discounts on a monthly and sometimes daily basis.

    I'll just put one entry with a date range, and it's done 'till it expires. If you know of any examples on a web-page or know what this type of reporting is called, it'll help me on my way to get this going. Yeah!

    Frosty
    (Frosty was taken, so hand to settle for FrostyFree)

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm not following you. Could you post your table structure and an example of applying a discount?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2005
    Posts
    18
    I'm sorry, I tried to put together an example of the db, but I'm messing up trying to erase real data with fake data to cover up the real numbers I work with. But I hope I can explain here in text.

    The table I have formed has four fields inside:

    Product#, Product Price, Discount, Date Range (Mostly for notes telling me when the discount expires).

    The query I use has a whole list of products and is linked to the table above by the product number and product price. The query is linked to take any matches from the table and apply the field values over to itself. For example, if product #3456 has a price of $2.00, then the table will apply a discount of 50 cents, reducing the $2.00 to $1.50 on the query.

    This is all working well, except that I want to leave old discounts in there without deleting them off, like I do manually. I was thinking that the query I use has a data range as a where field, so what if I have the query go into the table and look at the date range and know whether or not to apply the discount. If the query finds the table has dates out of range, then it won't apply the discount. To avoid confusion, I was going to use Julian dates if normal dates become bothersome.

    So what do you think? Am I smoking some good stuff or what?

    Frosty
    (Frosty was taken, so hand to settle for FrostyFree)

Posting Permissions

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