CODEWITHSUNDEEP
sqlms-accessinner-join
dieKoderin
dieKoderin

Reputation: 1582

show rows with no data in access

I have the following query:

SELECT PersonTotalHours.MA, PersonTotalHours.Year, PersonTotalHours.CalendarWeek,
PersonTotalHours.Hours, Person.Name, Person.Lastname 
FROM PersonTotalHours
INNER JOIN Person
ON PersonTotalHours.MA = Person.MA;

Which results in the following table:

  MA       Year    CalendarWeek    Hours    Name    Lastname
  aA       2000         5           53      aa        AA
  aA       2000         44          175     aa        AA
  ...      ...         ...          ...
  aA       2001         4           226     aa        AA
  aA       2001         12          87      aa        AA
  ...      ...         ...          ...
  bB       2000         1           189     bb        BB
  bB       2000         35          65      bb        BB
  ...      ...         ...          ...

as you can see, there is no data for some calendar weeks. Is there any way that I can have a row for all calendar weeks(1 to 53) and with hours=0 for the ones that don't exist now?

Edit I have solve this temporarily by adding the missing row to the table. using a function that is called when the report opens. Still looking for a non-stupid solution.

Upvotes: 1

Views: 116

Answers (2)

jarlh
jarlh

Reputation: 44766

Create a help table Calendar, storing all possible values. RIGHT JOIN it:

SELECT pth.MA, pth.Year, pth.CalendarWeek,
pth.Hours, p.Name, p.Lastname 
FROM PersonTotalHours pth
INNER JOIN Person p
    ON pth.MA = p.MA
RIGHT JOIN Calendar c
    ON pth.Year = c.Year AND pth.CalendarWeek = c.CalendarWeek

(Using table aliases to spare some typing.)

EDIT: MS Access query attempt, version 1:

SELECT pth.MA, pth.Year, pth.CalendarWeek,
pth.Hours, p.Name, p.Lastname 
FROM (PersonTotalHours pth
INNER JOIN Person p
    ON pth.MA = p.MA)
RIGHT JOIN Calendar c
    ON pth.Year = c.Year AND pth.CalendarWeek = c.CalendarWeek

EDIT: MS Access query attempt, version 2:

SELECT pth.MA, pth.Year, pth.CalendarWeek,
pth.Hours, p.Name, p.Lastname 
FROM calendar
    left join (PersonTotalHours pth
               INNER JOIN Person p ON pth.MA = p.MA)
    ON pth.Year = c.Year AND pth.CalendarWeek = c.CalendarWeek

Upvotes: 4

Aneesh Sivaraman
Aneesh Sivaraman

Reputation: 931

Please check this query. 

select PersonTotalHours.MA,PersonTotalHours.YEAR,b.CalendarWeek, PersonTotalHours.Hours, '' Name, '' LastName from PersonTotalHours
right join (
select '' MA, 0 Year, no CalendarWeek, 0 Hours, '' Name, '' Lastname from (
select row_number() over(order by object_id) no from sys.all_objects
) a where no <= 53) b on b.CalendarWeek = Table_1.CalendarWeek

I am not joined person table.

Upvotes: 0

Related Questions