How to count in Queries (1 Viewer)

randallst · Sep 23, 2017

Hi there,

It's been a while since I've used Access and I've gone a bit rusty (not that I was a master in the first place aha).

I've created a database to store certificates from our suppliers and I want to create a query that returns the following;

Count how many certificates arrived before delivery (<0)
Count how many certificates arrived after delivery (=>0)
Count how many certificates are yet to arrive (IsNull)

I want to sort this by supplier. The query is called 'Certificates Query' and the columns are called 'Supplier Name' and 'Day to get Cert'. I have attached a small screenshot of the 2 columns, hopefully will give you an idea of what I'm looking for.

Thanks in advance for any help you can give

Stu

CJ_London · Sep 23, 2017

not quite clear what you want returned but suggest in this case perhaps use a group by query and sum e.g.

Code:

SELECT Supplier, Sum(iif(getcert<0,1,0)) as before, Sum(iif(getcert>=0,1,0)) as after, Sum(iif(getcert is null,1,0)) as notarrived
FROM myTable
GROUP BY Supplier

MarkK · Sep 24, 2017

You can also see what happens if you just sum, and then negate, the boolean expressions directly...

Code:

SELECT Supplier, -Sum(getcert<0) as before, -Sum(getcert>=0) as after, -Sum(IsNull(getcert)) as notarrived
FROM myTable
GROUP BY Supplier

hth
Mark

randallst · Sep 24, 2017

CJ_London said:
not quite clear what you want returned but suggest in this case perhaps use a group by query and sum e.g.

Code:

SELECT Supplier, Sum(iif(getcert<0,1,0)) as before, Sum(iif(getcert>=0,1,0)) as after, Sum(iif(getcert is null,1,0)) as notarrived FROM myTable GROUP BY Supplier

Thanks to both of you for your replies, I tried CJ_London's response first and it returned what I was after! Thank you loads as I would have been sat here forever trying to work that out.

randallst · Sep 24, 2017

Okay I should have thought about this next request before I submitted my original question. I want to add a date range into the query (i.e. Certificate Stats based on deliveries between these dates, or show all deliveries if left blank).

I've got part of it working, but I can't seem to group the suppliers together like before as it's separating them out based on the 'Arrival Date'. Screenshots attached.

Sorry I'm rubbish at explaining, but hopefully along with the screenshots you may understand what I'm looking to achieve.

randallst · Sep 24, 2017

Sussed it!!

Code:

SELECT [Certificates Query].[Supplier Name], Sum(IIf([getcert]<0,1,0)) AS [Before Delivery], Sum(IIf([getcert]>=0,1,0)) AS [After Delivery], Sum(IIf([getcert] Is Null,1,0)) AS [Awaiting Certificate]
FROM [Certificates Query]
WHERE ([Certificates Query].[Arrival Date]) Between Nz([Forms]![Cert Arrival Popup]![From],#1/1/1930#) And Nz([Forms]![Cert Arrival Popup]![To],#1/1/2999#)
GROUP BY [Certificates Query].[Supplier Name]
ORDER BY [Certificates Query].[Supplier Name];

arnelgp · Sep 24, 2017

It would be generic not to use hard coded values:

SELECT [Certificates Query].[Supplier Name], Sum(IIf([getcert]<0,1,0)) AS [Before Delivery], Sum(IIf([getcert]>=0,1,0)) AS [After Delivery], Sum(IIf([getcert] Is Null,1,0)) AS [Awaiting Certificate]
FROM [Certificates Query]
WHERE ([Certificates Query].[Arrival Date]) Between Nz([Forms]![Cert Arrival Popup]![From],[Certificates Query].[Arrival Date]) And Nz([Forms]![Cert Arrival Popup]![To], [Certificates Query].[Arrival Date])
ORDER BY [Certificates Query].[Supplier Name];

How to count in Queries (1 Viewer)

randallst

Registered User.

Attachments

CJ_London

Super Moderator

MarkK

bit cruncher

randallst

Registered User.

randallst

Registered User.

Attachments

randallst

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread