Merge Data in One Column from One Data Sheet with Another

[treym]

I am VERY new to Access. I have managed to pull in 2 different Excel spreadsheets and can run a query combining the information need from between the two. However I have a column named the same in both with different sales numbers. So let's just call it Sales. When I query them I get 2 distinct columns of "Sales". How to I get it so those merge into one? Things are linked via an account number so to speak. So one might say $10 in "Sales" from worksheet 1, while the other might say $5. I'd like just one column that says $15. Is that possible?
Many Thanks,

 

[Joe4]

Yes, you just do a calculated field in a query that adds those two sales fields together. I would do it like this:

TotalSales: Nz([Sheet1].[Sales],0) + Nz([Sheet2].[Sales],0)

What the NZ function does is convert any blanks or nulls to zero, so if you have any nulls or unmatched values, it will still do the math without errors.

If you have any issues incorporating this, go into the query that you currently have that lists both sales fields, change to SQL View, and copy and paste the SQL code here. And then we can help you edit the code.

 

[treym]

Thanks, lets see if I completed this correctly. This is the code I modified: TotalUnsaleableCredits: Nz([COGNOS DATA].[Unsaleable Returns Total] AS [COGNOS DATA_Unsaleable Returns Total],0) + Nz([WMtgtSAMSrawData].[WMtgtSAMSrawData_Unsaleable Returns Total],0) ...but I'm sure I've done something wrong as I get an error that states "Characters found after end of SQL statement." I assume I replaced "Sheet 1" and "Sheet 2" with the actual table names?

 

[Joe4]

Just use:

[COLOR=#333333]TotalUnsaleableCredits: Nz([COGNOS DATA].[Unsaleable Returns Total],0) + Nz([WMtgtSAMSrawData].[WMtgtSAMSrawData_Unsaleable Returns Total],0)[/COLOR]

the " AS [COGNOS DATA_Unsaleable Returns Total]" part should not be in the middle of your equation.

If you aren't sure how to add calculated fields in an Access query, have a look here: [/COLOR]http://www.gcflearnfree.org/access2013/creating-calculated-fields-and-totals-rows/1/

 

[treym]

Thanks for the help and reference link!