+ Reply to Thread
Results 1 to 6 of 6

joining two queries

  1. #1
    Registered User
    Join Date
    11-21-2011
    Location
    Akron, OH
    MS-Off Ver
    Excel 2010
    Posts
    28

    joining two queries

    Hello, I am very limited to what I can do in access. What I can do is go into excel and import a query from access into excel. Then in excel I use it. The queries link directly with our software at work(this may be the case everywhere I'm not sure). My ultimate goes here is to export a new table query to an excel sheet that shows me the following information.

    Product Name, Product Class, PostDate(the month the product was sold in), Quantity, Amount, Customer Number

    OK, so if I go to Data - from microsoft query. I then choose MS Access Database. I find my database. Then in the Query Wizard where where I have available tables and columns on the left and columns in your query on the right. Here I am moving over two tables. The first table in our system is called MonthlySales This consists of Product Name, Product Class, PostDate, Quantity, and Amount. The second table I am moving over to the right is called CustomerFile, and this consists of Product Name and Customer Number.

    I believe I need to move the 2nd one "CustomerFile" over because I need the customer numbers for the sales to come through to excel. So I do this and it tells me that they do not link together and it takes me to design view I believe it is. Here I join Product Name from "monthlysales" and make a joining arrow to Product Name from "customerfile".

    I am then sorting by Product Name, then by customer number.

    Then when I return information to excel here is the problem I am getting. It is listing the Product 24 times with one customer number. Each time it is a different month for the past 2 years. The sales per month are different do I thought I was successful. Then 24 rows down for the same product it lists 24 more rows for a different customer number and the quantity of sales it returns are the same as for the first customer.
    It is doing this for all 20 customers we have for this product. It may be summing them all up between all customers I am not sure.

    I know this is probably hard to understand what I am doing without seeing it.
    I can provide whatever info necessary for any help. This may be too specific to our software and if so I apologize. Thank you

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: joining two queries

    What would you like the results to look like? What are you trying to accomplish with this query? Suggest you also post your SQL statement for your query after you tell us specifically what you want the results to be. Without that information, we are shooting darts blindfolded after being spun in a circle.

    Alan
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    11-21-2011
    Location
    Akron, OH
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: joining two queries

    The SQL statement is:

    SELECT `*MONTHLYSALES`.PNAME1, `*MONTHLYSALES`.pmast, `*MONTHLYSALES`.PCLASS, `*MONTHLYSALES`.POSTDATE, `*MONTHLYSALES`.QNTY, `*MONTHLYSALES`.AMT, `*MONTHLYSALES`.TOTCOST, `*MONTHLYSALES`.PROF, CPFILE2.CNUM, CPFILE2.PNUM, CPFILE2.MEAS
    FROM `S:\chempax.mdb`.`*MONTHLYSALES` `*MONTHLYSALES`, `S:\chempax.mdb`.CPFILE2 CPFILE2
    WHERE CPFILE2.PNAME1 = `*MONTHLYSALES`.PNAME1 AND ((`*MONTHLYSALES`.PCLASS='SYNTH'))
    ORDER BY `*MONTHLYSALES`.PNAME1, CPFILE2.CNUM, `*MONTHLYSALES`.POSTDATE

    I want the results of the query to be a table in excel with the following columns:
    Product Name, Product Class, PostDate, Quantity, Amount, Customer Number.

    The SQL statement may have more columns in it such as amount and totcost, this is okay with me.

    I am returning all the correct columns that I want, they just are not acting right. The amount column(which shows me sales in lbs) is repeating the same numbers for every customer number.

    Thank you

  4. #4
    Registered User
    Join Date
    11-21-2011
    Location
    Akron, OH
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: joining two queries

    To make it simple, it is giving me something like this. it is repeating the sales each month at the total amount of all customers. Instead of giving me that customers specific sales.

    Product Name CNUM POSTDATE Amount
    Product 1 1 Jan 10,000
    Product 1 1 Feb 20,000
    Product 1 1 Mar 30,000
    Product 1 1 Apr 50,000
    Product 1 1 May 40,000
    Product 1 1 Jun 30,000
    Product 1 1 Jul 10,000
    Product 1 1 Aug 50,000
    Product 1 1 Sep 10,000
    Product 1 1 Oct 20,000
    Product 1 1 Nov 20,000
    Product 1 1 Dec 30,000
    Product 1 2 Jan 10,000
    Product 1 2 Feb 20,000
    Product 1 2 Mar 30,000
    Product 1 2 Apr 50,000
    Product 1 2 May 40,000
    Product 1 2 Jun 30,000
    Product 1 2 Jul 10,000
    Product 1 2 Aug 50,000
    Product 1 2 Sep 10,000
    Product 1 2 Oct 20,000
    Product 1 2 Nov 20,000
    Product 1 2 Dec 30,000

    When the correct answer is this.

    Product Name CNUM POSTDATE Amount
    Product 1 1 Jan 5,000
    Product 1 1 Feb 10,000
    Product 1 1 Mar 15,000
    Product 1 1 Apr 25,000
    Product 1 1 May 20,000
    Product 1 1 Jun 15,000
    Product 1 1 Jul 5,000
    Product 1 1 Aug 25,000
    Product 1 1 Sep 5,000
    Product 1 1 Oct 10,000
    Product 1 1 Nov 5,000
    Product 1 1 Dec 15,000
    Product 1 2 Jan 5,000
    Product 1 2 Feb 10,000
    Product 1 2 Mar 15,000
    Product 1 2 Apr 25,000
    Product 1 2 May 20,000
    Product 1 2 Jun 15,000
    Product 1 2 Jul 5,000
    Product 1 2 Aug 25,000
    Product 1 2 Sep 5,000
    Product 1 2 Oct 10,000
    Product 1 2 Nov 5,000
    Product 1 2 Dec 15,000

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: joining two queries

    It appears that you need an aggregate query that sums on the product by customer by date.

    Look here for how to create aggregate queries in Access.

    Scroll down to the Group By Features on this site

    http://www.techonthenet.com/access/queries/index.php

    You might find that it will be easier to do the query directly in Access and then either link or export the results to Excel.

  6. #6
    Registered User
    Join Date
    04-25-2012
    Location
    Islamabad.
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: joining two queries

    I also want to know that.
    http://phproots.com

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1