Report by Date Problem

[Vassago]

Okay! I've had it up to here! I was trying my hardest to get this to work on my own, but it looks like my efforts are failing.

This is my very detailed and complicated problem:

I have a table called tbl_pending with the following fields:

Resolved_Date
Mail_Type
Mail_ID

I also have a table called RunData with the following fields:

Start_Date
End_Date

Basically, I need a report based from these two tables that has a format similar to this:

Mail Type Jan 03 | Feb 03 | Mar 03
Address Change 100 | 105 | 110
Doc Only 225 | 230 | 190
Totals 325 | 335 | 300


The date at the top is coming from the Resolved_Date field as long as it is between Start_Date and End_Date. The number is a count of the Mail_ID. I have the following query that gives me kind of what I need, but I can't figure out how to take this data and place it in a report with the above format.

SELECT (Format([Resolved_Date],"mmm"" '""yy")) AS ResolvedMonth, tbl_pending.Mail_Type, Count(tbl_pending.Mail_ID) AS CountOfMail_ID
FROM tbl_rundata, tbl_pending
WHERE (((tbl_pending.Resolved_Date) Is Not Null And (tbl_pending.Resolved_Date) Between [Start_Date] And [End_Date]))
GROUP BY (Format([Resolved_Date],"mmm"" '""yy")), tbl_pending.Mail_Type, (Year([Resolved_Date])*12+Month([Resolved_Date])-1)
ORDER BY (Year([Resolved_Date])*12+Month([Resolved_Date])-1);

Does anyone out there know how to make this work? I was thinking a crosstab query, but since the date is completely choosable by the user, I don't think that would work.

Any help with this would be greatly appreciated.

Thanks in advance!

Vassago

 

[PookaTech]

Hey Vassago,

Use a crosstab. When the user selects criteria that selects the records in a query you are correct in thinking that a crosstab may malfunction. What you can do and what I do with some of my queries is have a maketable query select the correct records and calculate any fields you need calculated for thse records. Then use the cross tab on the table that was created. The pain in the butt part is firing the maketable every time you want to access the query. Which is not bad if the user is clicking a button and running a report, but depending on what you are doing may be worse.

Good luck,
Pookatech

 

[Vassago]

Thanks for the reply. I was planning on using a cross-tab query. The problem I was then running into was making the report. The first time it works fine, but when I change the dates to look between, it produces errors. How can I make the report reformat with the new dates?

Thanks!

 

[Vassago]

Thanks Pat, that would be great...but...

I searched for the database and found the link you were talking about. Unfortunately, all I found was a 404 error. The other problem is you mentioned the database was for A2k. Unfortunately, we here at my wonderful company have a Bedrock tradition of using primitive software. I am stuck on Access 97 at the moment. I also don't have access to the installation disk because of IT liberals who think they are GOD of all computers in the company. Can you or someone else please help me out by posting a copy of that database? I would GREATLY appreciate it.

Thanks again!

Vassago

 

[Vassago]

Nevermind, I found a copy in Access 97 format. I appreciate the help and I will see what I can do.

Thanks!

Vassago

 

[Vassago]

Okay Pat, I took a look at the solutions database and created my report by following the directions. I don't know what I have done wrong, but now the report just comes up blank, no error messages of any kind. Please take a look at the attached sample and point me to the light! I'll love you forever!

Thanks again,

Vassago

 

[Rich]

You've forgotten to add code to some of the reports events
I don't want you to fall in love with me by the way!

 

[Vassago]

Thanks Rich!

What code have I forgotten? I promise not to fall in love with you if you'll tell me.

Vassago

 

[bradcccs]

Your Header and Detail events are incorrectly named.

eg:

Your Detail section "Detail" is referenced in code as "Detail1"

This is the same for your PageHeaders etc.

Brad.

 

[bradcccs]

Your ReportFooter4_Print also has an incorrect field reference.

Your "Tot" should be "Tol"


Brad.

 

[Vassago]

Well slap me silly and call me Susan! I would never have noticed that. Thanks Brad! Everything looks good now! That's what I get for copying it from Access help.

Yeah, I noticed the Tol and Tot thing right away. Thanks!

Sorry Rich, I guess I'll be forced to fall in love with you now.

Vassago

 

[bradcccs]

No problem Susan

 

[Vassago]

I have one more question for you, how can I make my columns automatically show up in chronological order? They seem to be in alphabetical order rather than chronological. I would greatly appreciate any help in this last step. Thanks!

Vassago

 

[Vassago]

Sorry, forgot to post my revised database.

 

[bradcccs]

Vassago,

I think (and that takes a lot of effort - and is often wrong but . . .) Dynamic Crosstab reports are normally ordered by using the "Column Headings" in the query properties. However, this will cause all columns to be present in your report (not dynamic).

If you sort by your Expr1:Format(...) line, then it sorts alphabetically as you mentioned.

If you are interested in a round about way workaround, then you could change your query to:

TRANSFORM Count(tbl_pendingmail.Mail_ID) AS CountOfMail_ID
SELECT tbl_pendingmail.Mail_Type AS ___Mail_Type
FROM tbl_rundata, tbl_pendingmail
WHERE (((tbl_pendingmail.Date_Entered) Between [Start_Date] And [End_Date]))
GROUP BY tbl_pendingmail.Mail_Type
ORDER BY Format([Date_Entered],"mm mmm yy")
PIVOT Format([Date_Entered],"mm mmm yy");

Note that I have padded the Mail_Type to ___Mail_Type as the report will trim the first 3 characters. The Format([DateEntered].. Now includes the month number (for sorting purposes) which will also be trimmed.

In order to display correctly in the report, you will need to alter the column naming section of your PageHeader_Format event to:

For intX = 1 To intColumnCount
        Me("Head" + Format$(intX)) = Right(rstReport(intX - 1).Name, Len(rstReport(intX - 1).Name) - 3)
    Next intX

Now, after all that, someone (Hi Pat ) is likely to post a 1 line solution, but at least this task got me through to morning smoko.

HTH

Brad.

 

[Vassago]

I must now bow in your general direction Brad!

This works perfectly. What would Access be without workarounds huh? I am curious though to see if Pat has one of those "one-line" fixes she always seems to have.

Anyway, thank you very much for your help. I am in debt.

Vassago