to return a ID value if criteria is met (1 Viewer)

dullster

Member
Local time
Yesterday, 20:09
Joined
Mar 10, 2025
Messages
83
Is there a way to return an ID from another table if criteria is met. Ex. If an Employee has 12 pay periods, taxes are filed individually, Minimum is >0 and <2500 (All of these fields would be in the Tax table) and return the TaxID to the Employee table that matches?

Or to have a query that answers these questions and returns the results?
 
The answer to your question is Yes. You can use DLookup() or use a non-equi join query.
 
... and return the TaxID to the Employee table that matches

Yes, but 'return' isn't the right verb or concept. Instead it would be more like identifying and using.

To help specifically, we would need a better understanding of how your tables are laid out and what the ultimate aim is of identifying the ID. Are you opening a specific report? Are you exporting a list of people that matches that criteria? Whats the ultimate end goal?
 
The answer to your question depends on how you attempt to do such a thing. There are functions that you could call via VBA to do this one value at a time, or there is a way to write a query that does this for every ID that matches. Tell us which way you wanted to go.
 
I want the

tblEmployees.Salary Pay Period of 12 or 26 to match tblpayrolltaxes.Payroll Period,

tblpayrolltaxes.Filing Status to match tblEmployees.Filing Status and

tblEmployees.Total of Pay Check to be between the tblpayrolltaxes.Minimum Income and Maximum Income.

Table screen shots attached
 

Attachments

I don't understand your expected results. But that's easy to fix. Just tell me what data you expect to end up with based on the data in your .pdf.

No need for an explanation or any words, just give me the data you expect the query to return. What do the results look like? Again, no explanation, just data.
 
Agree with plog.simple in principle until you look at the taxes table. So I would add what would be the expected return for an employee on a pay period 12,and a paycheck of $2244? Or a paycheck of $5000?

And an employee on a pay period of 26 and a paycheck of $3000?
 
I think i was making this harder than it needed to be. I woke up in the middle of the night and created a query with multiple criterias and the 12 or 26 pay periods to match and it worked perfectly.

The pay check amounts would fall between the minimum and maximum, the joint filing would match and the 12 or 26 pay periods would match. So 3 criterias had to match to calculate the tax level. The query with multiple criterias with each requirement worked perfectly.
 
If Basic Salary is between the Min and the Max, I want it to Calculate the tax. I have this statement, when i hit run I get, "The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or indentifier" and it highlights "between".

Taxes: IIf([tblEmployees]![Basic Salary],between [tblpayrolltaxes]![Min] and [tblpayrolltaxes]![Max], [tblEmployees]![Total of Pay Check] * [tblpayrolltaxes]!]Percent of Withholding])

Do you see anything wrong?

I just realized I had the bracket backwards in front of Percent of Withholding, which i corrected. I runs if I put "If" in front of "Between" but then i get the error Enter Parameter Value, If. It inserts brackets around If.

Taxes for 25520: IIf([tblEmployees]![Basic Salary], [If] Between [tblpayrolltaxes]![Min] And [tblpayrolltaxes]![Max],[tblEmployees]![Total of Pay Check]*[tblpayrolltaxes]![Percent of Withholding])
 
Last edited:
Comma before between.
No value for false part of iif()
If i take out the iif(), i get all kinds of errors. I now have this now and it is calculating for everyone, not just the Salaries that are between the Min and Max. I'm thinking maybe i need another set of () some where.

Taxes for 25520: IIf([tblEmployees]![Basic Salary] Between [tblpayrolltaxes]![Min] And [tblpayrolltaxes]![Max],([tblpayrolltaxes]![Min]-[tblEmployees]![Basic Salary]/[tblEmployees]![Salary Pay Period]),([tblEmployees]![Total of Pay Check]*[tblpayrolltaxes]![Percent of Withholding]))
 
Last edited:
I tried removing the last part that calculates tax and my results didn't change.

Taxes for 25520: IIf([tblEmployees]![Basic Salary] Between [tblpayrolltaxes]![Min] And [tblpayrolltaxes]![Max],[tblpayrolltaxes]![Min]-[tblEmployees]![Basic Salary]/[tblEmployees]![Salary Pay Period])
 
In post #8 you used the phrase 'worked perfectly' twice. Now, 6 hours later you are posting for more help.

Did you solve your initial issue (the one from post #1) and we are looking at a new one or did you simply go 10 feet down a path you think is a solution to your initial issue and get stuck again?

I still vote you give us the expected results of your query like I asked for in post #6.
 
If i take out the iif(), i get all kinds of errors. I now have this now and it is calculating for everyone, not just the Salaries that are between the Min and Max. I'm thinking maybe i need another set of () some where.

Taxes for 25520: IIf([tblEmployees]![Basic Salary] Between [tblpayrolltaxes]![Min] And [tblpayrolltaxes]![Max],([tblpayrolltaxes]![Min]-[tblEmployees]![Basic Salary]/[tblEmployees]![Salary Pay Period]),([tblEmployees]![Total of Pay Check]*[tblpayrolltaxes]![Percent of Withholding]))
Put your code between code tags. :(
It makes it easier to read.

Here is what you have for criteria being True
Code:
([tblpayrolltaxes]![Min]-[tblEmployees]![Basic Salary]/[tblEmployees]![Salary Pay Period])
Here is what you have if False
Code:
([tblEmployees]![Total of Pay Check]*[tblpayrolltaxes]![Percent of Withholding])
So how do you know which is which? :(

I would have zero for False, or use another query to only get what comes between those dates. Then run that your query against that one.
 
Taxes for 25520: IIf([tblEmployees]![Basic Salary] Between [tblpayrolltaxes]![Min] And [tblpayrolltaxes]![Max],[tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Min])
In post #8 you used the phrase 'worked perfectly' twice. Now, 6 hours later you are posting for more help.

Did you solve your initial issue (the one from post #1) and we are looking at a new one or did you simply go 10 feet down a path you think is a solution to your initial issue and get stuck again?

I still vote you give us the expected results of your query like I asked for in post #6.
My initial issue was splitting the Fed by different income levels which was splitting Fed taxes by filing Joint and Individual.

Now, i digging deeper to split out the State payroll tax by salary levels. So I solved my initial issue and I'm going deeper.

State taxes are:
If between 0 - 12760, tax is .0345. I have this one working.
If between 12760 - 25520, tax is .0465, this is where I at that is not calculating right. It i get past this one the next one, which is -
If between 25520 - 280950, tax is .053, this should fall right into place.

I hope this helps.
 
Put your code between code tags. :(
It makes it easier to read.

Here is what you have for criteria being True
Code:
([tblpayrolltaxes]![Min]-[tblEmployees]![Basic Salary]/[tblEmployees]![Salary Pay Period])
Here is what you have if False
Code:
([tblEmployees]![Total of Pay Check]*[tblpayrolltaxes]![Percent of Withholding])
So how do you know which is which? :(

I would have zero for False, or use another query to only get what comes between those dates. Then run that your query against that one.
I corrected it to read, Taxes: IIf([tblEmployees]![Basic Salary] Between [tblpayrolltaxes]![Min] And [tblpayrolltaxes]![Max],[tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Min],0)

It is still calculating people out side of the Min and Max
 
I corrected it to read, Taxes: IIf([tblEmployees]![Basic Salary] Between [tblpayrolltaxes]![Min] And [tblpayrolltaxes]![Max],[tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Min],0)
This looks like it's in a query.

If so, you do not use a bang(!) to qualify tables/fields.

Your expression should be:
Code:
Taxes: IIf(
  tblEmployees.[Basic Salary] BETWEEN tblpayrolltaxes.[Min] AND tblpayrolltaxes.[Max],
  tblEmployees.[Basic Salary] - tblpayrolltaxes.[Min],
  0
)

I'm sure you have been advised before that you should try and avoid using spaces and reserved words (like Min and Max) in field and table names.

I don't see how the above expression relates to your tax amounts at all.
 
This looks like it's in a query.

If so, you do not use a bang(!) to qualify tables/fields.

Your expression should be:
Code:
Taxes: IIf(
  tblEmployees.[Basic Salary] BETWEEN tblpayrolltaxes.[Min] AND tblpayrolltaxes.[Max],
  tblEmployees.[Basic Salary] - tblpayrolltaxes.[Min],
  0
)

I'm sure you have been advised before that you should try and avoid using spaces and reserved words (like Min and Max) in field and table names.

I don't see how the above expression relates to your tax amounts at all.
I pasted that code in, i still get a calculation for people not in between Min and Max. Should i change my Min and Max to Minimum and Maximum to Top and Bottom?
 
SQL:
SELECT
  e.EmployeeID,
  e.[Total of Pay Check],
  e.[Salary Pay Period],
  t.Percent
FROM tblEmployees e
INNER JOIN tblpayrolltaxes t
        ON e.[Filing Status] = t.[Filing Status]
       AND e.[Total of Pay Check] BETWEEN t.[Minimum Inc] AND t.[Maximum Inc]
Change the field names to fit according to your pdf you posted in Post #5
 

Users who are viewing this thread

Back
Top Bottom