LEFT JOIN creating Duplicate in SUM

Question

So I was wondering if I could get some help withi this:

I have 2 seperate tables:

Projects
ProjectID	ProjectName	ProjectCost
123456	Tilling Kitchen	150
545454	Painting Hallway	100
125877	Wallpapering Bedroom	200

Jobs
JobID	Job	ProjectID
1	Buy Grout	123456
2	Take Measurements	123456
3	Bought Paper	125877

But because I need a table that shows projects whether they have a job or not, I set up a new Merged table as below:

AllProjectsAndJobs
ProjectID	ProjectName	ProjectCost	JobID	Job	ProjectID
123456	Tiles and Tubs	150	1	Buy Grout	123456
123456	Tiles and Tubs	150	2	Take Measurements	123456
545454	Paint and Brushes	100
125877	Wallpaper and Glue	200	3	Bought Paper	125877

The problem I have is that because project 123456 has 2 jobs and I want a table to just show the Project and its costs, its duplicating the value for 123456 as it has 2 job entries:

How do I get around this?

Thanks

amitchandak · Accepted Answer

VistaDee , You can create a join between these two assume projects has unique project only. 1-M join(No need to merge) 
&nbsp;
a measure like this should have give you projects with no jobs too
count(Jobs[ProjectID])+0

Forum Discussion

LEFT JOIN creating Duplicate in SUM

1 Reply

Related content

Duplicate ranking

Create table to remove duplicates

Working with copy job and creating duplicates

Creating Relationships with duplicative values

Table Merge creates duplicate records