Results 1 to 5 of 5
  1. #1
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    108

    HOW TO CREATE query

    hello guys,
    I need your help.

    I have two table A and B

    Table A has column effective_date, expiry_date, fuel_charges

    effective_Date-- expiry_date-- fuel_charges
    1/1/2021 1/31/2021 $20.23
    2/1/2021 2/29/2021 $19.45
    ....... so on

    Table B has following columns

    PO_DT-- PO_NUM
    1/1/2021 00001
    1/2/2021 00002
    ......

    I want following result if PO_DT falls in between effective_date and expiry_date it shows fuel charges accordingly.
    PO_DT--- PO_NUM---- fuel_charges


    1/1/2021 001 20.23
    1/2/2021 002 20.23
    1/3/2021 003 20.23
    1/4/2021 004 20.23
    1/5/2021 005 20.23
    1/6/2021 006 20.23
    1/7/2021 007 20.23
    1/8/2021 008 20.23
    1/9/2021 009 20.23
    1/10/2021 010 20.23
    1/11/2021 011 20.23
    1/12/2021 012 20.23
    1/13/2021 013 20.23
    1/14/2021 014 20.23
    1/15/2021 015 20.23
    1/16/2021 016 20.23
    1/17/2021 017 20.23
    1/18/2021 018 20.23
    1/19/2021 019 20.23
    1/20/2021 020 20.23
    1/21/2021 021 20.23
    1/22/2021 022 20.23
    1/23/2021 023 20.23
    1/24/2021 024 20.23
    1/25/2021 025 20.23
    1/26/2021 026 20.23
    1/27/2021 027 20.23
    1/28/2021 028 20.23
    1/29/2021 029 20.23
    1/30/2021 030 20.23
    1/31/2021 031 20.23
    2/1/2021 032 19.45
    2/2/2021 033 19.45
    2/3/2021 034 19.45
    2/4/2021 035 19.45
    2/5/2021 036 19.45
    2/6/2021 037 19.45

    sorry i made a mistake in column names, red highlighted color should be output
    Last edited by adnancanada; 01-10-2021 at 06:45 PM.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,132
    It would really help if you used real field/table names....

    But try
    Code:
    SELECT TableB.PO_DT, TableB.[PO_NUm] AS [PO Num], TableA.fuel_charges
    FROM TableB, TableA
    WHERE (((TableA.effective_Date)<=[PO_DT]) AND ((TableA.expiry_date)>=[PO_DT]))
    ORDER BY TableB.PO_DT, TableA.effective_Date;


    My results from the query:
    PO_DT PO Num fuel_charges
    1/1/2021 001 $20.23
    1/2/2021 002 $20.23
    1/3/2021 003 $20.23
    1/4/2021 004 $20.23
    1/5/2021 005 $20.23
    1/6/2021 006 $20.23
    1/7/2021 007 $20.23
    1/8/2021 008 $20.23
    1/9/2021 009 $20.23
    1/10/2021 010 $20.23
    1/11/2021 011 $20.23
    1/12/2021 012 $20.23
    1/13/2021 013 $20.23
    1/14/2021 014 $20.23
    1/15/2021 015 $20.23
    1/16/2021 016 $20.23
    1/17/2021 017 $20.23
    1/18/2021 018 $20.23
    1/19/2021 019 $20.23
    1/20/2021 020 $20.23
    1/21/2021 021 $20.23
    1/22/2021 022 $20.23
    1/23/2021 023 $20.23
    1/24/2021 024 $20.23
    1/25/2021 025 $20.23
    1/26/2021 026 $20.23
    1/27/2021 027 $20.23
    1/28/2021 028 $20.23
    1/29/2021 029 $20.23
    1/30/2021 030 $20.23
    1/31/2021 031 $20.23
    2/1/2021 032 $19.45
    2/2/2021 033 $19.45
    2/3/2021 034 $19.45
    2/4/2021 035 $19.45
    2/5/2021 036 $19.45
    2/6/2021 037 $19.45
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,623
    Table B doesn't seem involved at all.

  4. #4
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    108

    Thumbs up HOW TO CREATE query

    Quote Originally Posted by ssanfu View Post
    It would really help if you used real field/table names....

    But try
    Code:
    SELECT TableB.PO_DT, TableB.[PO_NUm] AS [PO Num], TableA.fuel_charges
    FROM TableB, TableA
    WHERE (((TableA.effective_Date)<=[PO_DT]) AND ((TableA.expiry_date)>=[PO_DT]))
    ORDER BY TableB.PO_DT, TableA.effective_Date;


    My results from the query:
    PO_DT PO Num fuel_charges
    1/1/2021 001 $20.23
    1/2/2021 002 $20.23
    1/3/2021 003 $20.23
    1/4/2021 004 $20.23
    1/5/2021 005 $20.23
    1/6/2021 006 $20.23
    1/7/2021 007 $20.23
    1/8/2021 008 $20.23
    1/9/2021 009 $20.23
    1/10/2021 010 $20.23
    1/11/2021 011 $20.23
    1/12/2021 012 $20.23
    1/13/2021 013 $20.23
    1/14/2021 014 $20.23
    1/15/2021 015 $20.23
    1/16/2021 016 $20.23
    1/17/2021 017 $20.23
    1/18/2021 018 $20.23
    1/19/2021 019 $20.23
    1/20/2021 020 $20.23
    1/21/2021 021 $20.23
    1/22/2021 022 $20.23
    1/23/2021 023 $20.23
    1/24/2021 024 $20.23
    1/25/2021 025 $20.23
    1/26/2021 026 $20.23
    1/27/2021 027 $20.23
    1/28/2021 028 $20.23
    1/29/2021 029 $20.23
    1/30/2021 030 $20.23
    1/31/2021 031 $20.23
    2/1/2021 032 $19.45
    2/2/2021 033 $19.45
    2/3/2021 034 $19.45
    2/4/2021 035 $19.45
    2/5/2021 036 $19.45
    2/6/2021 037 $19.45
    Thank you very much ssanfu I put the origin tables name it worked. you are awesome

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,132
    You are welcome..
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 09-17-2018, 10:15 AM
  2. Replies: 5
    Last Post: 09-05-2016, 09:07 AM
  3. Replies: 3
    Last Post: 05-21-2015, 11:26 AM
  4. Replies: 5
    Last Post: 04-04-2014, 03:33 PM
  5. Replies: 7
    Last Post: 01-16-2014, 09:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums