Need a query to pull only 30 items

Select Top 30 ...

I have no idea where to define that at...

I don't think that's what you need. That will only pull the first 30 items total, not 30 from each store. That is what you want, right? 30 from each store?

Lee

Correct. 30 from each store

Try this:

SELECT a.Store_Number, a.Customer_Email_Address
FROM dbo_Invoice_Tb a
WHERE a.Customer_Email_Address IN (
SELECT Top 30 b.Customer_Email_Address FROM dbo_Invoice_Tb b
WHERE b.Store_Number = a.Store_Number)
ORDER BY a.Store_Number, a.Customer_Email_Address ASC;

Here's how to do it for one store:

SELECT Top 30 dbo_Invoice_Tb.Customer_Email_Address
FROM dbo_Invoice_Tb
WHERE (((dbo_Invoice_Tb.Customer_Email_Address) Like "*@*") AND ((dbo_Invoice_Tb.Invoice_Date) Between Date()-7 And Date()-1))
AND dbo_Invoice_Tb.Store_Number = 1234
ORDER BY dbo_Invoice_Tb.Customer_Email_Address DESC;


... You'll have to replace 1234 with a valid store number.

GRayL - The query runs but does not return data and does not time out. I tried it twice and aborted after 5 minutes

PDEBAETS - It works great for 1 store but I need it to do it for all 34 stores in one query

You may need a VBA function or subroutine to do what you want. What is it you are after? What is your ultimate goal here? It may be easier to address the full scope of what you are doing rather than this small piece.

I want to send 30 random customers per week per store an email to fill out a survey about there recent visit at that store. We capture about 50% of our customers email addresses so using them all would be more potential responses than what we want right now.

What I am trying to do is create a query that would give me these email address' which I could than export into an excel file which the mass email software we use can import the data from.

Try this.

My Google query: sql top from group

Copied and modified from first comment here http://www.bennadel.com/blog/1114-Selecting-Top-X-From-Each-Group.htm

SELECT tI3.Store_Number, tI3.Customer_Email_Address, qI.ranknum
FROM dbo_Invoice_Tb AS tI3 INNER JOIN
  (SELECT tI1.id, COUNT(*) AS ranknum
   FROM dbo_Invoice_Tb AS tI1 INNER JOIN dbo_Invoice_Tb AS tI2 ON tI1.id = tI2.id
   WHERE tI1.Customer_Email_Address Like "*@*" AND tI1.Invoice_Date) Between Date()-7 And Date()-1
   GROUP BY tI1.id HAVING COUNT(*) <= 3) AS qI)
  ON tI3.id = qI.id
ORDER BY tI3.Store_Number, tI3.Customer_Email_Address DESC;

Select allOpen in new window

LEE555J5 - I am getting a "Syntax error in FROM clause."

Remove the ) after the qI

Removed the ) and I am still getting the Syntax error

In the subquery, remove the ) before Between. I tried to delete all those () Access adds in. They make it very cluttered and confusing.

To make sure the subquery works, create a new query, switch to SQL View, paste in the subquery, and try to run it.

Also, does your table dbo_Invoice_Tb have an field named "id"?

Change the statement in the subquery to

"id" was in the example I found and modified.

You have lost me now. I am a click and drag access user so the SQL is a bit confusing. I do not have an ID field in this table. Here is a screenshot of the table

Thanks glad to help.