Why does what appears to be a simple append query is pasting 5 duplicate rows? 2

[TheresAlwaysAWay]

I have what is to the eye a simple append query. I'm posting a picture of the query structure with the message, and a picture of the table content.

The SQL for the query is::

INSERT INTO CVInsQuotes ( CVID, Company, Quote )

SELECT [Forms]![CommercialVehicles]![CVID] AS CVID, 'National General' AS Company, [Forms]![CommercialVehicles]![NatGen] AS Quote

FROM CVReferrals;

I don't see why it's trying to create 5 duplicate rows. Hoping someone can assist.

 

[hermanlaksko]

How many records do you have in the tbl: CVReferrals?

 

[Drainie]

How many records are there in the table CVReferrals? It would look as if you are creating one instance for each. Perhaps you need a 'WHERE' clause to limit the list generated.

 

[Chriss Miller]

Why are you even selecting from CVReferrals?

It seems to me you want to insert one record, that's done with a simple INSERT ... VALUES:

INSERT INTO CVInsQuotes ( CVID, Company, Quote ) VALUES ([Forms]![CommercialVehicles]![CVID], 'National General', [Forms]![CommercialVehicles]![NatGen])

When you select from another table you always get as many records as are in there, no matter if you sepcify or don't specify fields from that table or not. And the only way to reduce the number of records appended would be by a WHERE clause, as Drainie said.

It doesn't make sense to query from CVReferrals when you don't set any target column of CVInsQuotes to something coming a column of CVReferrals, though, does it? So even if you could add a where clause that filters only 1 record of CVReferrals, that would work but would still be pointless as you'd say "take this record, but instead of taking any values from it take these values." Well, there#s the simplest of INSERT-SQL queries fro that, the one specifiying your set of values. They seem to come from a form, not from another table, don't they? Or am I misunderstanding your intent?

 

[TheresAlwaysAWay]

How many records are there in the table CVReferrals? It would look as if you are creating one instance for each. Perhaps you need a 'WHERE' clause to limit the list generated.

None. It is a brand new table but I'm just creating for the first time

 

[TheresAlwaysAWay]

Why are you even selecting from CVReferrals?

It seems to me you want to insert one record, that's done with a simple INSERT ... VALUES:

INSERT INTO CVInsQuotes ( CVID, Company, Quote ) VALUES ([Forms]![CommercialVehicles]![CVID], 'National General', [Forms]![CommercialVehicles]![NatGen])

When you select from another table you always get as many records as are in there, no matter if you sepcify or don't specify fields from that table or not. And the only way to reduce the number of records appended would be by a WHERE clause, as Drainie said.

It doesn't make sense to query from CVReferrals when you don't set any target column of CVInsQuotes to something coming a column of CVReferrals, though, does it? So even if you could add a where clause that filters only 1 record of CVReferrals, that would work but would still be pointless as you'd say "take this record, but instead of taking any values from it take these values." Well, there#s the simplest of INSERT-SQL queries fro that, the one specifiying your set of values. They seem to come from a form, not from another table, don't they? Or am I misunderstanding your intent?

Thanks. No I'm thinking about it, you're absolutely right. There's no reason to draw the record from a table. It's only the record that's on a specific form

That may be the key gift and I will make that change.

But just so you know, there are no records in the system yet. This is a new development and I'm just troubleshooting it.

 

[TheresAlwaysAWay]

To complete the thought, the record that is on the screen of a form at that moment is stored in the CVReferrals table, but the value that I am inserting is simply taking what is on the screen in that form and inserting it to the table.

That is very likely where the problem is and I'm gonna check it out as soon as I get a chance.

 

[Chriss Miller]

But just so you know, there are no records in the system yet.

Well, if there are no records also in CVReferrals your original query wouldn't tell it will add 5 records. The 5 actually tells me (us) that CVReferrals has had 5 records at the time you did this. The way you did this query without any WHERE clause the number of records of CVReferrals plainly is the number of records you select and thus also eventually insert into CVInsQuotes.

Besides this, as you say the data comes just from a form, depending on more information we'd need to have, it would be a better idea to let the form bind its fields to the table columns instead of using an insert query, i.e. the way forms and data binding to tables works should be used. If the form fields you're referring to are getting their values from yet other tables (or are input by the user when those are emtpy still) then you'll indirectly copy over data from other tables into CVInsQuotes. Whether that makes sense depends on your data model and structuring of tables. We could discuss this, but would need to go into much more nitty gritty details of all of this.

So from all I see I'd say you simply want to insert some form fields values into CVInsQuotes and then you do that with either data binding or the simple INSERT...VALUES.

 

[TheresAlwaysAWay]

Why are you even selecting from CVReferrals?

It seems to me you want to insert one record, that's done with a simple INSERT ... VALUES:

INSERT INTO CVInsQuotes ( CVID, Company, Quote ) VALUES ([Forms]![CommercialVehicles]![CVID], 'National General', [Forms]![CommercialVehicles]![NatGen])

When you select from another table you always get as many records as are in there, no matter if you sepcify or don't specify fields from that table or not. And the only way to reduce the number of records appended would be by a WHERE clause, as Drainie said.

It doesn't make sense to query from CVReferrals when you don't set any target column of CVInsQuotes to something coming a column of CVReferrals, though, does it? So even if you could add a where clause that filters only 1 record of CVReferrals, that would work but would still be pointless as you'd say "take this record, but instead of taking any values from it take these values." Well, there#s the simplest of INSERT-SQL queries fro that, the one specifiying your set of values. They seem to come from a form, not from another table, don't they? Or am I misunderstanding your intent?

This solved my problem. Thank you so much!

 

[TheresAlwaysAWay]

Well, if there are no records also in CVReferrals your original query wouldn't tell it will add 5 records. The 5 actually tells me (us) that CVReferrals has had 5 records at the time you did this. The way you did this query without any WHERE clause the number of records of CVReferrals plainly is the number of records you select and thus also eventually insert into CVInsQuotes.

Besides this, as you say the data comes just from a form, depending on more information we'd need to have, it would be a better idea to let the form bind its fields to the table columns instead of using an insert query, i.e. the way forms and data binding to tables works should be used. If the form fields you're referring to are getting their values from yet other tables (or are input by the user when those are emtpy still) then you'll indirectly copy over data from other tables into CVInsQuotes. Whether that makes sense depends on your data model and structuring of tables. We could discuss this, but would need to go into much more nitty gritty details of all of this.

So from all I see I'd say you simply want to insert some form fields values into CVInsQuotes and then you do that with either data binding or the simple INSERT...VALUES.

You are absolutely correct. There are 5 records. It's a new development, but there are 5 in there!