• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Query Syntax- Minimum 5% or 5 records depending on which is greater

INSERT INTO tblAuditResults ( LoadDate, [Order#], [Overall Score], BU, Employee, Name, SSN, [Position], [EE Grp Text], [Package Type], CalendarDay )
SELECT TOP 5 PERCENT tblOrder_Activity.Date_Added, tblOrder_Activity.[Order #], tblOrder_Activity.[Overall Score], tblOrder_Activity.BU, tblAuditPoolWeekly.Perner, tblOrder_Activity.Name, tblOrder_Activity.[SS#], tblAuditPoolWeekly.Position, tblAuditPoolWeekly.EmployeeText, tblOrder_Activity.[Package Type], tblAuditPoolWeekly.CalendarDay
FROM tblOrder_Activity INNER JOIN tblAuditPoolWeekly ON (tblOrder_Activity.[Overall Score] = tblAuditPoolWeekly.[Overall Score]) AND (tblOrder_Activity.[SS#] = tblAuditPoolWeekly.SSN)
GROUP BY tblOrder_Activity.Date_Added, tblOrder_Activity.[Order #], tblOrder_Activity.[Overall Score], tblOrder_Activity.BU, tblAuditPoolWeekly.Perner, tblOrder_Activity.Name, tblOrder_Activity.[SS#], tblAuditPoolWeekly.Position, tblAuditPoolWeekly.EmployeeText, tblOrder_Activity.[Package Type], tblAuditPoolWeekly.CalendarDay
HAVING (((tblOrder_Activity.[Overall Score])="Clear") AND ((tblOrder_Activity.BU)="Corp"));

Open in new window




I need to pull 5 records or 5% whichever is greater
0
kwarden13
Asked:
kwarden13
1 Solution
 
Gustav BrockCIOCommented:
Open a recordset using SELECT TOP 5 PERCENT ...
Count the records.

If count >= 5, run your insert query using TOP 5 PERCENT
If count < 5, run your insert query using TOP 5

/gustav
0
 
SharathData EngineerCommented:
Is this a similar to http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26940853.html ?

Can you try changing the comparative operators?
INSERT INTO tblAuditResults 
           (LoadDate,[Order#],[Overall Score],BU,Employee,Name,SSN,[Position],[EE Grp Text],[Package Type],CalendarDay)
SELECT * 
  FROM (SELECT * 
          FROM QUERY_P5 
         WHERE 5 > (SELECT COUNT(* ) 
                       FROM QUERY_P5) 
        UNION ALL 
        SELECT * 
          FROM QUERY_T5 
         WHERE 5 <= (SELECT COUNT(* ) 
                      FROM QUERY_P5)) AS t1;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now