Excel clinic - Exporting multiple columns to Access

Posted by AccountingWEB PM | on Fri, 05/08/2005 - 09:12 2012 2 comments

Importing data from Excel into Microsoft's Access database is straightforward, but in this ExcelZone tutorial, Simon Hurst explains how to set up imports from different columns containing details on different clients. A Word version of the tutorial, with illustrations, can also be downloaded.

One of our clients wanted to import some data from Excel into an Access table in order to produce a particular report.

Continued...

SQL - all is not lost!

listerramjet | Fri, 05/08/2005 - 09:26 | Permalink

You can use the Access query builder to create the first query, and then you can view the resulting SQL and copy it, and then paste it to the new union query. You don't have to remember the SQL syntax - which is good because Access has its own flavour of SQL, which is one of many.

You could use query builder to include the cust table more than

jtomlin2 | Mon, 08/08/2005 - 12:12 | Permalink

Within query design view if you add a table more than once it appends an _n suffix (where n is the next iteration of the table added).

You can then create the link between the customer table and the relevant field in the main table so you can pull back the contact details. When you link be sure to choose the link option which gives you all data from main and only linked fields for the contact details table otherwise you may get incompatible joins between the 5 tables.

This means that you are linking the same table four times so depending on the size of the tables the query may run slowly.

HTH,

JamieT