Show all dates in a report

[jean.ulrich]

Hi

I have a table "tblDate" with one field named "DayWork"
This table as 31 records from march first through march 31

I have another table name "tblPointage"
This table as only 4 fields "DayWork", "EmployeN°", "Status" and
"Hours"

I have over 8000 records in this table

Now what I want is a report that will contain every day of the month
and if an employee did not wor for let say the 7 of march, report show
the 7 of march with noting. (there is no record for this employee for
the 7)

What I have now is if an employee have work every day in the month, I
obtain 31 lines which is fine
But if an employee have work only 5 days within the month, i obtain
only 5 lines and i would like to obtain 31 (data on the 5 days employee
have work and nothing for the other 26 lines)

Is it possible ?

Thanks

 

[PC Datasheet]

Create a query that includes both tables. Select "DayWork" in TblDate and
drag and drop it on "DayWork" in TblPointage. Double click on the join line.
A dialog will appear with three options and the first option will be
selected as the default. Select the option that says something like Show all
records from TblDate and only the related records from TblPointage.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1175 users have come to me from the newsgroups requesting help
(e-mail address removed)



Hi

I have a table "tblDate" with one field named "DayWork"
This table as 31 records from march first through march 31

I have another table name "tblPointage"
This table as only 4 fields "DayWork", "EmployeN°", "Status" and
"Hours"

I have over 8000 records in this table

Now what I want is a report that will contain every day of the month
and if an employee did not wor for let say the 7 of march, report show
the 7 of march with noting. (there is no record for this employee for
the 7)

What I have now is if an employee have work every day in the month, I
obtain 31 lines which is fine
But if an employee have work only 5 days within the month, i obtain
only 5 lines and i would like to obtain 31 (data on the 5 days employee
have work and nothing for the other 26 lines)

Is it possible ?

Thanks

 

[jean.ulrich]

Hi
That's what I did first, but it is not working

You have another suggestion ?

thanks

 

[StopThisAdvertising]

"PC Datasheet" <[email protected]> schreef in bericht
--

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications 'Resource ????
Over 1175 users have come to me from the newsgroups requesting help '1175 users ????
(e-mail address removed)

--
To Steve:
You still don't get it? No-one wants your advertising/job hunting here!
Over 850 !! users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

To the original poster:
Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.
And he is known here as a shameless liar with no ethics at all.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R

 

[PC Datasheet]

Hi Jean,

It's the way to do it!

Try again ---
1. Click on New to create a new query
2. Select Design View and click OK
3. Click the Close button
4. The View button on the toolbar at the top of the screen will show "SQL".
Click on the View button
5. Put your cursor on "Select;" and Paste the following into the Query View
screen:
SELECT TblDate.Daywork, TblPointage.EmployeeN, TblPointage.Status,
TblPointage.Hours
FROM TblDate LEFT JOIN TblPointage ON TblDate.Daywork = TblPointage.DayWork;
6. Close and save this query
7. Run the query and you should get the results you want

 

[John Marshall, MVP]

Stevie you need to go back and read the original request. Your solution is
only for a Left Join, while he is looking for a Cartesian Join. (31 records
for each employee)

John... Visio MVP

 

[jean.ulrich]

Hi
John is right
Here what I have try
In the table "tblPointage, I kept only 31 records with the same
employee number
Then I made a query with both table with the join all records from
"tblDateWork" and only those matching from "tblPointage"
That's ok report was ok
Then, i took out 2 records from table "tblPointage" the 7 and the 21 of
march
Report was still good, showing all 31 days of march but was giving
empty fields for both the 7 and the 21 of march
But when i put back the original table (over 8000 records) i could not
get what i wanted because of this reason
The report shoud have a sorting on employee's number as I want one
employee per sheet .
So if with the small table of the 7 and the 21 march i don't have
employee's number for those dates, i am screw and report cannot show
dates when there is no data to display.
I would have to print employees one by one to obtain the result I want
If there is 200 employees that means 200 manipulations.
Of course i can put a combo box "cmbChoice" on a for with the listing
of the employees then in the query under employeeès number put a
criteria like = [forms]![cmbChoice]
But i would have still to do it one by one.
At that point I am asking if there could be simple code that would put
an employee's number in the criteria of the query then print the first
emplyéé's data, then change the criteria for the next employee's
number then print the report, change the criteria for the third
employee's number...and so on until the last employee's number

Thanks for helping

 

[John Marshall, MVP]

Create two queries:
Call the first one QryEmployees and place

SELECT a.EmployeNo, b.DayWork
FROM [SELECT DISTINCT EmployeNo FROM tblPointage]. AS a, tblDate AS b;

in the SQL View

Create a second query (you can name it whatever you like) and place

SELECT QryEmployees.EmployeNo, QryEmployees.DayWork, tblPointage.Status,
tblPointage.Hours
FROM QryEmployees LEFT JOIN tblPointage
ON (QryEmployees.DayWork = tblPointage.DayWork)
AND (QryEmployees.EmployeNo = tblPointage.EmployeNo)
ORDER BY QryEmployees.EmployeNo, QryEmployees.DayWork;

in the SQL View

(Thanks Michel)

John... Visio MVP