Solved Query showing all records instead of just the ones that meet the criteria (1 Viewer)

[dullster]

I am working on finalizing a query to calculate Payroll taxes.

The last 2 queries be going to a form are QryStateTax which calculates with the 9 employees as it should. It then feeds that information to the qryCDpayrolltaxesWI which adds the St Tax Table to calculate the income if Marital Status matches the status from the tblEmployees and between Low Net Income and High Net Income. In that query it is bringing in all the records from the St Tax Table, which I dont' want. I want just the records that meet my criteria. I can narrow it down by doing a Is not null, but I don't want to do that. I'm guessing it has something to do with my relationships. What am I missing. DB is attached.

 

[tvanstiphout]

This relation is definitely wrong.
All relations need to be Enforced.
Most of the time they should be from PK to FK. So in this case I expect that each employee has a TaxID FK, which links to tblPayrollTaxes.TaxID, and the relation is enforced.

 

[plog]

You've got 9 records in qryStateTax--2 with MaritalStatus=2, 7 with MaritalStatus=1.
You've got 6 records in St Tax Table--3 with MaritalStatus=married, 3 with MaritalStatus=single.
You've JOINed qryStateTax to St Tax Table via MaritalStatus so all the records with equal statuses will find each other and make records:

2 MaritalStatus=2 in qryStateTax * 3 MaritalStatus=single in St Tax TAble = 6 records
7 MaritalStatus=1 in qryStateTax * 3 MaritalStatus=married in St Tax TAble = 21 records
21 + 6 = 27 records in qryCDpayrollTaxesWI

It works exactly like it should. How many records do you expect and which ones are the correct ones? I think you are missing a JOIN somewhere in there. Why wasn't St Tax Table included in qryStateTax to begin with? Seems like your expected results can be achieved in fewer steps.


Few notes:
1. Don't use spaces in table or field names. Makes coding and querying that much harder. Use camel case (LikeWhatIveDoneHere) or underscores.
2. Don't use lookups at the table level. Just use numbers and let it be, so much work trying to decipher what each marital status was in St Tax Table.
3. Don't use counterintuitive values. Married =1 and Single = 2? Man you make it hard on me.
4. No need for a table for binary values, just use a Yes/No field instead of a whole table. The Marital Status table is unnecessary if the only possibilities are Single and Married.

 

[The_Doc_Man]

Having a declared relationship is good, but choice of participating fields is crucial. As that relationship is diagrammed, you would potentially develop what is called a Cartesian JOIN with a LOT of members. Member plog showed you the potential numbers. Obviously, a WHERE clause would reduce the number of records, but relating two tables with more than two members each should NEVER be done on a field which has only two values - 1 & 2, for example, in your marital status. When you have two tables and you want to exploit some field that they have in common, it is best if the common field is something that doesn't have a lot of duplicate values.