RE: AS400 Query Question -- MAPICS-L

I wrote a set of instructions on how to create the connection and use it
from within Excel for another organization. I will try to find it over the
weekend and update the screen captures for our current Client Access. Let me
know if you want a copy of it and I will send it to you. 

There are a lot of steps involved in creating the ODBC connection but you
can use the same on for all of your AS/400 connections or you can build them
to be specific to each task or for security. Speaking of security, I usually
elect not to allow the ODBC to update AS/400 data except through specific
controlled connections. 

It is also necessary to have installed the MS Query with office or to at
least go back and add it; it does not install with the "typical" option. To
see if you have it, you can select "Get External Data" from the Data menu on
the Excel tool bar. If the options to Run Web Query, Run Database query and
Create New Query are not greyed out, you have the MS query installed.

You can also save the queries and run them to refresh the data when ever you
wish.

Have a great weekend everyone.


Jon Le Roi
Director of Information Systems
Tegal Corporation

 
-----Original Message-----
From: Rusty Jackson [mailto:rustyjackson@xxxxxxxxxxx]
Sent: Friday, April 11, 2003 1:24 PM
To: MAPICS ERP System Discussion
Subject: Re: AS400 Query Question


Jon:

I am a third party consultant/programmer that works in a lot of MAPICS
accounts, small and large.  I constantly run into situations where your
solution with ODBC and Microsoft products is exactly what the client needs
to use.  However, more often than not, the end-users in small companies
without in-house IS expertise do not have any idea how to go about setting
up a ODBC connection and then using it with their Microsoft products,
Access/MS QUERY/Excel/etc.

Rusty Jackson
Rusty Jackson And Associates
P.O. Box 20193
Knoxville, TN  37940

----- Original Message ----- 
From: <jleroi@xxxxxxxxx>
To: <mapics-l@xxxxxxxxxxxx>
Sent: Friday, April 11, 2003 4:19 PM
Subject: RE: AS400 Query Question


> Nandu,
>
> As you can see there are very many ways to solve this. Since the
destination
> of a lot of my work ends up being Excel, I use Micrsoft query from within
> Excel along with an IBM Client Access ODBC connection to my MAPICS files;
> any AS/400 ODBC will work.
>
> I have been using SQL and Microsofts Query Wizard for years to extract
data
> to either WExcel or Access in order to produce reports with better graphs,
> pivot tables or just to provide raw data to an Excel Power user so they
can
> slice and dice to their hearts content.
>
> Good Luck finding what works best for you and let me know if you need any
> assistance with setting up ODBC connections.
>
> Regards,
> Jon Le Roi
> Director of Information Systems
> Tegal Corporation
>
> -----Original Message-----
> From: Nandagopal Padmanabhan [mailto:npadmanabhan@xxxxxxxxxxxxxx]
> Sent: Friday, April 11, 2003 10:58 AM
> To: mapics-l@xxxxxxxxxxxx
> Subject: AS400 Query Question
>
>
> Hello Group,
> I'm new to MAPICS and AS400 queries, but do have experience writing SQL
> queries. I've been working on our MAPICS implementation for the last 4
> months and this is the first time I'm writing AS400 queries. Basically,
I'm
> having trouble trying to incorporate GROUP BY aggregate function in an
AS400
> query. I guess I haven't defined my report summary functions and report
> breaks the right way. I would like to query ITEMBL file to get a total
> on-hand inventory value for all warehouses. The SQL version of this would
> look like this:
>
> SELECT HOUSE, Sum(AVCST* MOHTQ) AS INVENTORY_VALUE
> FROM ITEMBL
> GROUP BY HOUSE
>
> HOUSE = Warehouse
> AVCST = Avg Unit Cost
> MOHTQ = On Hand Total Quantity
>
> I would like my report to look like this:
>
> Warehouse INVENTORY_VALUE
> PBC $107,117.41
> PBD $157,116.41
> PBX $257,116.41
>
> Any suggestions on how to accomplish the above will be appreciated. Could
> you also point me towards good online documentation/books on AS400
queries.
>
> Thanks,
> Nandu
> Nandagopal Padmanabhan | Vestas-AWT
> (503) 327-2050 | npadmanabhan@xxxxxxxxxxxxxx
>
>
>
> _______________________________________________
> This is the MAPICS ERP System Discussion (MAPICS-L) mailing list
> To post a message email: MAPICS-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/mapics-l
> or email: MAPICS-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/mapics-l.
> _______________________________________________
> This is the MAPICS ERP System Discussion (MAPICS-L) mailing list
> To post a message email: MAPICS-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/mapics-l
> or email: MAPICS-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/mapics-l.
>

_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list
To post a message email: MAPICS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.