?
Solved

Merging & exporting data from 2 tables

Posted on 2004-08-05
11
Medium Priority
?
201 Views
Last Modified: 2006-11-17
We have an Access 2002 SP2 customer database - several forms are used to view the contents of its tables. We would like to extract info from the tables and export into a CSV file, Excel and eventually Outlook. We want to move away from Access.

There are two important tables as follows:

TABLE1: company, contact, street, city, zip
TABLE2: company, tel type, tel number, email

TABLE1 has over 6,000 entries and the "company" field is unique. TABLE2 has over 16,000 entries, however the "company" field often contains the same company name with several different entries for "tel number" and "email" - hence over 16,000 items instead of 6,000. The field "tel type" has entries such as 'office', 'main', 'private', 'fax'. So in TABLE2 some companies have 4 "company" entries, with different "tel number" entries. Sometimes there are different "tel number" entries for the same "tel type".

That's where I am stuck. I would like to end up with a single row for each company, containing all the relevant info from both tables. This info I would then like to export. Not really sure how to combine info from the 2 tables (entries from both have a link relationship through the "company" field) - do I merge both tables into a 3rd table and then export? or do I create a report and then save as... I really have no idea!!! I've spent hours trying to come up with a solution... now over to the experts for some help!

Many thanks for your tips in advance.
0
Comment
Question by:omb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
11 Comments
 
LVL 8

Accepted Solution

by:
arcross earned 100 total points
ID: 11724321
you can create a query to join them..

SELECT * FROM Table1 LEFT JOIN Table2 ON Table2.Company = Table1.Company

(If Company is your primary key)

Now, the tables will be combined into a query. When you have that, have a look in access help to 'Docmd.transfertext...' to export it

Álvaro
0
 
LVL 8

Expert Comment

by:arcross
ID: 11724337
Ive had a look myself :) Memory fails sometimes....getting old

DoCmd.TransferText acExportDelim, ,  "Yourtablename", "C:\FileName.csv"
0
 
LVL 26

Assisted Solution

by:dannywareham
dannywareham earned 100 total points
ID: 11724342
As arcross says: use a select query to match where the companies are teh same and return all data, and where they don't match, supply the remaining data.
Then use either docmd.transfertext or DoCmd.OutputTo to export it to a file location or DoCmd.SendObject to export as an email attachment.

:-)
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 8

Expert Comment

by:arcross
ID: 11724346
Where it says 'YourTableName' replace it with the query name
0
 
LVL 16

Assisted Solution

by:PaulCaswell
PaulCaswell earned 100 total points
ID: 11724422
I'm guessing you want to export a table containing:

company, contact, street, city, zip, main tel, main e-mail, office tel, office e-mail, ...

Essentially, you must combine the two tables into one and then crosstab the resultant table with companies as the row headings and 'tel type' as the column heading.

To experiment, I setup a table of companies called 'Company' containing just 'Company'. I then setup a table of telephone numbers called 'Tel' with columns 'Company', 'Type' and 'Tel'.

Combine the tables with query:

SELECT Company.Company, Tel.Type, Tel.Tel
FROM Company LEFT JOIN Tel ON Company.Company = Tel.Company;

Then crosstab with:

TRANSFORM First([All].Tel) AS FirstOfTel
SELECT [All].Company
FROM [All]
GROUP BY [All].Company
PIVOT [All].Type;

Note: To create a crosstab query, select 'New...' button while the queries list is displaying.

This may not be the most efficient method but it seems to work.

I hope this helps.

Paul
0
 
LVL 3

Author Comment

by:omb
ID: 11730325
Thanks for all the feedback.

Just on the creating the query bit... the suggestions given by arcross & dannywareham work fine, however the end result produces several separate records for each company as each company has more than 1 record in TABLE2. However, the tables are joined.

PaulCaswell has taken this one step further. Yes, I would like to create a new table by joining the 2 original tables with fields like:

company, contact, street, city, zip, main tel, main e-mail, office tel, office e-mail, ...

I have also experimented with your suggestions. I created new tables like yours and 2 queries (1 being a crosstab query). Worked great - created a single record for each company with different field column headings for the different 'Tel.Type' and corresponding data.

However, when trying this with my 2 tables I get error "too many crosstab column headers (5613)". Could this be a problem with the amount of records in each table? FYI, TABLE1 contains 6775 & TABLE2 16870 records. Or is it something to do with too many different "Tel.Type" entries in TABLE2?

Many thanks for your support.
0
 
LVL 16

Expert Comment

by:PaulCaswell
ID: 11733533
>>Or is it something to do with too many different "Tel.Type" entries in TABLE2

Its that! You will have to reduce this if you want to use a crosstab.

Paul
0
 
LVL 3

Author Comment

by:omb
ID: 11742388
Okay, I need to go through the 16,000+ records of TABLE2 and see why there are so many inconsistent "Tel.Type" records...
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 11745744
Put the table into excel and use a basic autofilter (Alt D, F, F)
You can see all the different values in the dropdown box.
You can convert them easily and append them back into your table.

(ps. I know it's dirty guys!)

:-)
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question