Record are being added double the total records, is there any mistake in SQL (1 Viewer)

[sbaud2003]

INSERT INTO CREDIT_MASTER ( LEAVE_TYPE, CREDIT_DT )
SELECT [FORMS]![LEAVE_CREDIT]![TEL] AS Expr1, [FORMS]![LEAVE_CREDIT]![TDT] AS Expr2
FROM CREDIT_MASTER;

 

[pbaldy]

You likely want the VALUES clause rather than the SELECT clause:

SQL INSERT: How To Insert One or More Rows Into A Table

This tutorial shows you how to use SQL INSERT statement to insert one or more rows into a table and guides you how to copy rows from other tables.

www.sqltutorial.org

 

[ebs17]

The contents in the SELECT part have no relation to the table in the FROM part. Therefore, they are duplicated in the table according to the number of records.
There are probably two records in the table.

... FROM CREDIT_MASTER T1Only

 

[Pat Hartman]

Why are you running an append query to append the contents of the current record? If your form is bound, Access will automatically save the current record. That would explain the duplicate data entry. Both YOU and ACCESS are saving the record. Get rid of your append query and allow Access to do what Access does.

 

[sbaud2003]

The contents in the SELECT part have no relation to the table in the FROM part. Therefore, they are duplicated in the table according to the number of records.
There are probably two records in the table.

... FROM CREDIT_MASTER T1Only

Yes, I want to append the records from the value in the form, if there are three records it is adding three times , why?please guide me

 

[ebs17]

SELECT [FORMS]![LEAVE_CREDIT]![TEL] AS Expr1, [FORMS]![LEAVE_CREDIT]![TDT] AS Expr2

Only with form references do you have exactly one record from the form, namely the one that is currently active. This construct acts like its own table. If you now insert any table in the FROM part, something like a CROSS JOIN takes place; every record in one table is combined with every record in the other table. Hence my recommendation to use a table T1Only. This is a prepared table that contains exactly one record (Boolean field with unique index and content True and validation rule that only True is allowed).

In the case of a CROSS JOIN with exactly one record, it remains with just one record.

Or you can use the VALUES variant of the append query as suggested. In any case, only one record is created with the transferred values.

 

[The_Doc_Man]

Yes, I want to append the records from the value in the form, if there are three records it is adding three times , why?please guide me

OK, I'll try to explain the design error that caused this. Look at the original SQL and let's decide what it REALLY says.

INSERT INTO CREDIT_MASTER ( LEAVE_TYPE, CREDIT_DT )
SELECT [FORMS]![LEAVE_CREDIT]![TEL] AS Expr1, [FORMS]![LEAVE_CREDIT]![TDT] AS Expr2
FROM CREDIT_MASTER;

Part one of that query (the INSERT clause) says to INSERT a new record into table CREDIT_MASTER containing two fields, named LEAVE_TYPE and CREDIT_DT. That part of the query is reasonable and probably correct.

Your problem is in the second part of the query that deals with the source of the information you are inserting. INSERT INTO takes EITHER of two ways to specify information to be inserted.

The way some folks suggested to you was a VALUES clause that lists the values to be inserted. That inserts ONE and ONLY ONE set of values at a time, and you can (actually, MUST) enumerate the individual values for each field in the order they were presented in the INSERT clause. So if you used a VALUES clause you would list the LEAVE_TYPE value first because it is first in the INSERT clause list of fields.

What you actually did was to choose to drive the INSERT with the OTHER legal form, a SELECT ... FROM clause. In this context, what you did was set up an implied loop that is equivalent to:

FOR EACH RECORD IN CREDIT_MASTER, INSERT (two values) INTO CREDIT MASTER.

You get that many records because the SELECT clause drives the INSERT action once for each record already in CREDIT_MASTER at the time you opened the query for execution. You get that many new records because your FROM clause was unconstrained - i.e. you had no WHERE clause to filter out records. A SELECT statement takes as many records are implied by the FROM and WHERE clauses and does whatever it is going to do for each remaining record. With no WHERE clause, ALL of the records remained.

The values are duplicated (replicated) because the field elements in your SELECT are from a source that has nothing to do with anything already in CREDIT_MASTER. They come from the external source frmLEAVE_CREDIT, and in that context are essentially constant since during the query nothing would modify their values.

And finally, this is not so much an error as just gilding a lily, putting an AS alias in the SELECT clause has no meaning and no effect because you already supplied the only names ever needed when you put them in the INSERT clause's field list in parentheses.

My advice to you is to NOT use the SELECT method to drive this but rather to use the VALUES clause.

 

[Pat Hartman]

Yes, I want to append the records from the value in the form, if there are three records it is adding three times , why?please guide me

Maybe, you should read #4 again. Just remove the append query from your code. Comment it out if you are afraid to delete it lest it be "lost". It looks to us, as though you don't understand how Access works and you are trying to save data that Access is already saving for you.

If you are trying to copy the current record and insert it into a different table that is a different problem with the query. Sometimes you want to copy something like a quote and then make some changes so the second quote is slightly different from the first quote. There are ways to do this but you will need to explain WHY you are trying to copy/duplicate the data so we can offer a working solution.

 

[The_Doc_Man]

Pat's question is ALSO valid. If the form is BOUND to the CREDIT_MASTER table, then you don't need an INSERT at any time. Access would save the values for you under very predictable circumstances.

If the form is unbound, then you need the INSERT - but it is also possible that you could bind the form to the table and not need a separate INSERT clause.

I reviewed your comments and they are somewhat ambiguous on the matter of bound vs. unbound (vs. bound to something else). Because of the confusion inherent in your SELECT clause, I don't know what you were actually doing. It looks like the form MIGHT be bound - but I don't know if you meant that as a data source or a misguided data destination because of your confusion.