1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Date query

Discussion in 'Business Applications' started by jpatadia, Jan 20, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. jpatadia

    jpatadia Thread Starter

    Joined:
    Jan 20, 2011
    Messages:
    3
    I am running query where I want Price data from price table for particular month.Here is my
    tables.

    See the attachment for tables.




    Customer table has Date and Product code and Price table has date, code and price.I need query that will give me price from Price table based on Product code in Customer table.Example: In customer table product 100 sold in Jan , May and Aug. Price table has price for code 100 for Jan which got changed in Feb. then got changed again in April and so on.....I need a query for product 100 which got sold in may in customer table but available price in price table is in April. Query should pick first if the record available in same month (may) in price table if not in may the then the previous month like April.Thanks for help in advance.
     

    Attached Files:

  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    jpatadia, welcome to the Forum.
    I assume that you have a Quantity in your Customer Table as well?

    I think I can get a query to do what you want, but it may take 2 queries.
     
  3. jpatadia

    jpatadia Thread Starter

    Joined:
    Jan 20, 2011
    Messages:
    3
    Thanks OBP for prompt reply.I don't have qty in customer table. My main concern is picking the right price from price table.I am fine with two queries.So let me once again put light on the issue.There is a product sold in various months in customer table. There is a price for that product that got update in various months for same product.Query should look into the price table, if customer table has sold product "100" in may so query should look for price in price table for price in may if it doesn't find price in may month than it should look for recent price update which is in April.I hope this will make it more clear.Thanks again....
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    It would be much easier to achieve what you want if you had a DateTo field in your Prices table which held the date that the price changed again. See the attached database, the queries PreCALC and MyClac1 show how the data can be obtained and then used for calculations.
    Jimmy the Hands queries JimmysCALC and NewJimmysCALC also work well.
     

    Attached Files:

  5. jpatadia

    jpatadia Thread Starter

    Joined:
    Jan 20, 2011
    Messages:
    3
    Thanks a lot OBP.....cheers......I appreciate
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/975831

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice