Link to home
Start Free TrialLog in
Avatar of dur2348
dur2348

asked on

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.
Avatar of funnymanmike
funnymanmike
Flag of Canada image

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

Open in new window

Avatar of dur2348
dur2348

ASKER

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

Avatar of dur2348

ASKER

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?
SOLUTION
Avatar of funnymanmike
funnymanmike
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sharath S
Provide some sample set in History table.
Avatar of dur2348

ASKER

Sample set attached.
sample.pdf
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dur2348

ASKER

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.
SAMPLE2.pdf
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dur2348

ASKER

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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial