Extra columns on export query

[WineSnob]

I have a query with a bunch of columns. In data sheet view it looks good. When I export the query to excel I get 6 extra columns that are not part of the query but are part of the table. Why am I getting the extra columns on the export?

 

[theDBguy]

You shouldn't. Can you post a sample db to show the problem?

 

[CJ_London]

My guess is there is a * in there somewhere

 

[WineSnob]

I’ll try. The one I am using has linked tables.
I’ll try to create a “fake” db. Thanks.

 

[WineSnob]

Here it is. The query does NOT have the last 6 columns in it, but when I export to excel there are 6 extra columns on the excel file.
I hope I did the attachment right. Thanks.
Also the drop and drag to move the columns in design view do not "stick" on the results page. Why is that?
I have been away from Access for a few years (retired) and now things are all different.
BTW i am using the Online Office365 latest version. i just updated today.
Thanks for any assistance.

 

[WineSnob]

I figured out what the issue is. Access is adding ,* to the SQL statements. It does it every time I create a new query, I dont know WHY it is happening but now I can fix it and move on but i will need to check all queries going forward.
SELECT Master.Timestamp AS [Date/Time Entered], Master.USERNAME AS [Entered by], Master.[Date Worked], Master.Employee, Master.Vacation, Master.VacationHours, Master.[Job location] AS [Job Name], Master.Role, Master.[Start time], Master.[Stop Time], Master.[Total time], Master.[Personal Hours] AS [Personal Time], Master.PayType, Master.[Prevailing Wages], Master.[Per Diem], Master.Comments1 AS Comments, Master.[Reimbursable Expenses] AS Expenses, Master.Mileage, Master.[Paycode Total Qty], Master.[Piece Rate Pay Total], Master.[Paycode Type 1], Master.[Paycode Type 1 QTY], Master.[Paycode Type 2], Master.[Paycode Type 2 QTY], Master.[Paycode Type 3], Master.[Paycode Type 3 QTY], Master.[Paycode Type 4], Master.[Paycode Type 4 QTY], Master.[Paycode Type 5], Master.[Paycode Type 5 QTY], Master.Status, Master.ApprovedTime, Master.[Last Change] AS [Last Change Time], Master.[Changed By], *
FROM Master
ORDER BY Master.Employee;

 

[mike60smart]

I figured out what the issue is. Access is adding ,* to the SQL statements. It does it every time I create a new query, I dont know WHY it is happening but now I can fix it and move on but i will need to check all queries going forward.
SELECT Master.Timestamp AS [Date/Time Entered], Master.USERNAME AS [Entered by], Master.[Date Worked], Master.Employee, Master.Vacation, Master.VacationHours, Master.[Job location] AS [Job Name], Master.Role, Master.[Start time], Master.[Stop Time], Master.[Total time], Master.[Personal Hours] AS [Personal Time], Master.PayType, Master.[Prevailing Wages], Master.[Per Diem], Master.Comments1 AS Comments, Master.[Reimbursable Expenses] AS Expenses, Master.Mileage, Master.[Paycode Total Qty], Master.[Piece Rate Pay Total], Master.[Paycode Type 1], Master.[Paycode Type 1 QTY], Master.[Paycode Type 2], Master.[Paycode Type 2 QTY], Master.[Paycode Type 3], Master.[Paycode Type 3 QTY], Master.[Paycode Type 4], Master.[Paycode Type 4 QTY], Master.[Paycode Type 5], Master.[Paycode Type 5 QTY], Master.Status, Master.ApprovedTime, Master.[Last Change] AS [Last Change Time], Master.[Changed By], *
FROM Master
ORDER BY Master.Employee;

Hi Rick

Your 1 table approach is not how Access works.

You have Employees who work at various locations.

You therefore need to normalise your Master table.

 

[WineSnob]

Mike60,
I understand. I have 5 tables but I created a sample db with minimal data to show what was happening. This is NOT the actual DB.
I was able to edit the SQL statement to make it work properly.

 

[GPGeorge]

Mike60,
I understand. I have 5 tables but I created a sample db with minimal data to show what was happening. This is NOT the actual DB.
I was able to edit the SQL statement to make it work properly.

It depends on how you go about creating the query in the first place. If you use a query wizard and follow the steps through it, at one point, IIRC, you are asked to specify a sort field or fields along with other fields to display. I've seen cases where such queries end up with both the asterisk to select all fields, plus additional, manually added fields. I often verify the SQL in the SQL Designer as a sort of hedge against that happening if I've not crafted the SQL directly from the beginning. It's not as easy to spot in the Query Grid. Sometimes a little help from Access is a too much help.

 

[WineSnob]

I have almost always used the design mode to build queries. I tried using the wizard and it worked fine, it is only when I build it in design mode that it adds the extra ,*
I think I am good now.
thanks

 

[CJ_London]

in File>Options you have probably got the Output All Fields option ticked - if so, untick it

If you want all fields for the occasional query, you can select the property on the query properties (right click in the main QBE window and select properties or use the properties option on the ribbon)

 

[WineSnob]

That did it. THANKS. Microsoft support took 3 days and still couldn’t figure that out.