Results 1 to 8 of 8
  1. #1
    Join Date
    May 2015
    Posts
    4

    Unhappy Answered: Duplicated Values in Query from two tables using Iif Function

    Hi everyone!

    Really need some help here.

    I tried to make a query where it forms a new field. The new field is suppose to retrieve the export rate value from the ExchangeRate Table IF the date of the Lifting falls between the dates in the Exchange Rate (because the exchange rate is valid for a certain period of time).

    I had an ALMOST successful attempt because it worked but I got multiple duplicated values (see image).

    Click image for larger version. 

Name:	Query.png 
Views:	5 
Size:	15.2 KB 
ID:	16352

    My tables that are involved in the query are as below:

    Click image for larger version. 

Name:	ExchangeRateTable.png 
Views:	5 
Size:	12.7 KB 
ID:	16353Click image for larger version. 

Name:	LiftingTable.png 
Views:	4 
Size:	11.2 KB 
ID:	16354

    For your reference, my SQL is as below:

    SELECT DISTINCT Lifting.*, IIf([Lifting]![Lifting Date End] Between [ExchangeRate]![StartDate] And [ExchangeRate]![EndDate],[ExchangeRate]![Export Rate],IIf([Lifting]![Lifting Date End]>[ExchangeRate]![EndDate],"RATE UNAVAILABLE","NULL")) AS ExchangeR
    FROM ExchangeRate, Lifting;

    Can someone let me know where I've gone wrong or anything I missed?

    Thank you in advance! Any ideas / suggestions to go about this is HIGHLY appreciated!

    Best regards

  2. Best Answer
    Posted by healdem

    "
    Code:
    SELECT Lifting.[Lifting No], Lifting.Location, Lifting.[Lifting Date Start], Lifting.[Lifting Date End], Lifting.[Buyer / Client], [ExchangeRate].[Export Rate]
    FROM Lifting 
    LEFT JOIN ExchangeRate ON Lifting.[Lifting Date] between ExchangeRate.StartDate and ExchangeRate.EndDate
    Im not absolutely certain you can use the between syntax on a JOIN in Access, so you may instead have to use the older style 'theta' syntax which is deprecated

    Code:
    SELECT Lifting.[Lifting No], Lifting.Location, Lifting.[Lifting Date Start], Lifting.[Lifting Date End], Lifting.[Buyer / Client], [ExchangeRate].[Export Rate]
    FROM Lifting , ExchangeRate
    where Lifting.[Lifting Date] between ExchangeRate.StartDate and ExchangeRate.EndDate
    a couple of observations
    I don't see why you need to have an ID column in the lifting table, although I am assuming that the Lifting number is good enough to be the primary key (PK) all on its own. the only reason why it might not be is if you had a row come in, but didnt' have the relevant lifting number at the same time.

    Location should probably be in another table, and use the PK of that table in the lifting table
    likewise the currency code should also be constrained by using a separate table to define currencies they system can accept and use the PK of that table in the exchange rate table. by all means use the currency code (USD) as the PK/FK.

    however in this specific circumstance I would suggest that you store the local v alue of the transaction int her lift table, and NOT look up the exchange rate on the fly., the reason?
    at first glance doing that seems to and arguably does break the rules of normalisation
    well its a given value applicable to that transaction.
    it means that your system is less vulnerable to someone tinkering with the exchange rate after the transaction has been realised. obviously if you do get an exchange rate change AFTER a lift has been defined you need to work out what to do to make certain the transaction has the right value. thats probably a manual procedure done by a specific group of individuals cleared to do that.

    when naming tables or columns resist the temptation to use spaces and other symbols, make cure you don't use reserved words or symbols in table / column names. it will cause problems down the line. Get the list of reserved words /symbols from your version of Access from an on line source.
    if you want human friendly captionms / alebls then set those as part of the columns properties in the table definition (I forget the name of the property, heck it could be caption or title) but if you define the human friendly label there it will appear in all new forms and reports as the label for that column"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a join instead of an IIF.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    May 2015
    Posts
    4
    Quote Originally Posted by healdem View Post
    use a join instead of an IIF.
    Thank you for your prompt reply.

    I tried using join properties, it did eliminate the duplicates but it brings out the wrong records.

    Click image for larger version. 

Name:	Lifting Records (Joined).png 
Views:	1 
Size:	7.5 KB 
ID:	16356

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    And the join youve used is?
    Please post the sql here. In the query designer switch to sql view then paste the sql here
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    May 2015
    Posts
    4

    RE: healdem

    My sincere apologies.

    My SQL are as below:

    Code:
    SELECT DISTINCT Lifting.ID, Lifting.[Lifting No], Lifting.Location, Lifting.[Lifting Date Start], Lifting.[Lifting Date End], Lifting.[Buyer / Client], IIf([Lifting Date End] Between [ExchangeRate]![StartDate] And [ExchangeRate]![EndDate],[ExchangeRate]![Export Rate],IIf([Lifting]![Lifting Date End]>[ExchangeRate]![EndDate],"RATE UNAVAILABLE","NULL")) AS ExchangeRate
    FROM Lifting INNER JOIN ExchangeRate ON Lifting.ID = ExchangeRate.ID;
    However, regardless of which JOIN I use, may it be LEFT JOIN, RIGHT JOIN or INNERJOIN, the exchange rates values are incorrect.

    I thank you so much for your time on this matter.

    Kind regards.

  7. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    SELECT Lifting.[Lifting No], Lifting.Location, Lifting.[Lifting Date Start], Lifting.[Lifting Date End], Lifting.[Buyer / Client], [ExchangeRate].[Export Rate]
    FROM Lifting 
    LEFT JOIN ExchangeRate ON Lifting.[Lifting Date] between ExchangeRate.StartDate and ExchangeRate.EndDate
    Im not absolutely certain you can use the between syntax on a JOIN in Access, so you may instead have to use the older style 'theta' syntax which is deprecated

    Code:
    SELECT Lifting.[Lifting No], Lifting.Location, Lifting.[Lifting Date Start], Lifting.[Lifting Date End], Lifting.[Buyer / Client], [ExchangeRate].[Export Rate]
    FROM Lifting , ExchangeRate
    where Lifting.[Lifting Date] between ExchangeRate.StartDate and ExchangeRate.EndDate
    a couple of observations
    I don't see why you need to have an ID column in the lifting table, although I am assuming that the Lifting number is good enough to be the primary key (PK) all on its own. the only reason why it might not be is if you had a row come in, but didnt' have the relevant lifting number at the same time.

    Location should probably be in another table, and use the PK of that table in the lifting table
    likewise the currency code should also be constrained by using a separate table to define currencies they system can accept and use the PK of that table in the exchange rate table. by all means use the currency code (USD) as the PK/FK.

    however in this specific circumstance I would suggest that you store the local v alue of the transaction int her lift table, and NOT look up the exchange rate on the fly., the reason?
    at first glance doing that seems to and arguably does break the rules of normalisation
    well its a given value applicable to that transaction.
    it means that your system is less vulnerable to someone tinkering with the exchange rate after the transaction has been realised. obviously if you do get an exchange rate change AFTER a lift has been defined you need to work out what to do to make certain the transaction has the right value. thats probably a manual procedure done by a specific group of individuals cleared to do that.

    when naming tables or columns resist the temptation to use spaces and other symbols, make cure you don't use reserved words or symbols in table / column names. it will cause problems down the line. Get the list of reserved words /symbols from your version of Access from an on line source.
    if you want human friendly captionms / alebls then set those as part of the columns properties in the table definition (I forget the name of the property, heck it could be caption or title) but if you define the human friendly label there it will appear in all new forms and reports as the label for that column
    Last edited by healdem; 05-18-15 at 12:01.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #7
    Join Date
    May 2015
    Posts
    4
    Wow! That was an eye opener. Looks like I have to re-do my database. I guess this is what you call a poor data management. Sorry, this is my first attempt in creating a small database.

    Thanks a LOT for the pointers. I truly appreciate it.

    I shall take a look at the 'Theta' method you mentioned earlier after I get the basic tables sorted out.

    Thanks again!

  9. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you are happy with your current design, leave it as is. My observations are more about moving forward... on the next design consider using a more normalised approach.

    Rarely in the systems world is there one right answer. Theres usually lots of rkght answers, and what favours a particular right answer from others is ofoften perspective, experience, available time / tools / budget.

    If your current approach works for you use it.
    in the future dont use reserved words or symbols

    Unless you have come accross a serious problem with your current design, leave it. Its rare to have the time to revisit existing work
    take more time to design the data scheme (using the principals of normalisation)
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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