Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 39

Thread: Report help

  1. #1
    VBAX Regular
    Joined
    Jun 2005
    Posts
    65
    Location

    Report help

    Hi and thanks for reading.

    I need to create a report that displays the number of instances of records that fall into a specific catagory by month. I have found i can do a dcount (=DCount("TypeID","tblFile","[TypeID] = 3 AND [Opened] > #02/01/05# AND [Opened] < #02/28/05#")) in the control source of text boxes with the report bound to the table but would be static by year. Any ideas?

    Thanks again.

  2. #2
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    What do you want to do?

  3. #3
    VBAX Regular
    Joined
    Jun 2005
    Posts
    65
    Location
    Hi

    I want to display how many cases are opened by month for multiple catagories. hope that explains.

  4. #4
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    It sounds like you want a report based on a crosstab query, but I'm confused by "would be static by year".

  5. #5
    VBAX Regular
    Joined
    Jun 2005
    Posts
    65
    Location
    ignore that. the only way i could figure out a way with dcount was by coding the date in an sql where clause. anyway. when i tried to build a crosstab query with the wizard i get a you canceled the previous operation error.

  6. #6
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    Gimme a snapshot of your table, and I'll give you back a crosstab query. I only need those two fields.

  7. #7
    VBAX Regular
    Joined
    Jun 2005
    Posts
    65
    Location
    i have a bmp of the first few rows and colum headers, is that OK and how do i send it.?

  8. #8
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    I was worried that saying snapshot might make you think of screenshot. Please provide the data as an attachment to your post. Stick it in an otherwise empty database or export it to Excel if that's easier. Delete whatever you don't want anyone to see, but make sure you at least leave the fields for TypeID and Opened and whatever field you want to be the label for your rows.

  9. #9
    VBAX Regular
    Joined
    Jun 2005
    Posts
    65
    Location
    Ok i have it in excel, how do i send.

  10. #10
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    When in advanced posting mode, click the button for manage attachments.

  11. #11
    VBAX Regular
    Joined
    Jun 2005
    Posts
    65
    Location

    report help

    Think i figured it out

  12. #12
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    How to do the crosstab or how to attach?

  13. #13
    VBAX Regular
    Joined
    Jun 2005
    Posts
    65
    Location
    well ithought attach, ill try again. had to zip sorry

  14. #14
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    It's hard to tell with two rows of data if this looks like it should. I assumed the first column was the key, and I used that for the rows.

    TRANSFORM Count(Sheet1.Type) AS CountOfType
    SELECT Sheet1.FileID, Count(Sheet1.Type) AS [Total Of Type]
    FROM Sheet1
    GROUP BY Sheet1.FileID
    PIVOT Format([Opened],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

  15. #15
    VBAX Regular
    Joined
    Jun 2005
    Posts
    65
    Location
    Hi i modified the code as follows and recieve a expresion to complex error

    TRANSFORM Count(tblFile.TypeID) AS CountOfTypeID
    SELECT tblfile.FileID, Count(tblfile.TypeID) AS [Total Of TypeID]
    FROM tblfile
    GROUP BY tblFile.FileID
    PIVOT Format([Opened],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec" );

  16. #16
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    That's goofy. Let me see if I can recreate the problem on my end, but I'm going to need more data. Export to Excel again, but leave all your rows in the table. Just delete all the columns except FileID (unless you want to group by something else), TypeID, and Opened.

  17. #17
    VBAX Regular
    Joined
    Jun 2005
    Posts
    65
    Location
    OK hey thanks again

  18. #18
    VBAX Regular
    Joined
    Jun 2005
    Posts
    65
    Location
    one other note. i do have access 2000 installed also

  19. #19
    VBAX Regular
    Joined
    Jun 2005
    Posts
    65
    Location
    one other note. i do have access 2000 installed also. dont know if that could cause an issue

  20. #20
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    I think the problem could be that you have ######## instead of dates in a number of rows. Import your sheet into your database, and you'll get conversion errors (and subsequently a table that doesn't contain #######). Then, try this query:

    TRANSFORM Count(tblFile2.Type) AS CountOfType
    SELECT tblFile2.Type, Count(tblFile2.Type) AS [Total Of Type]
    FROM tblFile2
    GROUP BY tblFile2.Type
    ORDER BY tblFile2.Type
    PIVOT Format([Opened],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
    Note: the attached file has this as tblFile without the 2.

Posting Permissions

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