Sage 100

 View Only
Back to discussions
Expand all | Collapse all

MS Query for multiple companies

  • 1.  MS Query for multiple companies

    Posted 08-24-2022 11:49
    I am trying to pull data for seven different companies into one Excel Sheet using MS Query. I am able to pull each company into a different sheet and manually combine or do a pivot but I need to automate the process and have all the data come into one single Excel sheet, one company after another. It's a simple query pulling only from the AR_InvoiceHistoryHeader file. I'm sure it's possible with SI or some other tool but I need to use MS Query for this project. Is this possible? Sage100 2021 with latest Microsoft365.
    TIA

    ------------------------------
    Kenny Daniel
    TechnoClarity, Inc.
    ------------------------------


  • 2.  RE: MS Query for multiple companies

    Posted 08-24-2022 12:15
    Can you use a SQL Express or MS Access database to do the union?  That would be much easier.

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------



  • 3.  RE: MS Query for multiple companies

    Posted 08-24-2022 12:35
    The trick is getting the linked servers working... after that, merging data into one data source is super simple.


    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message


  • 4.  RE: MS Query for multiple companies

    Posted 08-24-2022 13:38
    Thanks everyone. All great solutions, some are over my paygrade, others require investment which the customer doesn't want to make at this time. I was really hoping to be able to simply use MS Query since it's already somewhat setup and connects to various other files. I'm guessing that's not possible. Appreciate all the suggestions.

    ------------------------------
    Kenny Daniel
    TechnoClarity, Inc.
    ------------------------------

    Original Message


  • 5.  RE: MS Query for multiple companies

    Posted 08-24-2022 13:41
    You may be able to use an Excel macro to automate combining the worksheets created by the query.

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Original Message


  • 6.  RE: MS Query for multiple companies

    Posted 08-24-2022 15:01
    SQL Express is free.

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message


  • 7.  RE: MS Query for multiple companies

    Posted 08-24-2022 12:24
    With a Sage Intelligence connector license this would be pretty straightforward.

    ------------------------------
    Bill Pfahnl
    Nims & Associates
    ------------------------------



  • 8.  RE: MS Query for multiple companies

    Posted 08-24-2022 12:47
    We've created consolidated Crystal reports using Access and PTQ/union query.  Depending on the end product it may be easier to spend the money for an SI connector license.

    ------------------------------
    Jeff Schwenk
    Bottomline Software, Inc.
    (540) 221-4444
    ------------------------------

    Original Message


  • 9.  RE: MS Query for multiple companies

    Posted 08-24-2022 14:41
    Power BI may be able to do this.  @Dan Burleson ????
    ​

    ------------------------------
    Jeff Schwenk
    Bottomline Software, Inc.
    (540) 221-4444
    ------------------------------

    Original Message


  • 10.  RE: MS Query for multiple companies

    Posted 08-24-2022 15:53

    My Sage100Shadow Access database does this just by entering the company codes and credentials into a "Company" table and it automatically creates a single table with all company's data plus a column for the company code for each row. It's cheap. Not as cheap as SQL Express, but you will be going in 5 minutes. Give me a shout.



    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    ------------------------------

    Original Message


  • 11.  RE: MS Query for multiple companies

    Posted 08-24-2022 22:55
    @Dan Burleson please call me (213)-200-5937 ​or email me to let me know how it works and what it costs. Thanks.

    ------------------------------
    Kenny Daniel
    TechnoClarity, Inc.
    ------------------------------

    Original Message


Related Content