Access query for SQL database to select records

We use Access to query a SQL database that is back end of our accounting software for reports.  In SQL there is a table--History that has shows payroll deductions.  A record for one employee's check will show the deductions as fields--ded1, ded2, ded3 etc.  Numbers are used as codes in these fields to indicate what type of deduction such as 401 k.  401k deduction uses "29".  However, that 29 will appear in different deduction fields depending on the employee--so that employee 1 might have "29" in ded1 and employee 2 might have "29" in ded2.  We want to query this table to return all employees that have 29 in any of these fields and no other employees.  When we insert 29 in the grid in Access select query design in all (ded1, ded2, etc.) this obviously does not work.  I do not have sophisticated Access skills--any help on how to design this would be appreciated.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

this should basically return what your looking for.
select * from <tablename> where <field1> = 29 or <field2> = 29

Open in new window

dur2348Author Commented:
If I want to query for "29" or "3",  can I add an or statement?
you can add as many or statments as you want.

if your checking 2 fields for two possible values then you can go with this.
select * from <tablename> where <field1> = 29 or <field2> = 29 or <field1> = 3 or <field2> = 3

Open in new window

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

dur2348Author Commented:
Is this to be used in SQL database or Access or does it matter?  If in Access would it be inserted in SQL view of query?
remember sql is a language, ms msql 2003, access, sybase are all management suits. 90% of sql commands work in all, some have additional features.

you can use it in SQL, and you should be able to use it in access if your connected to SQL properly.

SharathData EngineerCommented:
Provide some sample set in History table.
dur2348Author Commented:
Sample set attached.
SharathData EngineerCommented:
Where is the employee information is there? WHat is the expected output? If you can provide more details, its easy to write the query soon.
dur2348Author Commented:
I have attached another sample with two rows referring to two employees.  These are complete rows from table with dummy social security numbers and names.  You can see there are several fields for deductible codes and amounts.  We want to query employees that have 29 or 3 in any of the deductible code fields and the amounts in the corresponding deductible amount fields and we want to sum the total of the amount fields for each employee for whatever period of time we select by payend date.  I hope this is clear enough.  If not, please ask for more information.  Thanks for the help.
awking00Information Technology SpecialistCommented:
Perhaps something like the attached.
dur2348Author Commented:
I have used a nested if statement in Access query to return values for employees on a line by line basis.  The if statement has a limitation of 7 clauses that can be nested but so far we have only used 7 of the 10 deduction fields.  After designing first query I designed second query based on first to sum the values for each employee.  I was hoping for a more efficient method and one that would handle all 10 fields.  I will try awking00 suggestion.
dur2348Author Commented:
I am sorry not to post something sooner, but I have not been able to get back to this problem to try to use awking00's suggestion.  I am closing the question and splitting the points between experts.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.