none
ACCESS VBA to generate query RRS feed

  • Question

  • Hi All,

    My ACCESS QUERY 1 uses inner join with 2 tables and generates data like below.

    Date                Customer_ID                UNIT                        

    9/3/2015             AAA                            300

    9/4/2015            BBB                            400

    9/5/2015              AAA                           500

    9/8/2015             AAA                            600

    9/10/2015             CCC                         700

    9/12/2015             FFF                            750

    9/13/2015             CCC                            250

    There is a separate Access table "WeekTbl" which has data like below

    WEEK                    DAYS

    9/4/2015               9/3/2015 

    9/4/2015               9/4/2015

    9/4/2015               9/5/2015

    9/8/2015               9/7/2015

    9/8/2015               9/8/2015

    9/8/2015               9/9/2015

    9/11/2015             9/10/2015

    9/11/2015             9/11/2015

    9/11/2015             9/12/2015

     My Requirement - I want to create EXCEL REPORT that shall have data like below.

    Week           Count of ID     Total Unit  

    9/4/2015      02                  1200   ( Take all days in week 9/4/2015 from WeekTbl, Count Cusotmer-ID 

                                                                    and SUM Unit from my access query 1)      

    9/8/2015      01                   600    ( Take all days in week 9/8/2015, Count Cusotmer-ID and SUM unit)

    9/11/2015     02                  1450    ( Take all days in week 9/11/2015 , Count Cusotmer-ID and SUM unit)


    Currently i am copying all the data generated by ACCESS QUERY 1  into excel and apply filter manually by looking which days to include from table  "WeekTbl". Using SUM function i m getting Total units and using COUNTIF to get the count of each ID. But i have nearly 50 weeks to filter each time so its very time consuming process.

    I want to automate the process of generating EXCEL REPORT by VBA or Access query or excel which will do the following

    1. Read each week from table "WeekTbl"
    2. Count number of Customer_ID  for the days that fall under that particular week from my access query 1
    3. Sum Unit for the days that fall under that particular week From my access query 1


    Is it possible to create 3 new field in my ACCESS QUERY 1 OR create new query which will give Week, Count of ID and SUM of Unit?

    Thanks,

    Zaveri




    • Edited by zaveri cc Saturday, September 5, 2015 3:23 PM
    Saturday, September 5, 2015 2:54 PM

Answers

  • TESTED --

    SELECT WeekTbl.Week, [ACCESS QUERY 1].Customer_ID, Sum([ACCESS QUERY 1].UNIT) AS SumOfUNIT
    FROM WeekTbl INNER JOIN [ACCESS QUERY 1] ON WeekTbl.Days = [ACCESS QUERY 1].Date
    GROUP BY WeekTbl.Week, [ACCESS QUERY 1].Customer_ID
    ORDER BY WeekTbl.Week;

    SELECT Query15.Week, Count(Query15.Customer_ID) AS CountOfCustomer_ID, Sum(Query15.SumOfUNIT) AS SumOfSumOfUNIT
    FROM Query15
    GROUP BY Query15.Week;


    Build a little, test a little

    Saturday, September 5, 2015 4:56 PM

All replies

  • Hi. You should be able to use a second query to get the final result by joining the first query with the week table using a non-equi join. For example:

    SELECT T1.[Week], Count(Q1.ID) As CountOfID, Sum(Q1.Unit) As TotalUnit
    FROM WeekTbl As T1
    INNER JOIN [Access Query 1] As Q1
    ON Q1.[Date]>=T1.[Days]
    AND Q1.[Date]<=T1.[Days]
    GROUP BY T1.[Week]

    (untested)

    Hope that helps...


    • Edited by .theDBguy Saturday, September 5, 2015 4:35 PM
    Saturday, September 5, 2015 4:34 PM
  • TESTED --

    SELECT WeekTbl.Week, [ACCESS QUERY 1].Customer_ID, Sum([ACCESS QUERY 1].UNIT) AS SumOfUNIT
    FROM WeekTbl INNER JOIN [ACCESS QUERY 1] ON WeekTbl.Days = [ACCESS QUERY 1].Date
    GROUP BY WeekTbl.Week, [ACCESS QUERY 1].Customer_ID
    ORDER BY WeekTbl.Week;

    SELECT Query15.Week, Count(Query15.Customer_ID) AS CountOfCustomer_ID, Sum(Query15.SumOfUNIT) AS SumOfSumOfUNIT
    FROM Query15
    GROUP BY Query15.Week;


    Build a little, test a little

    Saturday, September 5, 2015 4:56 PM
  • Hi Karl. I guess you're right. We don't need a non-equi join because all the dates are listed as separate records anyway. Thanks.
    Saturday, September 5, 2015 5:16 PM
  • Hi Karl,

    Thanks for your response. I missed to mention 1 important thing that In my ACCESS QUERY 1, i am getting Unit by   SUM(Group by [Field1] multiply it by Sum(field2)

    UNIT= SUM(Group by [Field1] * Sum(field2)

    What change shall i make?

    As per your solution, i have to create 2 separate queries?

    In SQL there is Distinct count function. Is there something like that in Access ?

    Thanks,

    Zav


    • Edited by zaveri cc Saturday, September 5, 2015 9:58 PM
    Saturday, September 5, 2015 9:54 PM
  • Hi Zav. Maybe you can post the SQL statement of your Query 1.
    Saturday, September 5, 2015 9:58 PM
  • Thanks DB.
    Saturday, September 5, 2015 9:58 PM
  • UNIT= SUM(Group by [Field1] * Sum(field2)

    This is not understood - what are you trying to do?   You cannot have a GROUP BY inside of a SUM.

    You have two open parenthesis and one close.


    Build a little, test a little


    Sunday, September 6, 2015 1:45 AM
  • It seems like you have an Access solution, or even a couple solutions.  If I understand correctly, you also want to move the data from Access into Excel.  Please try one of the code samples here.

    http://www.erlandsendata.no/english/index.php?d=envbadacimportado

    http://www.erlandsendata.no/english/index.php?d=envbadacimportdao

    Then, you can simply create a Pivot Table and get the data in the structure you described.  Simply turn on the Macro Recorder before you start creating your Pivot.  All the code will automatically be generated for you.  You may have to play with the VBA just a bit to get it to do exactly what you need it to do.  It should be pretty simple to understand and work on.

    Good luck.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, September 6, 2015 12:02 PM
  • Hi Zav,

    >> i am getting Unit by   SUM(Group by [Field1] multiply it by Sum(field2)
    As the reply from KARL, you could not use something like this. If you have made it worked, I suggest you post your sql statement.

    >> i have to create 2 separate queries?
    With a complex sql query, I suggest you split it into two simple queries. It would be better to modify and be easier to understand when you check the sql statement later.

    >> In SQL there is Distinct count function. Is there something like that in Access ?
    I am not sure what you mean with “distinct count”, do you mean with distinct function and count function. If so, they are supported in Access.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, September 11, 2015 6:17 AM