Quote
All the source data is populated into a master table, with multiple rows being created for each record.
JOIN
db.outletdata OUTL
ON SEG.record_number = OUTL.record_number
But if you have multiple "lines" for each "record", then joining on this one field will give you duplication which, I suspect, is what you're seeing.
It also suggests that your data is not properly normalised.
Start by cutting your query down so that it only uses two tables (or the same table twice), but select everything from both.
That should show you the duplicated values that are giving you the trouble.
Then, as others have already asked, show us your table structures and some sample data so that we can recreate and, hopefully, help you solve the problem.
Regards, Phill W.