MS Access table issue after adding row

**strongman86** · Apr 16th, 2018, 12:22 PM

Lads,

I'm working with Table that uses query from MS Access and I have added manually few columns like you would normally do on excel. I have manually filled cells with data. When data is refreshed from query and if rows are automatically added it shifts all data to different rows, but data entered manually by me on added columns stays on the same row. Is there a way to make them also shift? Thanks.

**Joe4** · Apr 16th, 2018, 12:27 PM

I am not quite clear I understand what you are saying.

You can only add new fields that you can hard-code data into in the Table.
In a Query, the only type of fields you can add are Calculated Fields (which you cannot hard-code into, because they are formulas).

**strongman86** · Apr 16th, 2018, 01:01 PM

I don't know how to explain it in any other way. Lets say below columns A/B/C are connected to query and columns E/F/G are contain values that I need to change manually. If one row is added automatically by query after refresh it shifts all data 1 row down. See second table where data LIVE/0/Yellow were added by query. Manually added data on cells E2/F2/G2 need to shift down 1 row as well as data is relating to LIVE/1/Red. Hope this makes sense. Sorry if this sounds silly, but I have never worked with tables that are connected with query. Thanks.

Current

	A	B	C	D	E	F	G
1	status	product type	colour
2	LIVE	1	Red		ab	ad	ae
3	LIVE	2	blue
4	LIVE	3	green
5	LIVE	4	blue

After row added (Query data refresh)

	A	B	C	D	E	F	G
1	status	product type	colour
2	LIVE	0	Yellow		ab	ad	ae
3	LIVE	1	Red
4	LIVE	2	blue
5	LIVE	3	green

**Joe4** · Apr 16th, 2018, 01:09 PM

Where exactly is this Table located?
Is it an Access table, or an Excel table?

If it is a Table Excel that is set up for columns A, B, and C, then your other columns (D-G) are separate and outside of your table.
Since there is no connection between those columns, and columns A-C, they would not move with data added to the Table.

**strongman86** · Apr 17th, 2018, 02:26 AM

Joe4,

Yes it's located in excel spreadsheet. Is there a way to connect D-G columns?

**strongman86** · Apr 17th, 2018, 06:24 AM

Anyone? What about marco's?

**Joe4** · Apr 17th, 2018, 07:53 AM

Anyone? What about marco's?

A little patience please. You posted at 2:00 AM and 6:00 AM my time, and I am usually sleeping then.

You would need to expand the columns of your table.
However, it is a linked Table/Query from Access, I do not know if that will work (unless you "unlink" it and make it a permanent copy in Excel no longer related to Access).
Otherwise, you may need to add the new columns in Access, then re-link/refresh that in Excel.

**strongman86** · Apr 17th, 2018, 09:19 AM

Joe4,

Unlinking is not an option here it needs to up to date all times and neither editing database as I have no editing access/rights. I have come up with this post : https://www.mrexcel.com/forum/excel-...ata-query.html

And I'm quite confused and too dumb to understand it. Could this approach would work you think? If yes would you be able to explain it in more simple way? Much appreciated.

**Joe4** · Apr 17th, 2018, 09:35 AM

I think that approach could work, though I have never used it myself.
As it is a method that I have never attempted myself, I don't think I could explain it any simpler than Fazza did.
You may want to reply to that thread where Fazza describes the method, and mention that you have a similar situation that you are trying to get to work (and provide a link to this thread there).
Maybe Fazza will see it and weigh in here.