Hi,
I have two tables with a one to many relationship joined
via Serial_Number.
Table one (Carillion Data) holds my main data, table two
is (Resite Table) and holds information on moves.
Table one has Serial_Number as the Primary Key
Table two has Job Number as the Primary Key
I then have Resite Qry that gives me information to feed
my update Qry
SELECT [Resite Tbl].Job_Number, [Carillion
Data].Serial_Number, [Carillion Data].Make, [Carillion
Data].Model, [Resite Tbl].Old_Address, [Resite
Tbl].Old_CC, [Resite Tbl].New_Address, [Resite
Tbl].New_CC, [Resite Tbl].Completion_Date, [Resite
Tbl].Invoice_Number, [Resite Tbl].Tariff, [Resite
Tbl].Resite_Charge, [Resite Tbl].Resite_Comments, [Resite
Tbl].Resite_Owner
FROM [Carillion Data] INNER JOIN [Resite Tbl] ON
[Carillion Data].Serial_Number = [Resite
Tbl].Serial_Number
ORDER BY [Carillion Data].Serial_Number;
My Update Query to update Job Number in to the same field
within Carillion Data is
UPDATE [Carillion Data] INNER JOIN [Resite Qry] ON
[Carillion Data].Serial_Number = [Resite
Qry].Serial_Number SET [Carillion Data].Job_Number =
[Resite Qry].[Job_Number]
As mentioned below this works fine, however I would like
the Job Number feild within Carillion Data to hold more
than one job number. Is this possible?
Much Thanks
-----Original Message-----
Hi
I have a situation where I am trying to update a record
with a job number [Field]. The records are unique and
the Primary Key is the Serial_Number. However each
Serial_Number could have more than one Unique Job
Number.
You can't have it both ways. The Job Number is either "unique" - only
one exists - or it's not. If a Serial_Number can have more than one
job number then the job number IS NOT UNIQUE.
My problem is that when a record has a new job number it
is overwritting the previous job number with the new
one.
Please post the SQL of your query.
How is it possible to show all job numbers for that
record. I have a tbl that stores all the relevant
information for each job number.
And please describe your tables and their relationships. It *sounds*
like you might have a many to many relationship between serial numbers
and job numbers, requiring a third table.
.
