ERP Query with MS Access

Hi, In previous post after you created ODBC to link to MSSQL server. Now, below are the simple steps of    showing customer address with payment term in MS Access.

1.Create a blank ms access file and then decide whether to use linking or importing from the server. Normally this is called indirect access driver where the data source is not stored locally. Let's take the linking  for example. Linking to data is faster than importing because data is not duplicated though.

2. To do the linking, go to MENU\EXTERNAL DATA\ then open ODBC database. Then choose "Link to data source by creating a linked table". After that, you will be led to ODBC screen and pls choose the DSN that you created previously. You will then able to see a list of all the tables created in the database of the SQL Server.

3. In order to query all the customers with A/R info like payment term. We need to pick tables like Address master table, address detail table and customer master table. After adding the 3 tables. You would need to create query to display required records from the table. To create a query out of table. Go to MENU\CREATE\QUERYDESIGN   -  Name it as "qrycustomer"

4.In order to links these 3 tables together, you must link them up using either primary key or secondary key provided they are same field type. After linking, pls define the join type.  Join type would be inner , left and right join. Pls refer below illustration.

5.After that, you may double-click the fields to be display on screen. The field selected can be renamed Eg. Customer : ABALPH

Why do i need these 3 tables instead of 1? Hmm, it depends on the how your data are stored. Some fields are stored in table C where it's not in Table B or A. In above example, we just need to see those customer w/ payment term defined meaning not all the customers will be shown. Therefore, we use left join to show only those appear in customer master table.

6.After creating the query, you may click the "Run!" to display result. If everything ok, now you may go to MENU\CREATE\REPORT WIZARD See below picture..

7.Just go stepbystep to finish the wizard. At the end, you would see something like below :

Hope it helps.



























note : Demonstrated in MS Access ver 2010 with linking to JDedward database JDE1