Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Show all dates in a report

Discussion in 'Information Technology' started by jean.ulrich@snclavalin.com, Jul 28, 2006.

Tags:
  1. jean.ulrich@snclavalin.com

    jean.ulrich@snclavalin.com
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. PC Datasheet

    PC Datasheet
    Expand Collapse
    Guest

    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
    resource@pcdatasheet.com



    <jean.ulrich@snclavalin.com> wrote in message
    news:1147688925.313936.49970@u72g2000cwu.googlegroups.com...
    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
     
  4. jean.ulrich@snclavalin.com

    jean.ulrich@snclavalin.com
    Expand Collapse
    Guest

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

    You have another suggestion ?

    thanks
     
  5. StopThisAdvertising

    StopThisAdvertising
    Expand Collapse
    Guest

    "PC Datasheet" <NoSpam@Spam.Com> schreef in bericht news:AsZ9g.2463$x4.1030@newsread3.news.pas.earthlink.net...

    --
    > 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 ????
    > resource@pcdatasheet.com


    --
    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
     
  6. PC Datasheet

    PC Datasheet
    Expand Collapse
    Guest

    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

    --
    PC Datasheet
    Your Resource For Help With Access, Excel And Word Applications
    Over 1175 users have come to me from the newsgroups requesting help
    resource@pcdatasheet.com





    <jean.ulrich@snclavalin.com> wrote in message
    news:1147694902.554189.150560@g10g2000cwb.googlegroups.com...
    > Hi
    > That's what I did first, but it is not working
    >
    > You have another suggestion ?
    >
    > thanks
    >
     
  7. John Marshall, MVP

    John Marshall, MVP
    Expand Collapse
    Guest

    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

    "PC Datasheet" <NoSpam@Spam.Com> wrote in message
    news:m2%9g.2717$y4.2707@newsread2.news.pas.earthlink.net...
    > 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
    >
    > --
    > PC Datasheet
    > Your Resource For Help With Access, Excel And Word Applications
    > Over 1175 users have come to me from the newsgroups requesting help
    > resource@pcdatasheet.com
    >
    >
    >
    >
    >
    > <jean.ulrich@snclavalin.com> wrote in message
    > news:1147694902.554189.150560@g10g2000cwb.googlegroups.com...
    >> Hi
    >> That's what I did first, but it is not working
    >>
    >> You have another suggestion ?
    >>
    >> thanks
    >>

    >
    >
     
  8. jean.ulrich@snclavalin.com

    jean.ulrich@snclavalin.com
    Expand Collapse
    Guest

    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
     
  9. John Marshall, MVP

    John Marshall, MVP
    Expand Collapse
    Guest

    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
     

Share This Page