Query using multiple criteria doens't show all results

[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

[ranman256]

you shouldnt be using LIKE, that is for fuzzy searches.
you want exact seraches for empoyees.

IF you want , use a list box or combo. Pick the employee and the query should look at this to pull the data.
If you want MORE than 1 empoyee, to pick and choose, make a 'pick' table. Put these empoyee IDs in the table,
join the table to the main data and only THOSE picked will show.

I use a list box of names, upon dbl-click, it runs an append query to add the person to the picked table.

[ScaniaJP]

Hi,

thank you for your answer. I'm not great with forms and listboxes just yet but I've been meddling with it now but I'm still not getting the result I want (which is showing the employee and the 0).

I've also made another query where I just enter Pick1 and it also shows the 0 if there are no transactions (using a Count for this).
I'm guessing it's the combination of the 'Group By' that I do for the employee numbers and the 'Count' for the transactions in my query that is causing the issue.