Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    15

    Unanswered: Lookup without Join; my latest problem

    All:

    I am trying to determine a transportation fuel surcharge based on date range and transportation method. I have two tables, pertinent data therein listed below:

    Table 1: [10000 records]

    Package Code
    Transportation Method (either 'A' or 'B')
    Ship Date (Gregorian)

    Table 2: [15 records]

    Start Date (Gregorian)
    End Date (Gregorian)
    Surcharge for method A
    Surcharge for method B

    I am trying to return the surcharge corresponding to (1) which Date Range the ship date corresponds with and (2) which transportation method is in the record in Table 1.

    For simplicity's sake, I'm going to just return "ARt" for everything (I know how to make the chooser based on methods A and B.)

    The function I created and put into the query is as follows:


    Function Surcharge(ShpDt As String, begin As String, Finish As String, Method As String, ARt As Double, BRt As Double) As Double
    Select Case PUdt
    Case begin To Finish
    Surcharge = ARt
    Case Else
    Surcharge = 9999
    End Select
    End Function

    The reason I set the "else" surcharge to 9999 is that sometimes the surcharge applied within a given date range is legitimately zero.

    My problem is that I only want it to return the number of rows in Table 1. It's returning the product of the numbers of rows in Table 1 and Table 2. If there's a way to program this in, that would be preferable - setting criteria on the surcharge or grouping the result of the query takes a long time and doesn't sit well with me.

    Does anyone have any suggestions?

    I would greatly appreciate any help that could be provided!

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Query without joins

    Hi

    On the basis that the surcharge date ranges do no overlap, then my initial thoughts are to

    1) Write a function in a global module something like

    Function SuchargeRate(byval ShipDate as Date, byval TransportMethod as string) as single
    Dim rs as recordset
    Dim SQL as String

    SQL = “SELECT SurchargrTypeAField, SurchargrTypeBField FROM Table2 “
    SQL = SQL & “WHERE StartDateField <= #“ & ShipDate & “# AND EndDateField >= #“ & ShipDate &”#”

    Set rs = CurrentDB.OpenrecordSet(SQL)

    Select Case TransportMethod
    Case Is = “A”
    SurchargeRate = rs(“SurchargrTypeAField”)
    Case Is = “B”
    SurchargeRate = rs(“SurchargrTypeBField”)
    End Select

    End Function


    2) Construct a query using the above function ie.

    SELECT Table1.*, SuchargeRate(Table1.ShipDate, Table1.TransportMethodField) as Surcharge FROM Table1”


    Hope this is useful.

    There are probable otherways but it seems a bit complicated using just queies and joins !!??

    Please note this was not written in Access so these in no code syntax checking etc !!

    MTB

    PS If you are using UK dates in the database then you may need to substitute this line

    “WHERE StartDateField <= #“ & Format(ShipDate,”mm/dd/yy”) & “# AND EndDateField >= #“ & Format(ShipDate,”mm/dd/yy”) &”#”

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    Firt line should have read

    On the basis that the surcharge date ranges do NOT overlap, then my initial thoughts are to

    Sorry

    MTB

  4. #4
    Join Date
    Jan 2005
    Posts
    15
    Mike,

    Thank you - I adapted your query and code to Access and it dramatically improved my database's performance!

Posting Permissions

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