asked on

Query Syntax

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 return a minimum of 5% or 5 records, whichever is greater

Aaron Tomosky

You can do
select top 5
Select top 5 percent
And check the rowcount and keep the larger

Or select top 5 count(column) to return a number and and if to run the actual query for the greater number.

This is just an outline. I'm sure someone will give you working code in a few min.

kwarden13

ASKER

Would this all be in 1 query or 2 querys?

Guy Hengel [angelIII / a3]

I can think of only 1 solution: have 2 views (querydef), 1 with TOP 5 and 1 with TOP 5 PERCENT .
say they are named QUERY_T5 and QUERY_P5

then, you could do like this:

INSERT INTO tblAuditResults ( LoadDate, [Order#], [Overall Score], BU, Employee, Name, SSN, [Position], [EE Grp Text], [Package Type], CalendarDay )
SELECT * FROM QUERY_P5 WHERE 5<= (SELECT count(*) FROM QUERY_P5)
UNION ALL
SELECT * FROM QUERY_T5 WHERE 5> (SELECT count(*) FROM QUERY_P5);

Open in new window

kwarden13

ASKER

Also, I was thinking I could write an if/then statement to say:

IF the table has more then 100 records return 5%, if not then return 5 Records.

I have been trying to figure this out for hours. SO PLEASE HELP! ANY SUGGESTIONS ARE APPRECIATED.

kwarden13

ASKER

Angelllll-

I keep getting a missing syntax error with the Select * statement

kwarden13

ASKER

Any other suggestions out there?

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kwarden13

ASKER

so what exactly does your query say/do Sharath 123. It works, I just want to be sure it's what I need/want.

Sharath S

I wrapped both the queries Query1 UNION ALL Query2 in a sub-query so that you ended up with one derived table as t1. Now insert the data from t1 to tblAuditResults.

kwarden13

ASKER

ok thank you!!!