[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access 2007 Query

Posted on 2011-02-27
8
Medium Priority
?
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

656 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