Query - Write Conflict

[Discipher]

Hello,

I am trying to upgrade my current system to sql/adp.

I cant seem to get two table to work together in a query.

In Table A is where when a sample comes in it gets entered into the system. Sample Number And Date Arrived are two columns. Table B has more columns depending if the sample was processed or not.

I created the relationship tying the two tables together and when Table A is open the + symbol shows up next to the keys column and if i click on it i can see the columns in the otehr table where i can enter data into them. If i do the info shows up in the other table with the sample number (key) shows up.

If i create a query which will show the samplenumber column from Table A and another column from Table B. When i enter data into any of Table B's columns i get the Write Conflict error and nothing occurs on the Table B side.

Is there a way around this? Access MDB just automatically enters in the number into Table B and doesnt have any conflicts in doing so. I havent much experience in SQL but cant find any concrete solution to this

THanks

 

[cmmrfrds]

In ANSI Standard SQL you can only update 1 table in an update statement. Access allows updates to multiple table in an Update statement.
Do you have an identity column in the sql server table, if so do not provide a value for that column.
If you paste in your sql statement it would be easier to see what you are trying to do.

 

[Discipher]

Basically, i have a SampleNumber in table1 that has most of the data i need. In table two, i would like to list all of the sample numbers from Table1 and be able to leave a text field comment on any sample number within any of those sample numbers on a randomly selected basis. In access MDB i would create a query with the sample number column, and the text column from table2. when text is entered into table2 it would automatically enter into the samplenumber column the value from table1.

below is a code from the query. whenever i enter into the text field it brings up the Write Conflict error.



SELECT dbo.Table2.SampleText2, dbo.Table1.SampleNumber
FROM dbo.Table1 LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.SampleNumber = dbo.Table2.SampleNumber

 

[cmmrfrds]

So, what do you plan on putting into table2 when there is no corresponding record in table1 as indicated by the left outer join?

 

[Discipher]

Table1 would have say records called Sample001, Sample002 and so on. on table two i would like to have a query that showed all the records from table1 and a text box that had just a comments sections where a quality control comment could be left. this would be about 1 out of every 100 records in table1.

im not sure if i should be doing this a different way. I have a mdb database that runs quite well but we need to upgrade to a sql system as we are moving into multiple locations.

 

[cmmrfrds]

Change this to an inner join.

SELECT dbo.Table2.SampleText2, dbo.Table1.SampleNumber
FROM dbo.Table1 INNER JOIN
dbo.Table2 ON dbo.Table1.SampleNumber = dbo.Table2.SampleNum