I have numerous tables with different dates, when i try to make a calendar from them the data is duplicated for every date in the tables. My script looks like this -
DateLink: Load NHSNo, Date([SDT],'dd/MM/yyyy') as DATE, Month([SDT]) as Month, 'Episode' as ServiceTable, Year([SDT]) as Year RESIDENT Episode;
End if
If vIntermediateCareLoadY1 = 'Yes' Then Concatenate Load NHSNo, Date([IntermediateCare.ContactDATE],'dd/MM/yyyy') as DATE, Month([IntermediateCare.ContactDATE]) as Month, 'IntermediateCare' as ServiceTable, Year([IntermediateCare.ContactDATE]) as Year RESIDENT IntermediateCare;
End if
If vIntermediateCareLoadY1 = 'Yes' Then Concatenate Load NHSNo, Date([Community_Therapies.ContactDATE],'dd/MM/yyyy') as DATE, Month([Community_Therapies.ContactDATE]) as Month, Year([Community_Therapies.ContactDATE]) as Year, 'Community_Therapies' as ServiceTable RESIDENT Community_Therapies;
End if
If vIntermediateCareLoadY1 = 'Yes' Then Concatenate Load NHSNo, Date([Community_Nursing.ContactDATE],'dd/MM/yyyy') as DATE, Month([Community_Nursing.ContactDATE]) as Month, Year([Community_Nursing.ContactDATE]) as Year, 'Pathology' as ServiceTable RESIDENT Community_Nursing;
End if
If vSocialCareContractLoadY1 = 'Yes' Then Concatenate Load NHSNo, Date([Social_Care_Dom_Care.START_DATE],'dd/MM/yyyy') as DATE, Month([Social_Care_Dom_Care.START_DATE]) as Month, Year([Social_Care_Dom_Care.START_DATE]) as Year, 'Pathology' as ServiceTable RESIDENT Social_Care_Dom_Care;
End if
If vSocialCareContractLoadY1 = 'Yes' Then Concatenate Load NHSNo, Date([Social_Care_Carehome.START_DATE],'dd/MM/yyyy') as DATE, Month([Social_Care_Carehome.START_DATE]) as Month, Year([Social_Care_Carehome.START_DATE]) as Year, 'Pathology' as ServiceTable RESIDENT Social_Care_Carehome;
End if
If vPathologyLoadY1 = 'Yes' Then Concatenate Load NHSNo, Date(Pathology.SIDate,'dd/MM/yyyy') as DATE, Month([Pathology.SIDate]) as Month, Year([Pathology.SIDate]) as Year, 'Pathology' as ServiceTable RESIDENT Pathology;
End if
If vRadiologyLoadY1 = 'Yes' Then Concatenate Load [NHSNo], Date([Radiololgy.SIDate],'dd/MM/yyyy') as DATE, Month([Radiololgy.SIDate]) as Month, Year([Radiololgy.SIDate]) as Year, 'Radiololgy' as ServiceTable RESIDENT Radiololgy;
End if
If vTherapiesLoadY1 = 'Yes' Then Concatenate Load [NHSNo], Date([Therapies.SIDate],'dd/MM/yyyy') as DATE, Month([Therapies.SIDate]) as Month, Year([Therapies.SIDate]) as Year, 'Therapies' as ServiceTable RESIDENT Therapies;
End if
Do i need a further step for my calendar so I'm not getting duplicates?
If the NHSNo-field exist in all tables, there will already be a synthetic key without putting the calendar. If the tables are similar, it makes sense to concatenate the tables first and then place a calendar on it.
