Search query not showing all results/results with a 0 count

[ScaniaJP]

Hello,

I am creating a database that shows the amount of transactions done by employees of my company.
I use one table from which I filter the things I need via a query which are the date of yesterday, employee number and their name.

The criteria I use is under the employee number and I enter

Like [Pick1] Or Like [Pick2]

to fill in whose numbers I need.
After this I use a second query to give me the count of transactions per employee number.

The problem now is that if Pick1 has for example 10 transactions and Pick2 has none I only see Pick1 having 10 but Pick2 doesn't show at all.

I would like to be able to also see Pick2 having 0 transactions, like the following:
Pick1 - 10
Pick2 - 0

Here is the SQL

SELECT [Gisteren binperf all].Picknummer, Count([Gisteren binperf all].FULLNAME) AS CountOfFULLNAME
FROM [Gisteren binperf all]
GROUP BY [Gisteren binperf all].Picknummer;

The Picknummer is the employee number which is Grouped and then the FULLNAME shows their name which is Counted.


As I said this works fine if the person has made transactions but if they have none nothing shows up in the table at all.

I hope this is clear to you and that you could help me out.


Thanks in advance!

JP

[June7]

Using LIKE without wildcard is meaningless, just use = sign.

If there is no data then there is nothing for the query to display. You would need a dataset of all possible employee and date combinations. In your case this query might do it:

SELECT EmpID, Date()-1 AS Yesterday FROM Employees;

Then join that query to the transactions table joining on EmpID and TransDate fields (compound join) with join type 'show all records from [empID/yesterday query] and only those from [transactions] that match', apply pick filter and build your counts from there.

[andy49]

Date()-1 may not return yesterday's date.

If not use dateadd("d",-1, date())




Sent from my iPhone using Tapatalk