Hi Doug
If all you have is an Access table in the form you describe, then this will
be impossible to do reliably. The reason is that there is no inherent order
or records in a table - you can think of it as a "bucket of records". Just
like a bucket of fish (an analogy that it seems might be familiar to you
<g>) there is no guarantee that the records will come back out in the order
you put them in.
So, there is no guarantee that, when reading consecutive records from this
table, you won't get them in the following order:
Author Songs Locations
null Love 62-06
Nat 'King' Cole null null
null Ramblin' Rose 127-06
null Old Ruged cross 76-06
johnny cash Null Null
null This Is All I Ask 41-09
null Burning fire 45-05
To ensure a consistent order, you must have a field on which you can sort.
It seems that you have imported this data from somewhere else - an Excel
spreadsheet or a text file. Am I right?
If you still have access to the original data then you should either add
sequential record numbers like this:
Seq Author Songs
Locations
1 Nat 'King' Cole null
null
2 null Love
62-06
3 null Ramblin' Rose
127-06
4 null This Is All I Ask 41-09
5 johnny cash Null
Null
6 null Burning fire
45-05
7 null Old Ruged cross 76-06
....or, (better!) ensure that the author name appears in every row:
Author Songs Locations
Nat 'King' Cole null null
Nat 'King' Cole Love 62-06
Nat 'King' Cole Ramblin' Rose 127-06
Nat 'King' Cole This Is All I Ask 41-09
johnny cash Null Null
johnny cash Burning fire 45-05
johnny cash Old Ruged cross 76-06
With the first method, you can read the table sequentially *ordered by your
sequence number*. When you read a record with a non-null author, you create
a new record in the "authors" table and "remember" the primary key
(AuthorID) of the record you have created. When you read a record with a
null author, you create a record in the "songs" table and fill the foreign
key field with the current AuthorID. This method requires code.
If you have the author name in every record then you can do the job with two
simple append queries. One to add the authors:
Insert into Authors (AuthorName) select distinct Author from SourceTable;
.... and one to add the songs:
Insert into Songs (AuthorID, SongName, Location)
select AuthorID, Songs, Locations from SourceTable inner join Authors
on (Sourcetable.Author = Authors.AuthorName);
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
chselbit said:
I need to query a table with three columns, Authors, Songs and location;
In the Author Field, it has many Author names and with 1.. to many empty
data
or nulls in the authors field [authors] too.
Need to know how to creat two relational tables, one authors and one songs
with my data. Can some one show me how ?
Email me at (e-mail address removed)
Thank U...
example of table;
Author Songs
Locations
Nat 'King' Cole null null
null Love
62-06
null Ramblin' Rose 127-06
null This Is All I Ask 41-09
johnny cash Null
Null
null Burning fire
45-05
null Old Ruged cross 76-06
Appreciate any help or Suggestion to place the authors in the null field.
Thank U...
Doug
