Combining 2 tables with different fields, joining on a common ID (key) field... putting all the fields in 1 final table, populating 0 for any nulls
I'm creating an app to calculate a score based on attribute values. Some of the calculations are complex and are putting some fields in a table of their own called 'Result', while the rest of the attribute scores are calculated and have a resident table called 'Scores'. Both tables have the key field [BPARTNER].
Table 'Scores' has more BPARTNERs than the 'Result' table... I would like to append the 'Result' score fields to the 'Scores' table joining on BPARTNER and just have 0 instead of Nulls where 'Result' can't match on [BPARTNER].
What type of join would I need and how do I append those 'Result' score fields to the 'Scores' table? I'm thinking this will resolve the issue of not being able to set Nulls which actually don't exist in my result table to zeros by possibly forcing zeros with the table join.