Results 1 to 5 of 5

Thread: Report (97)

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report (97)

    I have a database that has fields: Specialist Name, Group Number, Group Name, Date job assigned , Date completed. Each record is a job. When the job is done the specialist brings up the record and puts a complete date. I was asked to produce a report that would have a date range (for example between 6/10/02 and 6/14/02). They want to know the total of jobs without a complete date, new jobs assigned within that week, jobs completed within that week and end inventory for each specialist. I can't think of a way to do this. If I use a parameter query, how would I get the total number of jobs for each specialist and how would I get an ending inventory. Any help would be appreciated. I think I am mixing apples and oranges.

  2. #2
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report (97)

    You are not mixing apples and oranges. To keep it simple you could use the report wizard based on your query.

    To get the total jobs without a completion date you could user your (Between #6/10/02# and #6/14/02#) in the criteria under Date Job Assigned. You will also place in the criteria under the Date Completed field (Is Null) (Is Null is without the parentheses.) This will pull all the records without a completion date. Save the query. Run the Report wizard based on the query.

    The great thing about the Report wizard is that it can do sorting, math functions and summarize data.

    You could base some of your other reports off the same query but with different criteria.

    HTH

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report (97)

    They want the report to be a summary with a beginning count of jobs that do not have a complete date, minus jobs that are complete (within a weeks date range), plus new jobs added within that week, ending total of jobs that do not have a complete date. This is for each specialist. This is to be one report - not several.

  4. #4
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report (97)

    This may not be the best way to achieve this but it will give you the chance to step through one of several processes to see how you are gathering data and produce your report. If anyone has a better idea please feel free to jump in here.

    Start backwards. Design a blank report with all the fields you will need. This will give you a picture to work from.

    Create two tables. Select a specialist and Append all his/her data for that week to the 1st table.

    Build the 2nd table from the fields on your report example.

    If you are familiar with VBA you can open recordsets based on queries from the 1st table and place that data onto the 2nd table.

    If you are unfamiliar with VBA the same thing can be accomplished with the use of queries to pull, and total data from the 1st table. Using forms based on the queries, cut and paste data onto the 2nd table.

    Base your report on the 2nd table.

    Once you have everything in place you can create a macro to automate the process.

    HTH

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

    Re: Report (97)

    I don't know that this is the better way, but here's another approach ...
    Create a query to extract all the records within the desired date range and sort it by Specialist. Then create an unbound report and add a Specialist Grouping section. I realize Domain Aggregate functions are not the most efficient, but it is all that comes to mind. Place unbound controls in the details section. Use DCount on that query for IsNull(CompleteDate) to get jobs without a Complete Date. Use DCount for CompleteDate within the date range for completed jobs. Use Dcount on AssignedDate within the date range for new jobs. (Do you also have to make sure they aren't completed so you are not double-counting?) Then, you can create a Total field that adds/subtracts these fields to get your total. You may also need to add the SpecialistID to the DCount criteria parameter to get the correct count.

    It seems like a bit much, but I think that covers the statistics you need. HTH

Posting Permissions

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