-
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.
-
-
Hi
I want to display how many cases are opened by month for multiple catagories. hope that explains.
-
It sounds like you want a report based on a crosstab query, but I'm confused by "would be static by year".
-
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.
-
Gimme a snapshot of your table, and I'll give you back a crosstab query. I only need those two fields.
-
i have a bmp of the first few rows and colum headers, is that OK and how do i send it.?
-
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.
-
Ok i have it in excel, how do i send.
-
When in advanced posting mode, click the button for manage attachments.
-
report help
Think i figured it out :)
-
How to do the crosstab or how to attach?
-
well ithought attach, ill try again. had to zip sorry
-
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.
Code:
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");
-
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" );
-
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.
-
-
one other note. i do have access 2000 installed also
-
one other note. i do have access 2000 installed also. dont know if that could cause an issue
-
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:
Code:
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.