Report Detail Not Displaying As Needed

[dp79]

I am creating a report and am totally stuck on details not displaying how I would like. I cannot seem to figure this out, any help would be appreciated. I would like the report to display "days" each customer account has been serviced - by month. I've created queries to pull the "day" from each date serviced, and by month. I do have the days showing in the appropriate month column on the report, but I need for them to all start at the top of the detail line. Kinda hard to explain. But essentially my report is looking like this:

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

ABC Company

02

04

12

05

10

15

20

I need the Acess report to look something like this:

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

ABCCompany

05

02

10

04

15

12

20

Any help would be sincerely appreciated.

[ssanfu]

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

ABC Company

02

04

12

05

10

15

20

It looks like the above because that is the what your recordset looks like.

Would you post the SQL of the recordset? And maybe the lines of data for the above example?

[dp79]

It looks like the above because that is the what your recordset looks like.

Would you post the SQL of the recordset? And maybe the lines of data for the above example?

Thank you for your reply and information. I sincerely appreciate it. What you state makes perfect sense; but I'm not sure how to redo my queries to get it right. I may be over complicating things.

Here's the SQL from the query the report is based on (is this what you were asking for), as well as a copy of the some of the actual data. Thank you again for your assistance.

SELECT SV_Q02.CUSTID, SV_Q02.SVPERYR, SV_Q02.[SVCODE(EXP)], SV_Q02.SVSYSTEMS, SV_Q02.SVMONTHS, [SV_Q01-DateCode].SVDATE, [SV_Q01-DateCode].YR001, [SV_Q01-DateCode].YR002, [SV_Q01-DateCode].YR003, [SV_Q01-DateCode].YR004, [SV_Q01-DateCode].YR005, [SV_Q01-DateCode].YR006, [SV_Q01-DateCode].YR007, [SV_Q01-DateCode].YR008, [SV_Q01-DateCode].YR009, [SV_Q01-DateCode].YR010, [SV_Q01-DateCode].YR011, [SV_Q01-DateCode].YR012
FROM SV_Q02 LEFT JOIN [SV_Q01-DateCode] ON SV_Q02.CUSTID = [SV_Q01-DateCode].CUSTID
GROUP BY SV_Q02.DIST, SV_Q02.AMID, SV_Q02.AMINI, SV_Q02.AMNAME, SV_Q02.CUSTID, SV_Q02.CUSTNAME, SV_Q02.TECHID, SV_Q02.TECHINI, SV_Q02.TECHNAME, SV_Q02.Sales2011, SV_Q02.SVPERYR, SV_Q02.[SVCODE(EXP)], SV_Q02.SVSYSTEMS, SV_Q02.SVMONTHS, [SV_Q01-DateCode].SVDATE, [SV_Q01-DateCode].YR001, [SV_Q01-DateCode].YR002, [SV_Q01-DateCode].YR003, [SV_Q01-DateCode].YR004, [SV_Q01-DateCode].YR005, [SV_Q01-DateCode].YR006, [SV_Q01-DateCode].YR007, [SV_Q01-DateCode].YR008, [SV_Q01-DateCode].YR009, [SV_Q01-DateCode].YR010, [SV_Q01-DateCode].YR011, [SV_Q01-DateCode].YR012
HAVING (((SV_Q02.CUSTID)="PRO395"));

Data

Column headings of YR001 actually represent the month (YR0 meaning current year, and 01 meaning Jan, etc., etc.)

CUSTID	SVPERYR	SVCODE(EXP)	SVSYSTEMS	SVMONTHS	SVDATE	YR001	YR002	YR003	YR004	YR005	YR006	YR007	YR008	YR009	YR010	YR011	YR012
PRO395					1/3/2012	3
PRO395					1/9/2012	9
PRO395					1/16/2012	16
PRO395					1/23/2012	23
PRO395					2/2/2012		2
PRO395					2/6/2012		6
PRO395					2/13/2012		13
PRO395					2/20/2012		20
PRO395					2/27/2012		27
PRO395					3/1/2012			1

[ssanfu]

Are "SV_Q02" & "SV_Q01-DateCode" queries? If so, what is the SQL for them?

Is "YR001" an actual field name?

[dp79]

Thank you for the reply. Yes, both of those are queries. I've built a couple of queries building up to the final one, relating other data to include in the final report. SQL as follows. YR001 is a field (to represent current year, month 01).

SV_Q01-DateCode was being used to extract the day from the date and put it in an appropriate month column.
SQL
SELECT SVDates.CUSTID, SVDates.SVDATE, xRef_DateCode.DATECODE, Day([SVDATE]) AS [DAY], IIf([DATECODE]="YR001",Day([SVDATE]),"") AS YR001, IIf([DATECODE]="YR002",Day([SVDATE]),"") AS YR002, IIf([DATECODE]="YR003",[DAY],"") AS YR003, IIf([DATECODE]="YR004",[DAY],"") AS YR004, IIf([DATECODE]="YR005",[DAY],"") AS YR005, IIf([DATECODE]="YR006",[DAY],"") AS YR006, IIf([DATECODE]="YR007",[DAY],"") AS YR007, IIf([DATECODE]="YR008",[DAY],"") AS YR008, IIf([DATECODE]="YR009",[DAY],"") AS YR009, IIf([DATECODE]="YR010",[DAY],"") AS YR010, IIf([DATECODE]="YR011",[DAY],"") AS YR011, IIf([DATECODE]="YR012",[DAY],"") AS YR012
FROM SVDates INNER JOIN xRef_DateCode ON SVDates.SVDATE=xRef_DateCode.DATEACTUAL;

SV_Q02
SELECT SVCustList.CUSTID, SVCustList.CUSTNAME, xRef_AM.DIST, SVCustList.AMID, xRef_AM.AMINI, xRef_AM.AMNAME, SVCustList.TECHID, xRef_Tech.TECHINI, xRef_Tech.TECHNAME, [SV_Q00-Sales].Sales2011, SVCustList.SVPERYR, SVCustList.[SVCODE(EXP)], SVCustList.SVSYSTEMS, SVCustList.SVMONTHS
FROM xRef_Tech RIGHT JOIN ((SVCustList LEFT JOIN xRef_AM ON SVCustList.AMID=xRef_AM.AMID) LEFT JOIN [SV_Q00-Sales] ON SVCustList.CUSTID=[SV_Q00-Sales].CUSTNO) ON xRef_Tech.TECHID=SVCustList.TECHID;

[ssanfu]

So after trying several different ways (that didn't work ), the only solution I can come up with is to write the data to a temp table, then base the report on a query on the temp table. Clunky, but with enough code, you can do anything..

[dp79]

Hi there! I really appreciate you're going the extra step and trying. I tried several things myself and can't seem to accomplish it either. I will try writing to a temp table, but I'm not sure that will do it either(?). Again, really appreciate your hanging in there with me on this and trying. Can I ask one more question, hopefully a little easier, if the company will just let me count the service calls (dates) and enter that number as final data per month, is there a way to quickly hide 0 values in the final report? I've done a little research, but not seeing how to accomplish that. Sounds easy enough, but ....
Thank you.

[ssanfu]

Hi there! I really appreciate you're going the extra step and trying. I tried several things myself and can't seem to accomplish it either. I will try writing to a temp table, but I'm not sure that will do it either(?). Again, really appreciate your hanging in there with me on this and trying. Can I ask one more question, hopefully a little easier, if the company will just let me count the service calls (dates) and enter that number as final data per month, is there a way to quickly hide 0 values in the final report? I've done a little research, but not seeing how to accomplish that. Sounds easy enough, but ....
Thank you.

You can set a custom format for a control. In design view, open the property dialog box. Click on the Format tab, the on the Format property. Press the F1 key. Look for a line that looks like "Number and Currency Data Types". Click on it.
You should see something that looks like:

Custom Formats
Custom number formats can have one to four sections with semicolons ( as the list separator. Each section contains the format specification for a different type of number.

Section Description
First The format for positive numbers.
Second The format for negative numbers.
Third The format for zero values.
Fourth The format for Null values.


For example, you could use the following custom Currency format:

$#,##0.00[Green];($#,##0.00)[Red];"Zero";"Null"

You could use something like

;;""

[dp79]

Hello! I apologize for not getting back with you right away. Got tied up in another project. Thank you so much for the above. I couldn't get the ;;"" to work. It actually just removed from the values from the field in the report. However, this one worked sort of..... #,##0;(#,##0);"Zero";"Null

Using this one changed the 0 values to the word "Zero". Did I mistype? Again, I can't tell you how much I appreciate this help. If I can get everyone to agree to the count method on this report, not displaying the 0 values will be key. Not displaying 0 values sounds simple enough, but I'm sure struggling with it.

[dp79]

Additional for above reply. I think I got it!!

#,##0; #,##0;"";"Null"

Replaced "Zero" with " "

[ssanfu]

Congratulations.. You've got it!