Update Query *2

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to create an update query that will update 2 tables at the same time.

Table 1 = [LAST NAME] [FIRST NAME], [SEQUENCE] + other detailes fields
Table 2 = [LAST NAME] [FIRST NAME], [COUNT]

[SEQUENCE] from table 1 is empty. I want a query than will look up table 2
using [LAST NAME] [FIRST NAME] from table 1 and update [SEQUENCE] FROM [TABLE
2.COUNT] then add 1 to [TABLE 2.COUNT] and update that table as well.

How can I do this?
 
I want to create an update query that will update 2 tables at the same time.

Table 1 = [LAST NAME] [FIRST NAME], [SEQUENCE] + other detailes fields
Table 2 = [LAST NAME] [FIRST NAME], [COUNT]

[SEQUENCE] from table 1 is empty. I want a query than will look up table 2
using [LAST NAME] [FIRST NAME] from table 1 and update [SEQUENCE] FROM [TABLE
2.COUNT] then add 1 to [TABLE 2.COUNT] and update that table as well.

How can I do this?
Click to expand...

You can't. You'll need two update queries, one for each table.

Your table design has some real problems. I know three people named
Fred Brown, all in this little town. Can you be ABSOLUTELY certain
that you will never have to deal with two different people who happen
to have the same name?

A better design would be to have a People table with a unique
PersonID, with the person's name in (nonunique) fields within the
table.

Secondly, storing derived data such as your Count and Sequence is
almost surely bad design too. Storing data in one table which depends
on a value in another table is deeply flawed - you have no guarantee
that the value of COUNT might not be changed independently of your
query, throwing your value of SEQUENCE into error.

Could you step back and explain what these tables represent, how they
are related, what each table has as a Primary Key, and - most
important - what you're trying to accomplish? I'm quite certain
there's a better way!

John W. Vinson[MVP]
 
The background is we have been given a flat file of people details and which
doesn't have a unique identifier, hence we need one. Whatever it becomes will
be what the user will have to log in using, hence rather than just using a
number we want to use a combination of last name and first name, but we know
that there will be duplicates hence the need to add a sequence number.

I would like to use one update query to do it, i.e. [ContactID] =
Left([contact.Last Name],5) & Left([contact.First Name],3) & ???? where ????
is a sequence number that starts at 01 for each unique lastname firstname
combo, hence I would get

DENNIPAU01
DENNIPAU02
MURPHDE01
MURPHDE02

This is why I was thinking that if I used 2 tables then I could save the
sequence number per lastname/firstname combo.

John Vinson said:
I want to create an update query that will update 2 tables at the same time.

Table 1 = [LAST NAME] [FIRST NAME], [SEQUENCE] + other detailes fields
Table 2 = [LAST NAME] [FIRST NAME], [COUNT]

[SEQUENCE] from table 1 is empty. I want a query than will look up table 2
using [LAST NAME] [FIRST NAME] from table 1 and update [SEQUENCE] FROM [TABLE
2.COUNT] then add 1 to [TABLE 2.COUNT] and update that table as well.

How can I do this?
Click to expand...

You can't. You'll need two update queries, one for each table.

Your table design has some real problems. I know three people named
Fred Brown, all in this little town. Can you be ABSOLUTELY certain
that you will never have to deal with two different people who happen
to have the same name?

A better design would be to have a People table with a unique
PersonID, with the person's name in (nonunique) fields within the
table.

Secondly, storing derived data such as your Count and Sequence is
almost surely bad design too. Storing data in one table which depends
on a value in another table is deeply flawed - you have no guarantee
that the value of COUNT might not be changed independently of your
query, throwing your value of SEQUENCE into error.

Could you step back and explain what these tables represent, how they
are related, what each table has as a Primary Key, and - most
important - what you're trying to accomplish? I'm quite certain
there's a better way!

John W. Vinson[MVP]
Click to expand...
 
Back
Top