Duplicate output destination error

[kirkm]

Hi, I'm getting this error after changing a Select query to an Append query and can't see why.

The select one works fine:-

SELECT tblMain5.*
FROM tblMain5
WHERE (((tblMain5.Year)='1965') AND ((tblMain5.Sheet)='Min') AND ((tblMain5.OBE)=1))
ORDER BY Val([High]), Val([#]) DESC , Val([10]) DESC , Val([40]) DESC , Val([CH]) DESC;

But fails after converting to:-

INSERT INTO ThisYear ( [Year], Sheet, OBE )
SELECT tblMain5.*, tblMain5.Year, tblMain5.Sheet, tblMain5.OBE
FROM tblMain5
WHERE (((tblMain5.Year)='1965') AND ((tblMain5.Sheet)='Min') AND ((tblMain5.OBE)=1))
ORDER BY Val([High]), Val([#]) DESC , Val([10]) DESC , Val([40]) DESC , Val([CH]) DESC;

The error is Duplicate output destination 'Year'.

Any help fixing it appreciated, thanks.

 

[MarkK]

Your insert query . . .

INSERT INTO ThisYear ( [Year], Sheet, OBE )

. . . only seeks data for three fields, but your SELECT clause . . .

SELECT tblMain5.*, tblMain5.Year, tblMain5.Sheet, tblMain5.OBE

. . . provides many more.

 

[kirkm]

Can the query be changed so it'll work? Can oyu show me? I've tried a few things but always some kind of error.
If not I'll take another approach altogether.

 

[Grumm]

Have you tried to remove the "tblMain5.*," ?
I don't know how many fields there are in your "tblMain5" table.
If you try this :

SELECT tblMain5.*, tblMain5.Year, tblMain5.Sheet, tblMain5.OBE
FROM tblMain5

You will have the fields 'Year','Sheet' and 'OBE' twice in the output.

 

[kirkm]

I do want to append all the fields in tblMain5, but only 3 of them are conditional.
There are 106 fields in the table.

Did try (amongst other things) INSERT INTO ThisYear (tblMain5.*) but that gave an error.

Can what I'm attempting be done in a query? If not, I'll try some VBA with .AddNew.

 

[MarkK]

Read the things people are posting a little closer. I think you have been handed the solution twice now.

 

[kirkm]

If they're solution they are not proving to be so here !

Thanks , I guess... will do it in VBA.

 

[MarkK]

Read what Grumm writes here . . .

Have you tried to remove the "tblMain5.*,"

. . . which I read as an explicit instruction you might follow.

 

[plog]

I can't stay silent any more. You've got 3 big red flags that make me think your tables are improperly structured:

Tables named tblMain5 and ThisYear. Sounds like you are storing values that should be in your tables as their names. For example, you wouldn't have a 2015Table, 2014Table, 2013Table. Instead you would have a field in your table which designates what year each row is for.

106 fields - That's a lot for one table. What are some of these field names? My gut is telling me you are making the same error at the field level that you are at the table level--storing values that should be in your table as names of columns (MarchSales, AprilSales, MaySales).

INSERT INTO - Action queries are often used to overcome poor structures and by those who don't understand how queries actually work. Why must you move this data into a new table? Why not just SELECT it?

Also, 'Year' is a reserved word and shouldn't be used as the name of a field--it makes writing code and queries that much more difficult.

So, I think this issue is a symptom of a bigger problem-- improperly structured tables. Why do you need to run an INSERT query? Why do you have 106 fields in a table? Can you post a screenshot of your relationship view or list your field names in that huge table?

 

[kirkm]

MarkK, yes I sure did but it resulted in "You are about to append 0 row(s)."
There are actually 222 rows to append.I believe an append query would be a better method, but I now have it working with this function.

Sub AppendIt(rx As DAO.Recordset)

Dim db As DAO.Database
Dim AppendTo As DAO.Recordset, i As Integer
Set db = CurrentDb
Set AppendTo = db.OpenRecordset("ThisYear")

rx.MoveFirst
Do
    AppendTo.AddNew
    For i = 0 To rx.Fields.Count - 1
        AppendTo(i) = rx(i)
    Next
    AppendTo.Update
    rx.MoveNext
Loop Until rx.EOF

Set AppendTo = Nothing
Set db = Nothing

End Sub

Hey, plog I can't really answer those questions! Too complicated for a note in a forum and while all of your comments may be perfectly valid, I'm designing this and several years work has gone into the 'structure'. LOL whether it be wrong or not, it's what I have. And maybe just to drive you even crazier one requirement is a sorted table which I know is not the Access way.Because it needs to sort in a way that cannot be done by a query and this routine is to confirm it is correct. It's applied to a copy of the main table, then the two are compared.Sorry about the ramble...