omb
asked on
Merging & exporting data from 2 tables
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Where it says 'YourTableName' replace it with the query name
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
>>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
Its that! You will have to reduce this if you want to use a crosstab.
Paul
ASKER
Okay, I need to go through the 16,000+ records of TABLE2 and see why there are so many inconsistent "Tel.Type" records...
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!)
:-)
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!)
:-)
DoCmd.TransferText acExportDelim, , "Yourtablename", "C:\FileName.csv"