Missing Values

Hello people,

I have been writing some queries and reports using SQL (with Crystal and ASP.NET), and have a problem. Something I often need to do is find the number of records for each month, so there is a date field and a currency field, and I need to sum all the currency values for each month.

Firstly, to get a group for each month of each year I am using ...GROUP BY Month(DateField), Year(DateField)
is this correct or is there a better way?

The more problematic thing is, where there are no records for certain month, of course this month does not show up in the query results. This is a problem when displaying results in a table/chart. Is there any way to force each month to appear (with a 0 in the SUMed field)?

Any advice would be most, most welcome,

Thanksyou could define a table say tblmonth and define a join, which will allways retrieve the a month even if there are no entries for that month|||I'll have a go at that, thanks

any idea what join that would be?|||I think it would be a left join (Outer joins allow select all from this side and show any matching on the other).

Give it a go and if what you get is silly switch to right join as there is a small chance I got it wrong.|||you'll need a table of months, or use an integers table to generate them

see Selecting all months even if they're missing (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid526288_tax285649,00.html) (site registration may be required, but it's free)|||Thanks for your help on this, I seem to have it working now.

I have a similar problem with a report I am writing in crystal reports. I am trying to create a group calendar from Outlook using a crosstab, but not all dates show up (if noone has an appointment booked on that day).

What I need is to do is get a datasource with a list of days which I can do a join on to force all dates to appear. The problem is that the dates from Outlook are just dates but other applications such as access, excel use date/time fields and so the join does not work! Does anyone know where I can get a data source of just dates to use in the join?|||a data source of just dates? yeah, you can generate it from an integers table

Missing DLL for Crystl32.ocx 6.1

Hi,

I'm using MS Access 2000 and VB in conjuction with crystal ActiveX control
Crystl32.ocx (v. 6.1) to preview and print from SQL 2000 database. All this worked fine under Windows 2000 OS. However, when I try to do the same under Windows XP OS I'm getting the error "Can not find Database DLL".

I tried re-registering the Crystl32.ocx control, which was not a problem, however, this did not correct the preview and print problem. The following are the DLLs that I copied from the Windows 2000 pc to Windows XP;

CRPE32.dll - Interface to the Crystal Report engine
CRPAIG32.dll - Crystal Reports text object suport
IMPLODE.dll - compression library

The remaining DLLs that I know of are;

MFC42.dll - MS Foundation Class libraries
MSVCIRT.dll - runtime lib
MSVCP60.dll - runtime lib
OLEPRO32.dll - OLE Property Support DLL

Thank you for your help,

Danielhi
i don't know is this going to help, but i had kind of same problem when started to use XP instead of 2K:
i was getting a message: "missing crystl32.ocx" all the time...tried to register it, but it didn't work..
what i had to do was to install an older version of crystal reports, 8.0 or 8.5, because i was told that newer versions do not support ocx controls...|||This is a really crappy message that Crystal are too lazy to fix by putting proper granular error-handler is their code.
It could be caused by a number of problems, including a missing DLL. It could be caused by having a subreport, and failing to programmatically send the logon information to the subreport by iterating through the collection of subreports and setting the logon infop for each one.
This occurs on some ODBC connections, but not on others depending on how the OS implements its security.
On our connection to an AS400, for example, we don't get this error because the connection is configurable and you can (in essence) set it to store its logon info for that session.
If you have subreports, try sending logoninfo to each one.

dave