LOCALID - POSTCODE - GPCODE
PTO1395164 - DN34 1AB - G9999981
PTO1395164 - DN34 1AB - G9999981
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - DN34 1AB - G8909058
PTO1395164 - TZ14 2AX - G8909058
PTO1395164 - TZ14 2AX - G8909058



Hi guys, this is a simple query I know by just cant recall how to do this.

The sample data above shows 1 customer with multiple episodes (different attend dates not important here), during the course of these attendances they moved home and moved GP practice.

Is there a simple way in Access to show a summary of this eh PTO1395164 = 2 postcodes, 2 GPs

THe ultimate aim would be to identify where a customer has changed postcode or GP within a selected timeframe and disregard the rest.

Also assume each row is a different day the top being day 1, the bottom being day 12.  So using the sample data you can see the customer changed GP on day 3 and changed postcode on day 11.

Hi ProfessorFudger,

I have checked the sample data you have provided and it seems you have three field "LOCALID","POSTCODE","GPCODE", you want to count the distinct of PSOTCODE and GPCODE and also want to know when the PSOTCODE and GPCODE have changed, right?

If so, you can use the function countdistinct() to get the distinct number like below:

GPCODE:     =countdistinct(Fields!GPCODE.Value,"DataSetName")
POSTCODE: =countdistinct(Fields!POSTCODE.Value,"DataSetName")

If you want to know when the POSTCODE have changed, you can use the expression as below to get the number or row:

Expression1:
=SUM(IIF(Fields!POSTCODE.Value="DN34 1AB",1,0))+1
Expression2:
=SUM(IIF(Fields!GPCODE.Value="G9999981",1,0))+1

Preview:

If I have some misunderstanding, please try to provide more details information about your requirements(expect output and report structure you have designed).

Regards,
Vicky Liu

 SELECT DISTINCT S1.LocalId, TotalPostCode,TotalGPCode
FROM DATASET S
INNER JOIN
(SELECT LOCALID, COUNT(DISTINCT POSTCODE) AS TotalPostCode
   FROM DATASET
GROUP BY LOCALID) S1 ON S.LOCALID = S1.LOCALID
INNER JOIN
(SELECT LOCALID, COUNT(DISTINCT GPCODE) AS TotalGPCode
   FROM DATASET
GROUP BY LOCALID) S2 ON S1.LOCALID = S2.LOCALID

This was the final answer which worked just as well as Vivky's kind answer, again huge thanks to all here, Im guenuinely appriciative.