7

I hope you can help me with this little problem. I am not quite sure of how to explain the situation to you, so I will just give it a try.

What I am trying to do is the following:

I want to insert some specific values and parameters (which I type in myself) into the table RFC_Risks, so basically every time I find a specific reason inside the table RCF_Risks, I want to write a new row that updates the priority of the RfC, every time that happens, the position shall be increased by 1.

My problem is now, that when I run this statement, I just get the SELECT part :-), not inserting is done, neither do I get a SQL statement error or anything like that. I just type in the parameters and then I get a SELECT Table thats all.

I'm using MS Access 2010 and I hope you can help me out with my "little" problem.

INSERT INTO RFC_Risks (RFC_No, RiskPos, Datum, Comments, RiskPrio, Reason)
SELECT RFC_Risks.RFC_No, (RFC_Risks.RiskPos +1) AS RiskPos, [Aktuelles Datum] AS Datum, [Kommentartext] AS Comments, [Neue Prio] AS RiskPrio, RFC_Risks.Reason 
FROM RFC_Risks INNER JOIN Risk_Reasons ON RFC_Risks.Reason = Risk_Reasons.Reasontext 
WHERE RFC_Risks.Reason = Risk_Reasons.Reasontext;
peterh
  • 11,875
  • 18
  • 85
  • 108
sXing
  • 71
  • 1
  • 1
  • 2
  • 1
    FYI, I don't think you need that `WHERE` clause at the end, your `INNER JOIN` does that for you =) – Josh Darnell Aug 12 '11 at 13:02
  • What error or unexpected results are you getting? This looks OK to me.. – Yuck Aug 12 '11 at 13:03
  • @Yuck it sounds like the unexpected result they're getting is that that no records are inserted into RFC_Risks (only the `SELECT` portion gets executed) – Josh Darnell Aug 12 '11 at 13:09

3 Answers3

3

I can't spot anything about your SQL statement which would prevent it from executing and/or throw an error. (I think your WHERE clause is redundant, but that should not cause the db engine to reject it.) What method are you using to "run" it?

If you're using the Access query designer, and switch from Design View to Datasheet View, your query isn't actually executed ... Datasheet View will show you the rows which would be affected if the query were executed.

The situation is the same as if you were building a delete query in the query designer ... Datasheet View would show you which rows would be deleted if the query were executed, but switching to Datasheet View does not delete those rows.

To execute the query, click the icon which has a red exclamation point.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Thank you very much for that information, tbh I didnt know that there was a difference in executing the query via datasheet or other method, I will try it out. – sXing Aug 18 '11 at 11:19
1

Try enclosing the subselect in parentheses.

taz
  • 1,506
  • 3
  • 15
  • 26
  • 1
    My answer probably would have been better as a comment, not an answer, but I can't comment on questions yet and the SQL looks pretty straightforward to me. As far as I know subselects must always be enclosed in parentheses, so I think that may work for you. Certainly reply if that is not the solution. – taz Aug 12 '11 at 13:03
  • So you mean I should put a parenthese in front of the select? INSERT INTO RFC_Risks ( RFC_No, RiskPos, Datum, Comments, Risk_Prio, Reason ) (SELECT RFC_Risks.RFC_No, (RFC_Risks.RiskPos +1) AS RiskPos, [Aktuelles Datum] AS Datum, [Kommentartext] AS Comments, [Neue Prio] AS Risk_Prio, RFC_Risks.Reason FROM Risk_Reasons INNER JOIN RFC_Risks ON Risk_Reasons.Reasontext = RFC_Risks.Reason); – sXing Aug 18 '11 at 11:23
0

You need to execute your query. If you only display it in Datasheet View, the SELECT Statement is executed and displayed to you as a kind of preview.

To really execute the query, when you are in design mode, you can click on the "Run" button (a red exclamation mark) in the toolbar. You can also double-click on the query in the database window.

Mathieu Pagé
  • 10,764
  • 13
  • 48
  • 71
  • thank you I tried that, and now after I entered the parameters, I get an error, where access keeps telling me that there is a key conflict. Im not entirely sure why this happens, the Table RFC_Risks has 2 primary keys, the RFC number and the RiskPosition because one rfc can have more then just one risk. Any ideas? – sXing Aug 18 '11 at 11:30
  • @sXing, You are probably trying to insert a new record with the sames values for the primary key as a record that is already in the table (You might also be adding two, or more, records with the sames values for their primary key). – Mathieu Pagé Aug 18 '11 at 13:15