Query outputting all fields

Topic

New Reply

This doesn’t happen in all my databases but – in at least two

when I select only some of the columns the datasheet view displays all columns.

I’ve unchecked “output all fields’ in Access Options > Object Designers, closed all instances of Access and restarted, but still the pain persists.

I can’t run an append because I get an error

The INSERT INTO Statement contains Unknown field ‘300809’. Make sure you have typed the name correctly, and try the operation again.

when I try to execute using ‘Run’. The unkown field is a field in the FROM table but it’s not in the SQL

Code:

INSERT INTO LandUse ( Supplier_No, Supplier_Name )
SELECT LanduseImport.[Supplier No], LanduseImport.[Supplier Name], *
FROM LanduseImport;

what am I missing?

Reply | Quote

Replies

Found it – there’s an Output all fields property for the query. Where did that come from!
New in 2007. Defaulted to the value of the Object designer setting but didn’t change when I changed the setting.

Reply | Quote

Found it – there’s an Output all fields property for the query. Where did that come from!
New in 2007. Defaulted to the value of the Object designer setting but didn’t change when I changed the setting.

I just looked back in 2003, and found that the same property and behaviour is there too.
I can’t imagine when you would want this.

Reply | Quote

No idea why anyone would ever want this.

What would be good is a button to add all fields so that you could then delete just the ones you don’t need.

That would be useful when you are creating append queries with lots of fields.

But to put out all fields regardless of the SQL seems a bit bizare!

Reply | Quote

Kentg,

This comes from using the asterisk in the SELECT statement. The * in a SELECT statement indicates to “OutPut All Fields” and vice-versa, if you then also include the field names as well, you will get duplicate field names. Access will give the duplicate column/field a name of its choosing: For example, in your statement “SELECT LanduseImport.[Supplier No], LanduseImport.[Supplier Name], * FROM LanduseImport” both [Supplier no] and [Supplier Name] would be duplicated in the output. Remove the * and you should be fine.

You said…”What would be good is a button to add all fields so that you could then delete just the ones you don’t need.”

Tip: If you use the QBE Grid/designer, you can double click on the Titlebar of the Field list. That will select all of the fields in the field list and then you can easily drag them all down into the grid and then delete the ones you don’t want.

Hope that helps.

Bob Oxford

Reply | Quote

This comes from using the asterisk in the SELECT statement. The * in a SELECT statement indicates to “OutPut All Fields”

I think Kentg’s observation is that the “Output all Fields” property is in the Properties box but not in Query Design. But it is true that if Output All Fields is set to true you do get an * added to the SQL. What I find is that if you leave out the table name in front of the * in the SQL Output All Fields is set to yes.
So SELECT * FROM tblNurseInfo does it but SELECT tblNurseInfo.* FROM tblNurseInfo does not.

The advantage of * is that it automatically adds any new fields to the query. I would like something that works like that, but means “Return all fields except….” so I could exclude some fields, but retain the capacity to have new field added automatically.

Reply | Quote

Tip: If you use the QBE Grid/designer, you can double click on the Titlebar of the Field list. That will select all of the fields in the field list and then you can easily drag them all down into the grid and then delete the ones you don’t want.
Bob Oxford

Here I’ve been a developer since version 1.0 and I just learned something new. Thank you.

Also found that you can select the fields in the usual way (using shift and control keys) to select a range or non-consecutive fields and drag them down. Control/A works too! Cool!

Reply | Quote