Duplicated data in query problem

[werafa]

Hi all,

I've just commenced my career as an access developer, and come off on the first bend.

I've created linked tables to my source data in excel

SELECT T1.*, 1 AS SheetSourceFROM [Excel 8.0;HDR=YES;IMEX=1;Database=D:\tim\Documents\OneDrive\copy & SEO\First National\SAS\Metrics\Grace_SAS Metrics Ver 45.xlsb].[Data Entry$A10:K65536] AS T1;




I've created a merge query

SELECT CharmSASData.Date, CharmSASData.Person, CharmSASData.Org, CharmSASData.Role, CharmSASData.Dept, CharmSASData.Activity, CharmSASData.Cost_Centre, CharmSASData.Start, CharmSASData.Endfrom CharmSASData


UNION


SELECT GraceSASData.Date, GraceSASData.Person, GraceSASData.Org, GraceSASData.Role, GraceSASData.Dept,GraceSASData.Activity,GraceSASData.Cost_Centre, GraceSASData.Start, GraceSASData.End
from GraceSASData;




and get 1070 rows of data.
I've created another query to filter and process this data ready for analysis.
I expect ~1060 rows of data, but instead get 64020 rows - with every row being duplicated multiple times.


SELECT [Merge SAS Data].Date, [Merge SAS Data].Person, [Merge SAS Data].Org, [Merge SAS Data].Role, [Merge SAS Data].Dept, [Merge SAS Data].Activity, [Merge SAS Data].Cost_Centre, DateDiff("n",[Start],[End]) AS [Time]FROM [Merge SAS Data], Staff


WHERE ((([Merge SAS Data].Date) Is Not Null) AND (([Merge SAS Data].End) Is Not Null))
ORDER BY [Merge SAS Data].Date DESC;

Does anyone know why this is occurring?

thanks
Werafa

[werafa]

got ut,

access had helpfully added a ref to another table.
delete this ref and all is good

(embarrassed grin)