Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jirm
Starting Member
1 Post |
 Posted - 2002-08-30 : 02:28:55
|
| Sorry with my short Inglish !!! I hope anyone can help my !!!...I have one SQL Jet (MS Access 97) query but I need that one field clause COUNT be modified with a limit of date without affect the other fields.I post the used SQL sentece next:TRANSFORM Count(c_planif_visitas_jul_ag.CIF_) AS [El Valor]SELECT c_planif_visitas_jul_ag.CASO1, c_planif_visitas_jul_ag.Oficina, c_planif_visitas_jul_ag.Asesor, Count(c_planif_visitas_jul_ag.CIF_) AS Planificados, Count(c_planif_visitas_jul_ag.TOP) AS [Top], Count(c_planif_visitas_jul_ag.[MAS DE UNA SEDE]) AS [mas de una sede], Count(c_planif_visitas_jul_ag.visita_inicial_020624) AS visitados, [Planificados]-[visitados] AS [No visitados 020624]FROM c_planif_visitas_jul_agWHERE (((c_planif_visitas_jul_ag.CASO1)="SI"))GROUP BY c_planif_visitas_jul_ag.CASO1, c_planif_visitas_jul_ag.Oficina, c_planif_visitas_jul_ag.AsesorORDER BY c_planif_visitas_jul_ag.Oficina DESC PIVOT c_planif_visitas_jul_ag.PY;I need the field be modified like this COUNT c_planif_visitas_jul_ag.visita_inicial_020624 > #01/01/2002#, but still INModified the result of the others fields, for that the WHERE clause must be the same (affect all the fields) and I don't know how to modify the SELECT clause with the prior express limit date.Please HELP me, I desperate. :-((Thanks, for all !!!! |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-09-04 : 20:03:07
|
| If this were a SQL Server query, then I believe a CASE statement would be in order, but since this is Access 97 I believe you would use IIF(). Basically, your IIF will test the date value and return either a 1 or 0 than can then be summed to create your count. |
 |
|
|
|
|
|
|
|
Limit COUNT field with a date