Results 1 to 6 of 6
  1. #1
    Carnivore is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    3

    Question Query to Extract Data from Table A for Customers in Table B

    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?




  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    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

  3. #3
    Carnivore is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    3
    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.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    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.

  5. #5
    Carnivore is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    3
    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];

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Try this:
    Code:
    SELECT [TableA].[CustomerTableA], Sum([TableA].SalesTableA) AS SumOfSalesTableA
    FROM [TableA] INNER JOIN [TableB] ON [TableA].[CustomerTableA] = [TableB].[CustomerTableB]
    GROUP BY [TableA].[CustomerTableA];
    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.

    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-29-2013, 02:39 AM
  2. Replies: 1
    Last Post: 03-04-2013, 08:09 PM
  3. Emailing Customers with data from a table
    By Elbows in forum Access
    Replies: 1
    Last Post: 05-24-2012, 12:21 PM
  4. Replies: 3
    Last Post: 05-13-2010, 08:18 PM
  5. Delete query, deleting customers froma table.
    By keithsrobinson in forum Queries
    Replies: 2
    Last Post: 02-14-2006, 11:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums