How to control which records are updated

[irsmalik]

[Z{Mod Edit: Removed duplicated information between merged posts}]


Hello friends

I have a form with Update buttons. When it is pressed, a query02 runs.

Source table: TEMPAY
Source field: QNO

Destination Table: PAYMENT125
Destination field: INSTNO

Query should check before Add record INSTNO <= QNO
if it is not <= query should not run

image of query is attached
[imgnothumb]http://bytes.com/attachments/attachment/6902d1360994066/append-query.jpg[/imgnothumb]

Here is the SQL for friends to correct it.

INSERT INTO [PAYMENT 125] 
   ( CODE125, INSTNO, [DATE], AMOUNT )
SELECT TEMPAY.CODE125, TEMPAY.QNO, 
   TEMPAY.QDATE, TEMPAY.QAMOUNT
FROM TEMPAY 
   INNER JOIN [PAYMENT 125] 
   ON 
      TEMPAY.CODE125 = [PAYMENT 125].CODE125
GROUP BY TEMPAY.CODE125, TEMPAY.QNO, 
   TEMPAY.QDATE, TEMPAY.QAMOUNT
HAVING (((TEMPAY.CODE125) Is Not Null)
    AND ((TEMPAY.QNO) Is Not Null) 
   AND ((TEMPAY.QDATE) Is Not Null) 
   AND ((TEMPAY.QAMOUNT) Is Not Null));

My database is also attached in zip format. QUERY02 needs to be correction.

[NeoPa]

This is a much better attempt at asking a question. Nevertheless, a picture of your query is no help. Mainly because it's too small even to see what's in it, but also because a picture of a query tells us very little. Please copy and paste the SQL of your query, which you can see if you view the query in SQL View. I suspect we can do something with that.

[zmbd]

irsmalik:

If you will take a careful look at your question... you have the answer there... add INSTNO <= QNO to your conditions.

Some things to point out:
- First, you should know that most of us will not open an attachment that is not requested.
It is always better to try and provide the information within the text of the post.

- Second, after some 20 posts someone should have pointed out the following:
-- Bytes is not a code nor homework service. Please read the FAQ and posting guidelines before re-posting your question.
>> Before Posting (VBA or SQL) Code.
>> How to ask "good" questions -- READ BEFORE SUBMITTING A QUESTION!.
>> POSTING_GUIDELI NES: Please Read Carefully Before Posting to a Forum.

[zmbd]

Good grief... I just noted
You have a field named "Date"
That is a reserved/key-word. Using reserved/key-words as a field name is very bad idea as it can and will cause you issues with VBA, Macro, and SQL. You really should avoid: RESERVED KEYWORDS BY LANGUAGE

[zmbd]

irsmalik:
I didn't catch that you had double posted your question.
Please do not do that in the future.

In this case, I've merged the two threads and the duplicated posts because the second post thread appeared to be an honest attempt to comply with Neopa's request for the SQL (post #2) and I like to give the benefit-of-the-doubt to the poster when possible.

In future, if you are requested to provide code/sql/script to clarify a question, please do so within the original thread.

Please read the links I provided at the bottom of post #3. You will be expected to follow the guidelines contained therein for all future posts.

[NeoPa]

It's a little clearer with the SQL, but you also need to explain what it is you want (much better than you did in the first post) and what problem you are actually getting.

Normally we can get some clues from the SQL, but this seems to be a mix of a number of ideas, none of which is done properly. Trying to guess what you want is therefore particularly difficult.