UPDATE Query with ReplicationID's 1

[jsaliers]

I am not good with SQL, so I am pretty well stuck. This is what I have:

tblDrawings:
drawingID - serves as unique identifier (Replication ID)
partNumber - actual part number
misc fields that aren't important

relDrawingComponents:
partNumber
componentPartNumber
componentCount

I added the drawingID to tblDrawings after the table was populated. However, I had also created parent-child relationships between parts from tblDrawings in relDrawingComponents. That is, a part in tblDrawings will have components also listed as parts in tblDrawings. I have both tables populated already with thousands of records, so I cannot really easily repopulate these tables. What I need is to change the fields in relDrawingComponents. I need partNumber to have that part number's associated drawingID as listed in tblDrawings. In addition, I need componentPartNumber to have that part number's associated drawingID as listed in tblDrawings. I don't really need partNumber or componentPartNumber, just the correct drawingID's for each. This is ideally the table I am looking for:

relDrawingComponents:
drawingID
componentDrawingID
componentCount

Any idea how to write an UPDATE query that would provide me the values required in relDrawingComponents?

Any help is much appreciated! Thanks in advance!!!

Jon

 

[talon121a]

Hey Buddy, (Its Jason)

Can you give me data types for each field? (in both tables)

ie. varchar(45)

Let me know and I'll get ya some SQL Its one of my favorite things to do ;-)

I'm such a nerd ;p

haha

Jason

 

[jsaliers]

Well... here goes...

tblDrawings
drawingID (AutoNumber, length=RepID)
partNumber (text)

relDrawingComponents
partNumber (text)
componentPartNumber (text)
componentCount (Number)

The database is just a regular Access 2000 database.

Any ideas?

Jon

 

[talon121a]

Sure thing, personally would write a stored procedure to populate existing records, and for all new records just make it automatically grab replication id information and populate existing fields.


---
Jon, contact me MSN (you have it) when ya can if ya have a sample I can help ya out ;-)
---