Results 1 to 8 of 8

Thread: Report help

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

    Report help

    I have a table that consists of Specialist, Group#, GroupName, DocumentType(separate table, choose from dropdown list), DateAssigned, DateCompleted. From this data I need to create a report that has Specialists names across the top. Down left are the different document types. (Looks like a crosstab query). I need a total of each type of document for each specialist. Then a total of all types of documents per specialist. Also, I have to only show data with a Datecompleted between a specified range (week range). I have tried different things but nothing produces the results that I need. Any suggestions would be appreciated.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report help

    Try making a cross-tab query to get the count of each document type for each specialist and check the results to make sure you end up with the totals you want. Then use that query for a report and trying using the report wizard to group the data initially the way you want it. You'll probably have to go into design view after the wizard finishes and add your own sorting and grouping. By using footers for the Document Types you can total each specialist's documents. It shouldn't be very difficult and I'd try several arrangements until you get the one you want.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report help

    OOPS!

    I forgot your date range requirement.

    I'd make a parameter query for date input, and base the crosstab query on this.

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

    Re: Report help

    I did a cross tab query and it works fine - I get specialists down the left side and document type across the top. I get a total of documents per specialist as well as a total of each document type. My problem is using that crosstab query to create a report. I choose the query but there aren't any fields.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report help

    Are you using the wizard? Try the wizard first and you should be walked-through a series of selections to pick what you need. If you don't use the wizard you'll have to add your own field choices and make a grouping/sorting layout of your own.

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

    Re: Report help

    Yes, I am using the wizard but when I pick the crosstab query there aren't any fields to choose from.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report help

    I made up a test database and ran through the steps and I've got two screen shots of what I get when I proceed with the wizard. Do you have fields on the left side of the dialog box?
    Attached Images Attached Images

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

    Re: Report help

    No, there aren't any fields. Maybe my crosstab query is setup wrong. I created a query that included specialist, doctype, grpname and datecompleted. In datecompleted I created a parameter between[start date] and [end date] which I defined in parameter dialog box. From that query I created a crosstab query. I needed at least 3 fields for the crosstab query so it used grpname to total on. Maybe this is where I went wrong.

Posting Permissions

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