Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need some guidance improving reports

    I work for a manufacturing facility that is using a canned Visual Foxpro database. It works decently for what we do but the reports have always been lacking. Before I run off and purchase some 3rd party application, I thought I would see what I could do using the standard Microsoft applications and VBA.

    So far, using Excel, I have been able to query a single table and filter the data. I used an OLE DB query with a SQL SELECT FROM WHERE statement. That actually worked out great but to build useful reports I need to extract data from mutliple tables based on common entries.

    Example: I need to pull a report that uses data from a table called SHIPPING and a table called SORD. They both use a sales order number and I'm assuming this is what links them together in the database.

    So my question is: Can I accomplish what I want just using Excel? Will the query tool be enough or am I going to have to write custom code? Is there a better application for this, maybe Crystal Reports?

    Thanks to all in advance. Look forward to responses. -Daniel

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,180
    Thanks
    47
    Thanked 983 Times in 913 Posts
    You can use SQL statements to query anything in your database if you know the syntax, or you can buy a 3rd party tool that lets you select the items and links, like Crystal Reports or the free SQL Power Wabit.
    To pull data from 2 or more tables you need a JOIN statement - plenty of examples on the web. Alternatively someone here might help with the statement if you can tell us the table and field names.

    cheers, Paul

  3. #3
    New Lounger
    Join Date
    Jul 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Paul,

    Do you know if the standard Query Command Text in the Import Data Tools will handle a more complex JOIN query or will I need to do this in the code editor?

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,180
    Thanks
    47
    Thanked 983 Times in 913 Posts
    Probably depends on the version of Excel. I've not used 2007/10 extensively, 2003 is fairly basic in it's data collection abilities.

    cheers, Paul

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    St. Joseph, MI, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    We use Crystal Reports for most of our standard reports. I use Excel or SQL Query Analyzer for quick and dirty research. I love Crystal Reports for the ease with which you can create joins and filters, and the user friendly formatting of the reports. For really complicated queries, that require sub-queries or multiple queries, I use MS Access.

Posting Permissions

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