Solved

Access 2007 Query

Posted on 2011-02-27
8
198 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
 
LVL 9

Expert Comment

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

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

896 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now