SQL Group By

[Ron Daemen]

Hi,

I'm using MS Access Databases ( mdb ) with the Ms Access ODBC driver. When i open a Query containing the Group By statement the ODBC driver returns the correct total of records but without any values. The query is correct because MS Access can run it and returns the same total of records including the data ?

-- Ron

[PeterK]

Are you using a CRecordSet derived class to access the data?

Can I see the SQL statement?

How many fields are in the Access table?

[Ron Daemen]

Hi,

Thanks for reply !

One of the Sql statements :
SELECT STAT.SESSIONID
FROM STAT
GROUP BY STAT.SESSIONID;

There are 11 fields in this Ms Access Table called STAT, SessionID is a text field and not the key-value. All other queries return data so the CRecordset structures are ok.

-- Ron

[PeterK]

I had a similar problem when attempting to execute SQL statement using the DISTINCT command.

I believe the problem lies in your DoFieldExchange method. You may need to alter it to handle returning only the one field.

Create a new member variabe called m_fieldExchangeType. Make sure to initialize it in your constructor.


void CAppmaintSet::DoFieldExchange(CFieldExchange* pFX)
{
// m_fieldExchangeType was added for use with a DISTINCT SQL statement
// the query is used to return all the applications for a specific institution
// there was a problem when using all 12 table fields to return just the appcode
// when one column is used in a query it automatically defaults to being placed
// in the first RFX statement in the DoFieldExchange.

//{{AFX_FIELD_MAP(CAppmaintSet)
pFX->SetFieldType(CFieldExchange::outputColumn);
if (m_fieldExchangeType == 1)
{
m_nFields = 1;
m_nParams = 0;
RFX_Long(pFX, _T("[ApplicationCode]"), m_ApplicationCode);
}
else
{
m_nFields = 12;
m_nParams = 3;
RFX_Long(pFX, _T("[ProcessorId]"), m_ProcessorId);
RFX_Long(pFX, _T("[InstitutionId]"), m_InstitutionId);
RFX_Long(pFX, _T("[ApplicationType]"), m_ApplicationType);
RFX_Long(pFX, _T("[ApplicationCode]"), m_ApplicationCode);
RFX_Text(pFX, _T("[ApplicationDescription]"), m_ApplicationDescription);
RFX_Long(pFX, _T("[TotalGroupCode]"), m_TotalGroupCode);
RFX_Long(pFX, _T("[FloatPriorityCode]"), m_FloatPriorityCode);
RFX_Bool(pFX, _T("[CashItem]"), m_CashItem);
RFX_Bool(pFX, _T("[FormattableItem]"), m_FormattableItem);
RFX_Bool(pFX, _T("[FloatableItem]"), m_FloatableItem);
RFX_Long(pFX, _T("[FloatOverrideTable]"), m_FloatOverrideTable);
RFX_Bool(pFX, _T("[AppRecInXRefFile]"), m_AppRecInXRefFile);
}
//}}AFX_FIELD_MAP

if (m_fieldExchangeType != 1)
{
pFX->SetFieldType(CFieldExchange::param);
RFX_Long(pFX, "Param1", m_nParm1);
RFX_Long(pFX, "Param2", m_nParm2);
RFX_Long(pFX, "Param3", m_nParm3);
}
}

// In your code where you execute the SQL statement set the new member variable

// Must use DISTINCT statement because may have multiple processors
// in the appmaint table. Column names must follow the order of DoFieldExchange
// only need all the fields up to the one field you want.
// application maintenance will have multiple records
CString strSQLSelect = "SELECT DISTINCT [ApplicationCode] FROM [ApplCode] ";

pAppMaintSet->m_strFilter.Format("[InstitutionID] = %ld", m_instID);

pAppMaintSet->m_strSort = "[ApplicationCode]";

pAppMaintSet->m_fieldExchangeType = 1;

pAppMaintSet->Open(CRecordset::snapshot, (LPCTSTR)strSQLSelect);

// reset the member variable for normal field exchange
pAppMaintSet->m_fieldExchangeType = 0;






I hope this helps.