I can see the data but...

For the last few weeks I have been trying to move out Company address
data from a monolithic single table database.

I want to have a list of unique companies and to link that back to the
records in the main table so that instead of cutting and pasting the
whole address data whenever a member moves, I can be in a position to
only need to change the Company ID number in the Membership/Customer
record.

I have had major surgery with less stress than trying to acomplish
what seems such a simple and obvious thing to do!

Right, enough grouching!

So far I have created a new table called Companies. I have appended
the data I wanted into it. The data is a list of company names from
the Main Table. Each company has a unqie ID number, courtesy of an
autonumber field, CompanyID No.

I have also created a field on the main table called CompanyID No.

I can do a query which lists

Company.CompanyID No
Company. CompanyName
MainTable.CompanyID No
MainTable.CompanyName

The Company ID lists 1 to 1500 ok, followed by the corresponding
Company Name company name. Then the same details from the Main Table.
I have tried umpteen times to copy the Company.CompanyID No into the
currently blank MainTable CompanyID No. Absolutely nothing happens.
It is so frustrating. I can see the columns all ligning up. If I
could just cut and past then the job would be done.

Why can't I copy the data fom the same field in one table to the same
field in another table. If this is at all possible, please can
someone explain where I'm going wrong, before I go loopy.

Many thanks for all help given.

David

"David F-B" wrote in message

For the last few weeks I have been trying to move out Company address
data from a monolithic single table database.

I want to have a list of unique companies and to link that back to the
records in the main table so that instead of cutting and pasting the
whole address data whenever a member moves, I can be in a position to
only need to change the Company ID number in the Membership/Customer
record.

I have had major surgery with less stress than trying to acomplish
what seems such a simple and obvious thing to do!

Right, enough grouching!

So far I have created a new table called Companies. I have appended
the data I wanted into it. The data is a list of company names from
the Main Table. Each company has a unqie ID number, courtesy of an
autonumber field, CompanyID No.

I have also created a field on the main table called CompanyID No.

I can do a query which lists

Company.CompanyID No
Company. CompanyName
MainTable.CompanyID No
MainTable.CompanyName

The Company ID lists 1 to 1500 ok, followed by the corresponding
Company Name company name. Then the same details from the Main Table.
I have tried umpteen times to copy the Company.CompanyID No into the
currently blank MainTable CompanyID No. Absolutely nothing happens.
It is so frustrating. I can see the columns all ligning up. If I
could just cut and past then the job would be done.

Why can't I copy the data fom the same field in one table to the same
field in another table. If this is at all possible, please can
someone explain where I'm going wrong, before I go loopy.

Many thanks for all help given.

David

What's the SQL of this query of yours? If you want to update MainTable
so that it is matched against Companies on the CompanyName field, and
each MainTable record that has a match in Companies gets its [CompanyID
No] field set to the [CompanyID No] field of the matching Companies
record (that is, the one with the same CompanyName), then you should be
able to run an update query with SQL like this:

UPDATE MainTable INNER JOIN Companies
ON MainTable.CompanyName = Companies.CompanyName
SET MainTable.[CompanyID No] = Companies.[CompanyID No];

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

On Wed, 23 Jun 2004 16:41:02 -0400, "Dirk Goldgar"
wrote:

"David F-B" wrote in message

For the last few weeks I have been trying to move out Company address
data from a monolithic single table database.

I want to have a list of unique companies and to link that back to the
records in the main table so that instead of cutting and pasting the
whole address data whenever a member moves, I can be in a position to
only need to change the Company ID number in the Membership/Customer
record.

I have had major surgery with less stress than trying to acomplish
what seems such a simple and obvious thing to do!

Right, enough grouching!

So far I have created a new table called Companies. I have appended
the data I wanted into it. The data is a list of company names from
the Main Table. Each company has a unqie ID number, courtesy of an
autonumber field, CompanyID No.

I have also created a field on the main table called CompanyID No.

I can do a query which lists

Company.CompanyID No
Company. CompanyName
MainTable.CompanyID No
MainTable.CompanyName

The Company ID lists 1 to 1500 ok, followed by the corresponding
Company Name company name. Then the same details from the Main Table.
I have tried umpteen times to copy the Company.CompanyID No into the
currently blank MainTable CompanyID No. Absolutely nothing happens.
It is so frustrating. I can see the columns all ligning up. If I
could just cut and past then the job would be done.

Why can't I copy the data fom the same field in one table to the same
field in another table. If this is at all possible, please can
someone explain where I'm going wrong, before I go loopy.

Many thanks for all help given.

David

What's the SQL of this query of yours?

Having spent all my time working on a single table database I am not
up on complex queries. Finding stuff, deleting, appending and making
tables are as far as I've got. See the thread, 'Still Stuck' for
where I got to previously.

Basically I've got two tables and I've listed four records in the
query, two from each and both being identical field names: Company
Number and Company Name basically.

Because the Compant Table data is originally all from Main Table, both
tables share things like Company Name. As the two tables have the
same field I thought I should join them on the Company Name field. So
I dragged Company Name from Main Table list to Company Name Table
list. There is now a line joining them. Clicking on the line I get
Join Properties:-
Left Table Companies
Right Table Main Table
Left Column Field: CompanyName
Right Column Name: Company Name

Option 1 (Only Include rows where the joined fields are equal). All
joined fields should be equal because the text is the same in each
table.

I tried to make Company Name be the Key for each table. However, the
computer would not let me, saying it messed up relationships and
created duplicates.

The whole problem with this thing is that within main table the
companies are repeated several times, once for each member/contact who
belongs to that company.

Running this query I get

Comp:CompanyID No MT.CompanyName Comp.CompanyName MT.CompID

1 Alpha Co Alpha Co BLANK
2 Beta Co Beta Co BLANK
2 Beta Co Beta Co BLANK
3 Cab Co Cab Co BLANK
4 Delta Co Dela Co BLANK

And so on. The repeated Companies.CompanyID is because there is more
than one contact/member for Beta Co on the main table.




so that it is matched against Companies on the CompanyName field, and
each MainTable record that has a match in Companies gets its [CompanyID
No] field set to the [CompanyID No] field of the matching Companies
record (that is, the one with the same CompanyName), then you should be
able to run an update query with SQL like this:

UPDATE MainTable INNER JOIN Companies
ON MainTable.CompanyName = Companies.CompanyName
SET MainTable.[CompanyID No] = Companies.[CompanyID No];

Sorry to be dense, but where do I write the above?

Many thanks for your help.

(comments and questions interspersed with quoted text)

"David F-B" wrote in message

On Wed, 23 Jun 2004 16:41:02 -0400, "Dirk Goldgar"
wrote:

"David F-B" wrote in message

For the last few weeks I have been trying to move out Company
address data from a monolithic single table database.

I want to have a list of unique companies and to link that back to
the records in the main table so that instead of cutting and
pasting the whole address data whenever a member moves, I can be in
a position to only need to change the Company ID number in the
Membership/Customer record.

I have had major surgery with less stress than trying to acomplish
what seems such a simple and obvious thing to do!

Right, enough grouching!

So far I have created a new table called Companies. I have appended
the data I wanted into it. The data is a list of company names from
the Main Table. Each company has a unqie ID number, courtesy of an
autonumber field, CompanyID No.

I have also created a field on the main table called CompanyID No.

I can do a query which lists

Company.CompanyID No
Company. CompanyName
MainTable.CompanyID No
MainTable.CompanyName

The Company ID lists 1 to 1500 ok, followed by the corresponding
Company Name company name. Then the same details from the Main
Table. I have tried umpteen times to copy the Company.CompanyID No
into the currently blank MainTable CompanyID No. Absolutely
nothing happens. It is so frustrating. I can see the columns all
ligning up. If I could just cut and past then the job would be
done.

Why can't I copy the data fom the same field in one table to the
same field in another table. If this is at all possible, please can
someone explain where I'm going wrong, before I go loopy.

Many thanks for all help given.

David

What's the SQL of this query of yours?

Having spent all my time working on a single table database I am not
up on complex queries. Finding stuff, deleting, appending and making
tables are as far as I've got. See the thread, 'Still Stuck' for
where I got to previously.

Basically I've got two tables and I've listed four records in the
query, two from each and both being identical field names: Company
Number and Company Name basically.

Four "records"? I think you mean four fields.

Because the Compant Table data is originally all from Main Table, both
tables share things like Company Name. As the two tables have the
same field I thought I should join them on the Company Name field. So
I dragged Company Name from Main Table list to Company Name Table
list. There is now a line joining them. Clicking on the line I get
Join Properties:-
Left Table Companies
Right Table Main Table
Left Column Field: CompanyName
Right Column Name: Company Name

Option 1 (Only Include rows where the joined fields are equal). All
joined fields should be equal because the text is the same in each
table.

If you're looking at the query in Design View, you can get the SQL of
the query -- the actual language that defines the query, rather than the
visual representation of it that Design View gives you -- by clicking
the down-arrow beside the View button (leftmost on the button bar) and
choosing SQL View from the list, or by clicking menu item View - SQL
View. When someone asks you for the SQL of a query, you can copy and
paste from that window into the message. To those who understand
Structured Query Language, that's more informative than a verbal
decription of the Design View.

I tried to make Company Name be the Key for each table. However, the
computer would not let me, saying it messed up relationships and
created duplicates.

You wouldn't want to do that, at least not for your main table, which we
already know has multiple records with the same company name.

The whole problem with this thing is that within main table the
companies are repeated several times, once for each member/contact who
belongs to that company.

Running this query I get

Comp:CompanyID No MT.CompanyName Comp.CompanyName MT.CompID

1 Alpha Co Alpha Co BLANK
2 Beta Co Beta Co BLANK
2 Beta Co Beta Co BLANK
3 Cab Co Cab Co BLANK
4 Delta Co Dela Co BLANK

And so on. The repeated Companies.CompanyID is because there is more
than one contact/member for Beta Co on the main table.

Right.

so that it is matched against Companies on the CompanyName field, and
each MainTable record that has a match in Companies gets its
[CompanyID No] field set to the [CompanyID No] field of the matching
Companies record (that is, the one with the same CompanyName), then
you should be able to run an update query with SQL like this:

UPDATE MainTable INNER JOIN Companies
ON MainTable.CompanyName = Companies.CompanyName
SET MainTable.[CompanyID No] = Companies.[CompanyID No];

Sorry to be dense, but where do I write the above?

First you'll have to modify it so that the table and field names match
the actual names of your tables and fields. You've written different
things at different times, so I'm not sure what the correct names are.
Once you've made those corrections, you would do the following:

1. Create a new query in design view.

2. Close the Show Table dialog without adding any tables.

3. Click the View Button, which will now be set to SQL View -- or click
View - SQL View.

4. Copy and paste the corrected SQL I gave you into the query window,
replacing the highlighted word "SELECT;".

5. Switch into Design View to see what it looks like that way (and make
sure there were no errors in the code). You can switch into Datasheet
View for an additional check, but it will just show you a bunch of
records with blank fields. You won't see the values they'll be updated
to, so this is really only good as a test to make sure the query can be
parsed.

6. Switch back into Design View and click the Run button (the big
exclamation point) to execute the query.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)