Need Help with AND query

[KAZSTREBOR]

Hi there. I'm not sure if it is even possible to do what I want in access. I need to create a query that will count the number of records if two conditions are met. I have 2 columns "Date Order Placed" and "Date Order Received". I need to be able to report the amount of orders placed in each month and the amount of those orders received in each month. IE- Count of orders placed in Jan AND received in Jan, orders placed in Jan AND received in Feb, and so on. Everything I try gives me the count for one column or the other but not both. Any help is much appreciated.

 

[Micron]

If there were 30 orders place in January and 20 received in January and you want the stats for January only, you expect to see
Placed = 30, Received = 20?
Probably a UNION query would be simple enough and provide that.

EDIT - Actually, a Totals query is probably more suitable.

 

[Micron]

Something along the lines of

SELECT Count(Tbl1.OrderDate) AS CountOfOrderDate, Count(Tbl1.ReceivedDate) AS CountOfRecDate
FROM Tbl1;

using your table and field names of course.

 

[jackd]

I ran a mockup with some sample data. It involves 1 table and 2 queries. Results are below.

Table SampOrders
SampOrders SampOrders

ID	OrderDate	ReceivedDate
1​	21-Jan-21​	27-Jan-21​
2​	10-Jan-21​	23-Jan-21​
3​	15-Jan-21​	07-Feb-21​
4​	17-Jan-21​	04-Mar-21​
5​	03-Feb-21​	16-Feb-21​
6​	14-Feb-21​	31-Mar-21​
7​	15-Jan-21​	05-Apr-21​
8​	12-Feb-21​	18-May-21​
9​	06-Apr-21​	23-Apr-21​
10​	08-Feb-21​	12-Feb-21​
11​	09-Feb-21​	08-Apr-21​
12​	12-Jan-21​	23-Jan-21​
13​	07-Apr-21​	17-Apr-21​
14​	11-Feb-21​	06-May-21​

Query1 OrderedReceivedQ

SELECT MonthName(Month(orderdate)) AS Ordered
, MonthName(Month(receiveddate)) AS Received
, 1 AS Num, Month([OrderDate]) AS O
, Month([receiveddate]) AS R
FROM samporders
ORDER BY Month([OrderDate])
, Month([receiveddate]);

Query2 OrderedReceivedFinalCountsQ

SELECT OrderedReceivedQ.Ordered
, OrderedReceivedQ.Received
, Count(OrderedReceivedQ.Num) AS CountOfNum
FROM OrderedReceivedQ
GROUP BY OrderedReceivedQ.Ordered
, OrderedReceivedQ.Received
, OrderedReceivedQ.O
, OrderedReceivedQ.R
ORDER BY OrderedReceivedQ.O
, OrderedReceivedQ.R;

Tested Output:

OrderedReceivedFinalCountsQ OrderedReceivedFinalCountsQ

Ordered	Received	CountOfNum
January	January	3​
January	February	1​
January	March	1​
January	April	1​
February	February	2​
February	March	1​
February	April	1​
February	May	2​
April	April	2​

 

[KAZSTREBOR]

I ran a mockup with some sample data. It involves 1 table and 2 queries. Results are below.

Table SampOrders
SampOrders SampOrders

ID	OrderDate	ReceivedDate
1​	21-Jan-21​	27-Jan-21​
2​	10-Jan-21​	23-Jan-21​
3​	15-Jan-21​	07-Feb-21​
4​	17-Jan-21​	04-Mar-21​
5​	03-Feb-21​	16-Feb-21​
6​	14-Feb-21​	31-Mar-21​
7​	15-Jan-21​	05-Apr-21​
8​	12-Feb-21​	18-May-21​
9​	06-Apr-21​	23-Apr-21​
10​	08-Feb-21​	12-Feb-21​
11​	09-Feb-21​	08-Apr-21​
12​	12-Jan-21​	23-Jan-21​
13​	07-Apr-21​	17-Apr-21​
14​	11-Feb-21​	06-May-21​

Query1 OrderedReceivedQ

SELECT MonthName(Month(orderdate)) AS Ordered
, MonthName(Month(receiveddate)) AS Received
, 1 AS Num, Month([OrderDate]) AS O
, Month([receiveddate]) AS R
FROM samporders
ORDER BY Month([OrderDate])
, Month([receiveddate]);

Query2 OrderedReceivedFinalCountsQ

SELECT OrderedReceivedQ.Ordered
, OrderedReceivedQ.Received
, Count(OrderedReceivedQ.Num) AS CountOfNum
FROM OrderedReceivedQ
GROUP BY OrderedReceivedQ.Ordered
, OrderedReceivedQ.Received
, OrderedReceivedQ.O
, OrderedReceivedQ.R
ORDER BY OrderedReceivedQ.O
, OrderedReceivedQ.R;

Tested Output:

OrderedReceivedFinalCountsQ OrderedReceivedFinalCountsQ

Ordered	Received	CountOfNum
January	January	3​
January	February	1​
January	March	1​
January	April	1​
February	February	2​
February	March	1​
February	April	1​
February	May	2​
April	April	2​

This worked great, thank you!

 

[jackd]

You are very welcome. Good luck with your project.