I have two similar tables for two different companies that include: Customer name, Product, Sales Date, Sales Amount, etc. I would like to identify the customers in Table B who are also in Table A and their Table A sales data. Is this possible?
I have two similar tables for two different companies that include: Customer name, Product, Sales Date, Sales Amount, etc. I would like to identify the customers in Table B who are also in Table A and their Table A sales data. Is this possible?
Create a simple matching query between the two tables, joining on the Customer field, and return whatever fields you like.
See: http://office.microsoft.com/en-us/ac...010096320.aspx
Its a pretty basic task in Access, so just about any introductory Access book will show you how to do this. The built-in Access help files may show you also. And there is a lots of good stuff online (usually, you can find a lot of YouTube presentations on this stuff, if you are a visual sort of learner. Here is one I found rather quickly: http://www.youtube.com/watch?v=yqabnPqyNrk
I've tried the simple matching query, but what it gives me is sales data from both tables. My problem is I have Customer and Sales in both tables. What I'm trying to query is Customers who are on Table B and also in Table A and their Table A sales only.
When creating a query, you decide which fields you want to return. So if you only want the sales from Table A, only elect to return that field. You do not need to return the sales field from Table B.
If you are still having issues figuring it out, switch your query to SQL View, and Copy and Paste the SQL code of you query here so we can see what it is doing.
Thank you for your help. After I build a query in design mode bringing in Customers from Table B and Sales from Table A, I get the SQL code below. The query produces sales data from Table B. I'm trying to get Sales from Table A for Customers in Table B.
SELECT [TableB].[CustomerTableB], Sum([TableA].SalesTableA) AS SumOfSalesTableA
FROM [TableA] INNER JOIN [TableB] ON [TableA].[CustomerTableA] = [TableB].[CustomerTableB]
GROUP BY [TableB].[CustomerTableB];
Try this:
Note, do not worry that you are returning CustomerTableA instead of CustomerTableB, because you are joining on CustomerTableA = CustomerTableB. So the values should be one and the same.Code:SELECT [TableA].[CustomerTableA], Sum([TableA].SalesTableA) AS SumOfSalesTableA FROM [TableA] INNER JOIN [TableB] ON [TableA].[CustomerTableA] = [TableB].[CustomerTableB] GROUP BY [TableA].[CustomerTableA];
If this does not give you what you want, I think you will need to post a small sample of the data in each table, along with your expected results so we can see exactly what you are working with.