Report help

[calenger]

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.

[xCav8r]

What do you want to do?

[calenger]

Hi

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

[xCav8r]

It sounds like you want a report based on a crosstab query, but I'm confused by "would be static by year".

[calenger]

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.

[xCav8r]

Gimme a snapshot of your table, and I'll give you back a crosstab query. I only need those two fields.

[calenger]

i have a bmp of the first few rows and colum headers, is that OK and how do i send it.?

[xCav8r]

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.

[calenger]

Ok i have it in excel, how do i send.

[xCav8r]

When in advanced posting mode, click the button for manage attachments.

[calenger]

Think i figured it out :)

[xCav8r]

How to do the crosstab or how to attach?

[calenger]

well ithought attach, ill try again. had to zip sorry

[xCav8r]

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");

[calenger]

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");

[xCav8r]

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.

[calenger]

OK hey thanks again

[calenger]

one other note. i do have access 2000 installed also

[calenger]

one other note. i do have access 2000 installed also. dont know if that could cause an issue

[xCav8r]

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.

[xCav8r]

Oh, it imported the Type as text because I just accepted default. I changed it to number, then I modified the query to get rid of nulls.


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

Sorry for the inconsistency. I just left this as tblFile.

[calenger]

Hi The ###### are only in excel. I checked the opened coloum and fixed any null values, still same error. thanks for the help i am going to start on it again tomorrow.

[xCav8r]

K, sounds good. Tomorrow verify that your field type is a date/time for opened.

[calenger]

Hi just noticed the change statment. tryed it and had the same error.

[calenger]

yes set to date/time

[xCav8r]

Well, before you go, make a new mdb to attach here and export this table to it (with these three columns). I'll see what I can do.


....errrr, whenever. ;)

[calenger]

Ok. have removed any sensitive info.

[calenger]

thanks talk to you tomorrow.

[xCav8r]

Can you convert it to 2000 format and see if you get the same error? I want to see if that's the reason that you're having problems.

[calenger]

Morning, well afternoon :)

When i try to open it or convert to 2000 i get a you dont have rights error.

[xCav8r]

Create a new mdb and import from the 97 one. That should get around the security.

[calenger]

Ok imported into new 97 db and then converted to 2000. during conversion i recieved a "could not find description field" error and halts

[xCav8r]

can you not create a 2000 one and import from the 97? (getting around the whole conversion thingee)

[calenger]

Ok had one error on import. under qryfind "invalid argument". when i try to do anything in the new version (design, run, anything) it generates mutiple errors.

[xCav8r]

Okay, pick up your computer and throw it across the room. Lemme know if that fixes the problem. ;)

[xCav8r]

Just kidding. Here's an idea...short term workaround so you can get this report done. Try creating a new database, 2000 format, and link to the 97 mdb with tblFile. Try to run the crosstab query wizard and build a query based on that linked table. Hopefully, that'll work.

[calenger]

LOL. my thoughts to :)

[calenger]

It seems that the to complex expression error is generated by the format of opened to months. works perfect in 2000.

I can't use 2000 for the report unfortunaty. this department does not own it and is not willing to buy and want thier in house people to be able to generate the report.

Thanks for trying as hard as you did.

[xCav8r]

Bummer.