Query - Group by, Count if ,Create a new field, If>x,"Good","Bad"

[oriels]

Hello


I have a table like this:
Customer ID, Order ID, Date.
(It can contain two rows or more with the same customer ID)

So I need a query that will do that:
If customer made more then 2 orders, in July 15 (for example), then write in a new cell "Good" or "Bad".

I succeed to do it with 3 queries. But, I think it possible with 2 queries or even to do it with one query. Does anyone have any idea how to do it?

The way i did it with 3 queries:

First query:"1" (filter for get july orders)
Customer ID
Order Date - criteria (range of July 15).

Second query:"2" (sort by a group per customer and count the orders in that date)
Customer ID - Group by. - query 1
Order Date - count - query 1.

Third query :"3" ( actual check )
Customer ID - query 2
Count - (just for display reasons -- not necessary)
Good or Bad: if query 2 date count is more then 2 so "good" else "bad".

===
I thought to make "if" term in query 2 but it not worked for me.
pleas i need it for homework.

Thank you all!

[CJ_London]

query 1

SELECT CustomerID, count(*) As OrderCount
FROM myTable
WHERE month(mydate)=7 and year(mydate)=2015
GROUP BY CustomerID

query 2

SELECT *, iif(ordercount>1,"Good","Bad") as Status
FROM Query1

[ssanfu]

Should not use spaces, punctuation or special characters (underscore is the exception) in object names.

"Date" is a reserved word in Access and shouldn't be used as an object name.

[oriels]

Should not use spaces, punctuation or special characters (underscore is the exception) in object names.

"Date" is a reserved word in Access and shouldn't be used as an object name.

thank you .!

query 1

SELECT CustomerID, count(*) As OrderCount
FROM myTable
WHERE month(mydate)=7 and year(mydate)=2015
GROUP BY CustomerID

query 2

SELECT *, iif(ordercount>1,"Good","Bad") as Status
FROM Query1

In Query 1 - I have just (*) no "Count(*)" in "myTable" do i need to write it? can you sxplain me how to do it?
and also if i make "total" it not allow me to "gruop by".

Ok I got it and it worked!!!
Thank you very very much!

[CJ_London]

In Query 1 - I have just (*) no "Count(*)" in "myTable" do i need to write it? can you sxplain me how to do it?

most help for queries uses sql as examples etc.

Using the query designer generates sql and this can be seen by going to the sql window which can be found in the dropdown on the left of the ribbon.

for future questions you may have, copy and paste this code into your post (no screenshots) - it tells us a lot more than you describing it. It also means that responders will usually use your table and field names so you can simply copy and paste back and return the query design window to see what it looks like. This makes it easier for everyone. The number of times the OP retypes the code manually, makes a mistake and then says 'it doesn't work' is very frustrating

I fully agree with ssanfu - never understood why users use spaces - means you have 3 extra characters to type each time and increased opportunity for errors. That is what the caption property is for

here is a link to access reserved words https://support.office.com/en-us/art...7-da237c63eabe