Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    query not getting expected results

    All, using Access 2010. I have a couple of queries below:

    Code:
    CustNum   CustEx   State  Acct.                                 CustNum   State    Rate
    1234          1234    AL      100                                   1234          AL       $2.00
    1234A        1234A  AL      100                                   1234A        AL       $2.50
    1234          1234    NV     102                                   1234B        NV       $3.00
    I am trying to create another query to get this result
    Code:
    CustNum   CustEx  State     Rate        Acct.
    1234           1234     AL        $2.00      100
    1234          1234A    AL        $2.50      100
    1234          1234B    NV        $3.00      102
    But I’m getting this result.
    Code:
    CustNum   CustEx  State      Rate        Acct.
    1234           1234     AL        $2.00     100
    1234          1234A    AL        $2.00     100
    1234          1234B    NV        $3.00     102
    I tried an IIF statement:
    Code:
    rates: IIF(LEN([CustRates].[CustNum]>4,[CustRates].[CustNum].[rate],[ CustRates].[ CustNum].[rate]))
    but I’m getting “wrong number of arguments” error
    What am I doing wrong? Can anyone help please? Thanks

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    I myself can't understand from your post as to which data is in which table as the starting point.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    That expression should error because of misplaced paren:

    rates: IIF(LEN([CustRates].[CustNum])>4, [CustRates].[CustNum].[rate], [CustRates].[CustNum].[rate])

    Has the same reference in both True and False results - the expression accomplishes nothing.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Quote Originally Posted by June7 View Post
    That expression should error because of misplaced paren:

    rates: IIF(LEN([CustRates].[CustNum])>4, [CustRates].[CustNum].[rate], [CustRates].[CustNum].[rate])

    Has the same reference in both True and False results - the expression accomplishes nothing.
    I was trying to say; if qryCustRates.CustNum len is greater than 4 for the same state; return whatever the rate for that record is. If not return the rate less len less than 5


    Code:
    qryCustRates
    CustNum   State   Rate
    1234             AL      $2.00
    1234A          AL       $2.50
    1234            NV       $3.00
    Code:
    qryCustAcct
    CustNum   CustEx   State   Acct.                                  
    1234           1234      AL       100                               
    1234          1234A     AL      100                                
    1234          1234B     NV     102
    The result I’m trying to get is this:

    Code:
    CustNum    Rate   CustEx  State     Acct.
    1234           $2.00   1234     AL         100
    1234           $2.50   1234A    AL       100
    1234           $3.00   1234B    NV      102
    Sorry for the confusion. Thanks

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I don't understand how you are getting $2.00 in the second record.

    Would have to view query statement and/or the database.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Ok. I'll mock up a sample. Give me a few minutes

  7. #7
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    First: qryTestFirst-to strip the fifth character to find matches in the next step
    Second: qryTestSecond-to join the two tables
    Third: qryTestFinalW/acct-to join the acct
    Last: qryWrongRateFor123A-trying to add back the rate for a final result (Stuck here)
    If anyone can show me a way to get the end result without going through all this; please show me. Thanks
    Attached Files Attached Files

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Might have it with 3 queries.

    Query1
    SELECT Left([CustNum],4) AS Customer, tblCustRate.CustNum, tblCustRate.State, tblCustRate.Rate FROM tblCustRate;

    Query2
    SELECT tblCustAcct.CustNum, tblCustAcct.CustEx, tblCustAcct.State, tblCustAcct.Acct, tblCustAcct.Year, Query1.Rate, tblCustRate.State, tblCustRate.Rate, IIf(Len([tblCustAcct].[CustEx])>4,[Query1].[rate],[tblCustRate].[rate]) AS rates
    FROM tblCustRate RIGHT JOIN (Query1 RIGHT JOIN tblCustAcct ON (Query1.Customer = tblCustAcct.CustNum) AND (Query1.State = tblCustAcct.State)) ON tblCustRate.State = Query1.State
    WHERE (((tblCustAcct.CustNum)=[tblCustRate].[CustNum]));

    Query3
    SELECT Query2.CustNum, Query2.CustEx, Query2.Acct, Query2.Year, Max(Query2.rates) AS MaxOfrates
    FROM Query2
    GROUP BY Query2.CustNum, Query2.CustEx, Query2.Acct, Query2.Year;

    If that doesn't work, maybe need VBA or db redesign.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Sorry; was off yesterday. I ran the queries but I don't want the Max. I am trying to get these results

    Code:
    CustNum    Rate   CustEx  State     Acct.
    1234           $2.00   1234     AL         100
    1234           $2.50   1234A    AL       100
    1234           $3.00   1234B    NV      102

    Quote Originally Posted by June7 View Post
    Might have it with 3 queries.

    Query1
    SELECT Left([CustNum],4) AS Customer, tblCustRate.CustNum, tblCustRate.State, tblCustRate.Rate FROM tblCustRate;

    Query2
    SELECT tblCustAcct.CustNum, tblCustAcct.CustEx, tblCustAcct.State, tblCustAcct.Acct, tblCustAcct.Year, Query1.Rate, tblCustRate.State, tblCustRate.Rate, IIf(Len([tblCustAcct].[CustEx])>4,[Query1].[rate],[tblCustRate].[rate]) AS rates
    FROM tblCustRate RIGHT JOIN (Query1 RIGHT JOIN tblCustAcct ON (Query1.Customer = tblCustAcct.CustNum) AND (Query1.State = tblCustAcct.State)) ON tblCustRate.State = Query1.State
    WHERE (((tblCustAcct.CustNum)=[tblCustRate].[CustNum]));

    Query3
    SELECT Query2.CustNum, Query2.CustEx, Query2.Acct, Query2.Year, Max(Query2.rates) AS MaxOfrates
    FROM Query2
    GROUP BY Query2.CustNum, Query2.CustEx, Query2.Acct, Query2.Year;

    If that doesn't work, maybe need VBA or db redesign.

  10. #10
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Can someone help me please? Thanks

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I do see that output in with the suggested queries. Except for the additional row for 1234C which you don't deal with in your example.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Yes; 1234C is GA and should have a rate of 4.00. Sorry didn't list it because the problem I was getting was is if there were CustNum in same states with different rates

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    The data provided represents that situation? So what is wrong with the output? It looks like what you asked for.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    the rates are not matching up. It's listing duplicates. In the query 3 the results are grouped and getting the max rate for matching custex with same states.

  15. #15
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    I ran this but now I'm not getting the 1234C in GA.

    Code:
    SELECT tblCust.CustNum, tblCust.CustEx, tblCustAcct.Acct, tblCustAcct.State, tblCustAcct.year, tblCustRate.Rate
    FROM (tblCust INNER JOIN tblCustAcct ON (tblCust.CustEx = tblCustAcct.CustEx) AND (tblCust.CustNum = tblCustAcct.CustNum))
    
    INNER JOIN tblCustRate ON (tblCustAcct.State = tblCustRate.State) AND (tblCustAcct.CustEx = tblCustRate.CustNum);

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query not returning expected results
    By MarcieFess in forum Queries
    Replies: 3
    Last Post: 10-18-2013, 05:28 PM
  2. Query not returning all expected results
    By amenitytrust in forum Queries
    Replies: 6
    Last Post: 11-05-2012, 07:13 AM
  3. Replies: 13
    Last Post: 01-13-2011, 10:15 AM
  4. Replies: 3
    Last Post: 07-21-2010, 02:41 AM
  5. Replies: 1
    Last Post: 07-10-2010, 09:56 PM

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