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

[dullster]

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?

 

[theDBguy]

The answer to your question is Yes. You can use DLookup() or use a non-equi join query.

 

[plog]

... 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_Doc_Man]

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.

 

[dullster]

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

 

[plog]

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.

 

[CJ_London]

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?