[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2007 Query

Posted on 2011-02-27
8
Medium Priority
?
243 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 40

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 40

Accepted Solution

by:
als315 earned 2000 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 40

Assisted Solution

by:als315
als315 earned 2000 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 40

Expert Comment

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

834 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