Solved Query if Between lower and upper to return lower, returning all records

dullster

Member
Local time
Today, 14:55
Joined
Mar 10, 2025
Messages
211
I have a query that is supposed to find Marital Status in a table then between lower and upper limits and return lower limit, else 0. It is returning all the records. The query is QryFedTax1, pulling data from QryFedTax. I know there as some fields that can be removed form QryFedTax but I don't want to remove them until I find I don't need them for the calculations. Can any see why it is not just pulling the record I request. DB attached.
 

Attachments

I think we've been here before.

There seems to always be three rows that the join will return. Which row are you interested in?

I can't make any sense of your relationships. What is MyUCA? Why does QryFedTax1 have a hard coded 17 as the argument? I don't see any rows that would match so I'm not sure why the query is retuning any rows at all. Why is there a join to Fed Tax Table from both queries and WHY is the join DIFFERENT???????????

The joins to tblClabs and Fed Tax Table from tblDemoUCAs are both wrong. You should NEVER be joining data field to data field. Joins should always be data field to PK.
 
Last edited:
I think we've been here before.

There seems to always be three rows that the join will return. Which row are you interested in?
The results for this should be Married, no 2nd job (which is null0 and Adjusted Income should be 17100 and 49500. Returned results should be 17100.

I had this problem with State Taxes, which works. Federal taxes will not.
 
If income determines which of three rows, you need to add more criteria to the query.

Also, answer the other questions please. I might have updated my post while you were answering.
 
Why is qryFedTax1 including join to FedTaxTable? qryStateTax2 does not repeat join to StateTaxTable.
Should be consistent with naming. Why not names qryFedTax1 and qryFedTax2?
I would advise not to use spaces in object naming.
 
I think we've been here before.

There seems to always be three rows that the join will return. Which row are you interested in?

I can't make any sense of your relationships. What is MyUCA? Why does QryFedTax1 have a hard coded 17 as the argument? I don't see any rows that would match so I'm not sure why the query is retuning any rows at all. Why is there a join to Fed Tax Table from both queries and WHY is the join DIFFERENT???????????

The joins to tblClabs and Fed Tax Table from tblDemoUCAs are both wrong. You should NEVER be joining data field to data field. Joins should always be data field to PK.
Yes, this was updated while I was answering. MyUCA is the code that is on the CT report filed with the State. 17 is the ID in the tblDemoUCA table. The Fed Tax Table matches the UCA code and Marital Status. This DB was originally designed in a DOS program and the references where the MyUCA because there was no ID then. I didn't want to change to much if it worked. The Join in both queries in the same, it just doesn't say Marital Status ID because i have it doing a lookup of the word.
 
Why is qryFedTax1 including join to FedTaxTable? qryStateTax2 does not repeat join to StateTaxTable.
Should be consistent with naming. Why not names qryFedTax1 and qryFedTax2?
I would advise not to use spaces in object naming.
I will correct the tables to be qryFedTax1 and qryFedTax2 as you recommended.
 
The Join in both queries in the same,
No. In one case the join is on MyUCA and in the other it is on Marital Status. The two queries are incorrect. The joins are wrong and you shouldn't have two joins to the same table on different columns. The second query is probably not needed if you fix the first query AND include the correct range criteria.

and what are the Demo tables all about?
I would advise not to use spaces in object naming.
There was so much wrong with the object naming including inconsistencies which are worse than all the other problems, I felt no need to go there;)
 
No. In one case the join is on MyUCA and in the other it is on Marital Status. The two queries are incorrect. The joins are wrong and you shouldn't have two joins to the same table on different columns. The second query is probably not needed if you fix the first query AND include the correct range criteria.

and what are the Demo tables all about?

There was so much wrong with the object naming including inconsistencies which are worse than all the other problems, I felt no need to go there;)
The Demo tables were all named that in the DOS program. Why? I have no idea. I don't care for it but it's really not important enough to change it.
 
Just a comment.

A lot of your Tables contain Lookup Fields.
You should not use Lookup Fields in tables.
Google "The Evils of Lookup Fields in Access Tables"
 
Just a comment.

A lot of your Tables contain Lookup Fields.
You should not use Lookup Fields in tables.
Google "The Evils of Lookup Fields in Access Tables"
I have fixed all those and need to load some data to test reports.
 
I have the Federal Taxes fixed. I removed the 2nd job tax option from the table. Once I did that, it worked. The board members will never make enough money to pay State and Federal taxes. The only people that will are the Road Patrolman this would be their primary job. If they find they need more taxes taken out, they can use the Extra Federal Tax option.
 

Users who are viewing this thread

Back
Top Bottom