query not getting expected results

[slimjen]

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

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

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.

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:

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

[NTC]

I myself can't understand from your post as to which data is in which table as the starting point.

[June7]

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.

[slimjen]

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

qryCustRates
CustNum   State   Rate
1234             AL      $2.00
1234A          AL       $2.50
1234            NV       $3.00

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:

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

[June7]

I don't understand how you are getting $2.00 in the second record.

Would have to view query statement and/or the database.

[slimjen]

Ok. I'll mock up a sample. Give me a few minutes

[slimjen]

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

[June7]

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.

[slimjen]

Sorry; was off yesterday. I ran the queries but I don't want the Max. I am trying to get these results

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

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.

[slimjen]

Can someone help me please? Thanks

[June7]

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.

[slimjen]

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

[June7]

The data provided represents that situation? So what is wrong with the output? It looks like what you asked for.

[slimjen]

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.

[slimjen]

I ran this but now I'm not getting the 1234C in GA.

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);