Richard,
Looks like you are trying to normalize your database, good idea.
1. The first step, for the question you are addressing is to create a new
table (tbl_Clients), which should contain a Client_ID (autonumber probably)
and the ClientName field. If table1 contains more client information, add
fields for those as well.
2. Next, create a query that selects all of the Client related fields from
table1 and then make this an aggregate query (GROUP BY all of these fields)
so that you only get one record for each combination of fields.
At this point, I would review this query and determine whether there are
multiple records with the same ClientName, but different values in the other
fields. If so, I would identify whether I need to have these "duplicate"
values. You might just have typo errors. If so, correct the typos in table
1 and re-run the query. You might also have multiple values because you have
fields in the query that contain different information for the same Client.
If this is the case, you probably need an additional table that would contain
this information (maybe something like ClientPhones). If this is the case, I
would drop those fields that are causing the duplicates so that you only end
up with one record for each ClientName.
3. Once you have this, you can change the query to an Append query and
append the information to tbl_Clients that you created during step 1.
4. Add a Client_ID field to your [table1]. Now create a query that joins
table1 to tbl_Clients with joins on all of the fields you just appended to
tbl_Clients(don't join on the Client_ID field). Change this query to an
Update query and update the Client_ID field in [tables] with the Client_ID
value from tbl_Clients. Your query will look something like:
Update table1
SET Client_ID = tbl_Clients.Client_ID
FROM table1 INNER JOIN tbl_Clients
ON table1.ClientName = tbl_Clients.ClientName,
table1.Field2 = tbl_Clients.Field2
Now you have a Client_ID value in tables, and you can delete all of the
redunant data (all of the fields you just imported into tbl_Clients) from
that table .
5. If you had duplicate records in the query you created in step 2 because
there were multiple records with the same ClientName, you should now be able
to create a make-table query for with these new records.
If this is not detailed enough, send more information about your table
structure (field names) and we can get into more detail.
HTH
Dale
HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.
richard said:
I have inherited a database with just one table[table1].
I need to split this table down into other tables.
One of these is to split the clients off, however every record in
[table1]has the client, so when I perform a select query I am returning all
records. I therefore wish to enter a criteria into the query to prevent
duplicate returns on the [ClientName] field.
Can anyone give me a simple criteria to do this
Thanks
Richard
