No entries dont show in results (1 Viewer)

[oxicottin]

Hello, I need to create an query expression from my table that checks if there is a year [boughtYear] and if so is the yes/no field [boughtvac] true or false using a criteria and if there is a year then under my expression give a number 40 and if there is no such year then give a 0 for each employee [EmployeeID]

I tried....
test: IIf([BoughtVac]=True,40,0)

But for the employees that don't have a year entry [boughtYear] OR [boughtvac] they don't show up in my results and I need them in there.

SELECT tbluEmployees.EmployeeID, tbluBoughtVacation.BoughtVac, IIf([BoughtVac]=True,40,0) AS test, tbluBoughtVacation.BoughtYear
FROM tbluEmployees INNER JOIN tbluBoughtVacation ON tbluEmployees.EmployeeID = tbluBoughtVacation.EmployeeID
GROUP BY tbluEmployees.EmployeeID, tbluBoughtVacation.BoughtVac, tbluBoughtVacation.BoughtYear
HAVING (((tbluBoughtVacation.BoughtYear)=([Forms]![frm_Switchboard]![cboYear])));

 

[pbaldy]

Try

SELECT tbluEmployees.EmployeeID, tbluBoughtVacation.BoughtVac, IIf([BoughtVac]=True,40,0) AS test, tbluBoughtVacation.BoughtYear
FROM tbluEmployees INNER JOIN tbluBoughtVacation ON tbluEmployees.EmployeeID = tbluBoughtVacation.EmployeeID
WHERE tbluBoughtVacation.BoughtYear=[Forms]![frm_Switchboard]![cboYear] OR tbluBoughtVacation.BoughtYear Is Null
GROUP BY tbluEmployees.EmployeeID, tbluBoughtVacation.BoughtVac, tbluBoughtVacation.BoughtYear

 

[oxicottin]

@pbaldy I get the same results..... The problem I'm having is if I never entered a [boughtYear] or [boughtvac] for an employee then it wont display any result at all for that employee. If I go and enter lets say the year [boughtYear] in the table for the no result employee then it will show something and everything works fine. Any other ideas?

here is a new example with an image of results. If you look at the last entry employee 36 is a 0 and 0 because I never entered a year. this is what I need but only for the criteria [Forms]![frm_Switchboard]![cboYear] which if I use that then employee 36 wont display

SELECT tbluEmployees.EmployeeID, IIf([BoughtVac]=True,40,0) AS test, IIf([BoughtYear]=True,[BoughtYear],0) AS test2
FROM tbluEmployees LEFT JOIN tbluBoughtVacation ON tbluEmployees.EmployeeID = tbluBoughtVacation.EmployeeID
GROUP BY tbluEmployees.EmployeeID, IIf([BoughtVac]=True,40,0), IIf([BoughtYear]=True,[BoughtYear],0);

 

[pbaldy]

Can you attach a sample db? That SQL should return records with no boughtyear.

 

[oxicottin]

@pbaldy I got it with your is null.... Thank you!

SELECT tbluEmployees.EmployeeID, IIf([BoughtVac]=True,40,0) AS test, IIf([BoughtYear]=True,[BoughtYear],0) AS test2
FROM tbluEmployees LEFT JOIN tbluBoughtVacation ON tbluEmployees.EmployeeID = tbluBoughtVacation.EmployeeID
WHERE (((tbluBoughtVacation.BoughtYear)=[Forms]![frm_Switchboard]![cboYear] Or [tbluBoughtVacation].[BoughtYear] Is Null))
GROUP BY tbluEmployees.EmployeeID, IIf([BoughtVac]=True,40,0), IIf([BoughtYear]=True,[BoughtYear],0);

 

[pbaldy]

No problem.

 

[oxicottin]

@pbaldy I thought I had it but it seems I don't. I made a test query and if you run the test query it asks for a year. If I enter 2021 then I get the results I'm after BUT if I enter 2022 then I only get 3 results from employees who have no entries in the table (tbluBoughtVacation) and everyone where there is at least one entry doesn't show up. I need everyone to show for any year whether they have entries in (tbluBoughtVacation) or not, just like if I entered the year 2021.

 

[arnelgp]

this is somewhat similar.
Solved - Force Zero Result for Week in Query | Access World Forums (access-programmers.co.uk)

 

[arnelgp]

see finalQuery.

 

[oxicottin]

@arnelgp I checked out your example (Thank You), basically that's exactly what I'm doing now in my original database. What I originally do is for each employee I add the year each year and whether or not they bought a vacation and the data I need will show but who wants to do that every year including doing it for new employees. Is there any other solutions with my example?

Thanks!

 

[arnelgp]

You do not need to add "missing" records on your table. The Cartesian query will fill the missing data.

 

[oxicottin]

@arnelgp Thanks I got it working with your example, I see how its working now! Before I thought I had to still enter a year every year but now its pulling from the table until 2050... Good one!

Thanks again!