Need to re-use a month recap report for different dates 2

[lisaharris]

I have a process that imports data from another source, then calculates data and compiles information based on day of the month. I want to create a "scorecard" report, but I am having a hard time creating something that is reusable.

A few of the data fields for February's data are as follows:
RD1Time.01
RD1Time.02
RD1Time.03
RD1Time.04
RD1Time.05
RD1Time.08
RD1Time.09 ... etc where 01 is the first day of the month, 02 is the second, and so on.. The field names come from a crosstab query and use the day as the header.

My problem is that for February's data, there is no .06 or .07 as they were a weekend. But I want a column for 06 and 07 to exist, since they are valid weekdays in other months.

I tried using an iserror in the control source but it wouldn't work. I'm stumped.

__________
Veni, Vidi, Visa: I came, I saw, I charged it.

 

[SkipVought]

Hi,

Do you have access to the source that this x-tab REPORT was generated from?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lisaharris]

Yes, my process involves importing data, running queries to calculate some new fields, and then a make-table query to store the data. The crosstab queries are built off of the final table.

__________
Veni, Vidi, Visa: I came, I saw, I charged it.

 

[SkipVought]

Please post your x-tab SQL code.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lisaharris]

TRANSFORM Count([Reporting Query].appnum) AS CountOfappnum
SELECT [Reporting Query].EnterMONTH, [Reporting Query].AMPM, Count([Reporting Query].appnum) AS [Total Of appnum]
FROM [Reporting Query]
GROUP BY [Reporting Query].EnterMONTH, [Reporting Query].AMPM
PIVOT [Reporting Query].EnterDAY;

__________
Veni, Vidi, Visa: I came, I saw, I charged it.

 

[dhookom]

Enter all the possibe EnterDay values into the Column Headings property of the crosstab query. The property would have 31 values and probably look something like:
[tt][blue]Column Headings: "01","02","03","04",... "31"[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[lisaharris]

Duane,
Precisely what I needed!! I'm on the right track now!! Thank you so much!

__________
Veni, Vidi, Visa: I came, I saw, I charged it.

 

[dhookom]

Skip did all the drill-down work. I just swooped in with the quick answer. I gave him a star for saving me all the trouble ;-)

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Thanks Duane, but I do not have nearly the depth of knowledge and skill in MS Access as you and many other in these Access forums. As a p/a I know what questions need answers, and a general approch to a solution. I see myself in these forums as more of a facilitator. I am learning. But I spend very little development/coding time in MS Access, just due to my priorities.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]