Solved

Access 2007 Query

Posted on 2011-02-27
8
211 Views
Last Modified: 2012-05-11
Hi,
Attached SAMPLEV2.1.accdb .
I need to make a  query that do this:
list/give me ALL the rows in the table SubstractionList that the TOTAL-SUM of the (EmployeeReward+EmployerReward+CompensationReward) <0.02 WHERE FundCode AND ID is equal.
I appretiate having query in design view AND an vba code to learn from
0
Comment
Question by:drtopserv
  • 4
  • 2
  • 2
8 Comments
 
LVL 39

Expert Comment

by:als315
ID: 34995012
Sample was not attached
0
 

Author Comment

by:drtopserv
ID: 34995024
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 34995071
the following query should work:-

SELECT * from SubstractionList where (EmployeeReward+EmployerReward+CompensationReward) <0.02 and FunDCode=ID

Open in new window



0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 9

Expert Comment

by:mayank_joshi
ID: 34995080
The access file containing the required query as 'SQL' is attached.
SAMPLEV2.1.accdb
0
 

Author Comment

by:drtopserv
ID: 34995931
what i was saying by :
FundCode AND ID  is:
search through rows and check :
if the rows have same fundcode and same ID , go sum : EmployeeReward+EmployerReward+CompensationReward
then if the totalsum <0.02 then don`t display theses rows.
otherwise bring me it.
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 34996794
Try this query:
SELECT SubstractionList.ID, SubstractionList.FundCode, Sum([EmployeeReward]+[EmployerReward]+[CompensationReward]) AS Total_Sum
FROM SubstractionList
GROUP BY SubstractionList.ID, SubstractionList.FundCode
HAVING (((Sum([EmployeeReward]+[EmployerReward]+[CompensationReward]))>=0.02));

0
 
LVL 39

Assisted Solution

by:als315
als315 earned 500 total points
ID: 34996818
If first query will be saved as Q1, second will give all records with your condition:
SELECT SubstractionList.*
FROM Q1 INNER JOIN SubstractionList ON (Q1.FundCode = SubstractionList.FundCode) AND (Q1.ID = SubstractionList.ID);
0
 
LVL 39

Expert Comment

by:als315
ID: 34996864
Both queries
SAMPLEV2.1.accdb
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question