MS Access table issue after adding row

[strongman86]

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]

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]

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
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]status[/TD]
[TD]product type[/TD]
[TD]colour[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]LIVE[/TD]
[TD]1[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]ab[/TD]
[TD]ad[/TD]
[TD]ae[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]LIVE[/TD]
[TD]2[/TD]
[TD]blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]LIVE[/TD]
[TD]3[/TD]
[TD]green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]LIVE[/TD]
[TD]4[/TD]
[TD]blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

After row added (Query data refresh)
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]status[/TD]
[TD]product type[/TD]
[TD]colour[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]LIVE[/TD]
[TD]0[/TD]
[TD]Yellow[/TD]
[TD][/TD]
[TD]ab[/TD]
[TD]ad[/TD]
[TD]ae[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]LIVE[/TD]
[TD]1[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]LIVE[/TD]
[TD]2[/TD]
[TD]blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]LIVE[/TD]
[TD]3[/TD]
[TD]green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

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]

Joe4,

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

 

[strongman86]

Anyone? What about marco's?

 

[Joe4]

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]

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...anually-entered-data-external-data-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]

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.

 

[rondeondo]

Hi Strongman.

Are you adding data in excel sheet next to data that is linked to an access table? or adding data to an access table that is linked to an excel sheet?

From the conversation it seems to me it may be the Excel sheet with data linked in from Access, in which case the excel sheet is acting as an output to the access database and the additional data you are adding is not related in any way to that.

If this is the case it's going to take a bit to keep it together as it's a little like printing a report and writing on the paper then printing a new copy of the report and wanting the writing to appear on the new copy.

So to accomplish this you'll need a unique identifier in the data coming in from access, line number, some kind of ID.
In your additional comments you need to include this ID along with what you're adding.
You then need a vlookup to put your additional data into the table next to the access data.

If this sounds about right let me know and send me your excel sheet and I can add the bits or explain further.

Ron