Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query / Report (Access 2000 SP-1)

    I am trying create a query / report that will only print the current pay period hours and O/T. The problem is that when this database was created many years ago, the table was setup without dates, only with employee ID, then seperate pay periods ranging from p1hrs to p27 hrs and p1OT to p27 OT. I can create a query to pull all the info and then print a report with only one pay period, but I have to manually change it every 2 weeks. Is there an expression I can use to prompt for which pay period I'm on and only report hrs and OT for that pay period.

    Thanks in advance

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query / Report (Access 2000 SP-1)

    Modify your query to extract only the records for the Pay Period you are interested in. To do this, on the criteria row for Pay Period in the query grid, type the expression (using the square brackets)
    [Enter the Pay Period]
    When you run the query, a window will display with the text Enter the Pay Period. Type in the correct Pay Period and click the OK button or press Enter. Then the query will extract only the records with the Pay Period you entered. If you make this query the source for your report, when you run the report, the query will display the parameter window and you should be all set..

  3. #3
    New Lounger
    Join Date
    Jan 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query / Report (Access 2000 SP-1)

    Thanks for the post. I tried adding the [Enter the pay period] and it does prompt me. I then get an error that the expression is typed incorrectly or is too complex, etc. I don't think that it knows what to do with the P1 once I've entered it. Is there an "if" statement or something like that that needs to be added to each data criteria row so the report knows what was entered and how to compare?

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query / Report (Access 2000 SP-1)

    I'm not certain what is happening. A parameter query does not typically require an IF statement in order to extract using the parameter as a criteria, but then I can't see the rest of your query, so I don't know what is causing the error.
    Are you getting the error when you run the query alone or are you opening the report? You need to post the SQL or the query so we can see the specifics.

  5. #5
    New Lounger
    Join Date
    Jan 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query / Report (Access 2000 SP-1)

    I'm not sure how to post just a query, so here is my description of the query. Again I think the original design of the database was not the best.
    Name ID P1hrs P2hrs P3hrs P4hrs .... P1OT P2OT P3OT P4OT....
    I have the [Enter the pay period] in the criteria field below P1hrs.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query / Report (Access 2000 SP-1)

    If this is designed so that one record contains all the Pay periods per person, it seems that you have an unnormalized database. That can cause problems and I'm not certain how to guide you further.

    If that is the case, someone else may know how to accomplish what you need with this design.

  7. #7
    New Lounger
    Join Date
    Jan 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query / Report (Access 2000 SP-1)

    Here is a copy of the SQL. I appreciate all the help. Maybe I'm trying to make this more complicated than it really is. Thanks for all the help.

    SELECT STAFFINF.NAME, [BENEFITS TABLE].[STAFF ID], [BENEFITS TABLE].P1HRS, [BENEFITS TABLE].P1OT, [BENEFITS TABLE].P2HRS, [BENEFITS TABLE].P2OT, [BENEFITS TABLE].P3HRS, [BENEFITS TABLE].P3OT, [BENEFITS TABLE].P4HRS, [BENEFITS TABLE].P4OT, [BENEFITS TABLE].P5HRS, [BENEFITS TABLE].P5OT, [BENEFITS TABLE].P6HRS, [BENEFITS TABLE].P6OT, [BENEFITS TABLE].P7HRS, [BENEFITS TABLE].P7OT, [BENEFITS TABLE].P8HRS, [BENEFITS TABLE].P8OT, [BENEFITS TABLE].P9HRS, [BENEFITS TABLE].P9OT, [BENEFITS TABLE].P10HRS, [BENEFITS TABLE].P10OT, [BENEFITS TABLE].P11HRS, [BENEFITS TABLE].P11OT, [BENEFITS TABLE].P12HRS, [BENEFITS TABLE].P12OT, [BENEFITS TABLE].P13HRS, [BENEFITS TABLE].P13OT, [BENEFITS TABLE].P14HRS, [BENEFITS TABLE].P14OT, [BENEFITS TABLE].P15HRS, [BENEFITS TABLE].P15OT, [BENEFITS TABLE].P16HRS, [BENEFITS TABLE].P16OT, [BENEFITS TABLE].P17HRS, [BENEFITS TABLE].P17OT, [BENEFITS TABLE].P18HRS, [BENEFITS TABLE].P18OT, [BENEFITS TABLE].P19HRS, [BENEFITS TABLE].P19OT, [BENEFITS TABLE].P20HRS, [BENEFITS TABLE].P20OT, [BENEFITS TABLE].P21HRS, [BENEFITS TABLE].P21OT, [BENEFITS TABLE].P22HRS, [BENEFITS TABLE].P22OT, [BENEFITS TABLE].P23HRS, [BENEFITS TABLE].P23OT, [BENEFITS TABLE].P24HRS, [BENEFITS TABLE].P24OT, [BENEFITS TABLE].P25HRS, [BENEFITS TABLE].P25OT, [BENEFITS TABLE].P26HRS, [BENEFITS TABLE].P26OT, [BENEFITS TABLE].P27HRS, [BENEFITS TABLE].P27OT
    FROM [BENEFITS TABLE] INNER JOIN STAFFINF ON [BENEFITS TABLE].[STAFF ID] = STAFFINF.[STAFF ID]
    WHERE ((([BENEFITS TABLE].P1HRS)=[Enter the pay period]) AND (([BENEFITS TABLE].P1OT)="P1") AND (([BENEFITS TABLE].P2HRS)="P2") AND (([BENEFITS TABLE].P2OT)="P2") AND (([BENEFITS TABLE].P3HRS)="P3") AND (([BENEFITS TABLE].P3OT)="P3") AND (([BENEFITS TABLE].P4HRS)="P4") AND (([BENEFITS TABLE].P4OT)="P4") AND (([BENEFITS TABLE].P5HRS)="P5") AND (([BENEFITS TABLE].P5OT)="P5") AND (([BENEFITS TABLE].P6HRS)="P6") AND (([BENEFITS TABLE].P6OT)="P6") AND (([BENEFITS TABLE].P7HRS)="P7") AND (([BENEFITS TABLE].P7OT)="P7") AND (([BENEFITS TABLE].P8HRS)="P8") AND (([BENEFITS TABLE].P8OT)="P8") AND (([BENEFITS TABLE].P9HRS)="P9") AND (([BENEFITS TABLE].P9OT)="P9") AND (([BENEFITS TABLE].P10HRS)="P10") AND (([BENEFITS TABLE].P10OT)="P10") AND ((STAFFINF.[ACTIVE/INACTIVE])="A")) OR ((([BENEFITS TABLE].P1HRS)="P1"));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •