error in query

Cristina

Registered User.
Local time
Today, 10:35
Joined
Nov 18, 2010
Messages
67
Good evening,
I need some help with a query which has been working for years, and just now is giving me a error.
The query calculates income tax - Taxes: (SELECT T.[TaxAmount] FROM IncomeTax AS T WHERE [PayRoll Table].GROSS Between T.PayRangeLow And T.PayRangeHigh)

It is an append query and does not append the data. The error is "AT most one record can be returned by this subquery.

I switched to a select query. The query actually runs and shows the data but when you try to select the date to copy it give the same error.

Nothing was changed in the Database. The Income Tax table remained the same.

Any ideas why that would happen?

Thanks
Cristina
 
if it has always worked up to now, it suggests a problem with your data - I would check to see if your GROSS value can be between more that one T.PayRangeLow And T.PayRangeHigh pair
 
also it is usual to use select max/min/first/last or top 1 in a subquery that is not in the criteria - but if it has been working for years, I guess that is not the case, but you could try modifying your subquery to see it resolves the problem
 
Thanks. I checked the data, and there is no variance from the previous month's Gross. They are in the same range. Would you please guide me on how to modify the query. Somebody helped me with that last one. I am not good at SQL or complex querries
Thanks
 
normally you would use something like

Taxes: (SELECT max(T.[TaxAmount]) FROM IncomeTax AS T WHERE [PayRoll Table].GROSS Between T.PayRangeLow And T.PayRangeHigh)


or

Taxes: (SELECT TOP 1 T.[TaxAmount] FROM IncomeTax AS T WHERE [PayRoll Table].GROSS Between T.PayRangeLow And T.PayRangeHigh)
 
Thanks you so much. It worked like magic, wish I understood the reasoning behind the code.
Very grateful for your kind assistance.
Cristina
 
I bet you got someone just on the range limit. BETWEEN includes both ends and if you have ranges like

100,000-200,000 and
200,000-300,000

then BETWEEN will give 2 responses for someone making 200,000. Either specify

100,001-200,000 and
200,001-300,000

or use fx <= and > instead of BETWEEN

Max works because it just grabs the first fitting max randomly.

Update:

On reflection: If something did work but does not work now then mostly it is data. CJ_London did ask you to check data because his diagnosis that the data revealed the problem was right. But you didn't!

Next time, if someone asks you to check data, that means run your stuff with old data, and if that works then start replacing old by new data until you find which new record or records give problems.
 
Last edited:
Tax rates with both the minimum and maximum stored is a data normalization error. It should be stored with just the maximum. Any figure above this would inevitably be in the next range.

The query becomes a search for the minimum of those records above the supplied value.
 
Thanks everyone for your input.
I used the max with the suggested query by CJ_London and it worked well.
There was not a problem with the range.

Cristina Seawell
 
You've missed the point entirely.

I say that CJ_London's solution is a patch for the way you defined you ranges. Galaxiom says that that definition is wrong altogether and I'd agree with him.

If you do not understand it, then ask, instead of just dismissing the comments. If you do not learn from you own mistakes you are likely to repeat them or make worse ones in the future.
 
Hi,
I appreciate your input.
I think I understand, that you should not use Minimum and Maximum , as in the example of my Income Tax Table.
IncomeTax PayRangeLow PayRangeHigh TaxAmount $1,060.78 $1,060.97 $44.08 $1,060.98 $1,061.17 $44.13 $1,061.18 $1,061.37 $44.18 $1,061.38 $1,061.57 $44.23 $1,061.58 $1,061.77 $44.28 $1,061.78 $1,061.97 $44.33 $1,061.98 $1,062.17 $44.38 Did I understand correctly? Please be patient as sometimes I don't understand technical terms like data definition.
What would be the calculation in the field. Also what would happen to payroll data that fall below the minimum.
Cristina
 
I switched to a select query. The query actually runs and shows the data but when you try to select the date to copy it give the same error.
The fact that it runs as a select query doesnt mean it works.
The select query will only actively fetch the first so many records, unless you scroll to the bottom of the query to force Access to fetch all the data.

It is likely that if you do go to the end you will find your select query will give an error too.

By eliminating proper records (i.e. excluding keys you can see in your query) going step by step you can eventually narrow down your problem to only a few records.
 
Code:
 [FONT=Calibri][COLOR=#000000]PayRangeLow[/COLOR][/FONT] | [FONT=Calibri][COLOR=#000000]PayRangeHigh[/COLOR][/FONT] | [FONT=Calibri][COLOR=#000000]TaxAmount[/COLOR][/FONT] 
 [FONT=Calibri][COLOR=#000000]$1,060.78              [/COLOR][/FONT] [FONT=Calibri][COLOR=#000000]$1,060.97[/COLOR][/FONT]          [FONT=Calibri][COLOR=#000000]$44.08[/COLOR][/FONT] 
 [FONT=Calibri][COLOR=#000000]$1,060.98[/COLOR][/FONT]          [FONT=Calibri][COLOR=#000000]$1,061.17[/COLOR][/FONT]          [FONT=Calibri][COLOR=#000000]$44.13[/COLOR][/FONT] 
 [FONT=Calibri][COLOR=#000000]$1,061.18[/COLOR][/FONT]          [FONT=Calibri][COLOR=#000000]$1,061.37[/COLOR][/FONT]          [FONT=Calibri][COLOR=#000000]$44.18[/COLOR][/FONT] 
 [FONT=Calibri][COLOR=#000000]$1,061.38[/COLOR][/FONT]          [FONT=Calibri][COLOR=#000000]$1,061.57[/COLOR][/FONT]          [FONT=Calibri][COLOR=#000000]$44.23[/COLOR][/FONT] 
 [FONT=Calibri][COLOR=#000000]$1,061.58[/COLOR][/FONT]          [FONT=Calibri][COLOR=#000000]$1,061.77[/COLOR][/FONT]          [FONT=Calibri][COLOR=#000000]$44.28[/COLOR][/FONT] 
 [FONT=Calibri][COLOR=#000000]$1,061.78[/COLOR][/FONT]          [FONT=Calibri][COLOR=#000000]$1,061.97[/COLOR][/FONT]          [FONT=Calibri][COLOR=#000000]$44.33[/COLOR][/FONT] 
 [FONT=Calibri][COLOR=#000000]$1,061.98[/COLOR][/FONT]          [FONT=Calibri][COLOR=#000000]$1,062.17[/COLOR][/FONT]          [FONT=Calibri][COLOR=#000000]$44.38[/COLOR][/FONT]

A good reason to avoid including Minimum and Maximum in range tables. How easy it would be to mistype and make entries that left gaps between the ranges or overlapped them?

An overlap would cause affected records to be doubled up. A gap would drop records out completely.
 

Users who are viewing this thread

Back
Top Bottom