MS Access table issue after adding row

strongman86

Board Regular
Joined
Feb 15, 2017
Messages
115
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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).
 
Upvote 0
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
ABCDEFG
1statusproduct typecolour
2LIVE1Redabadae
3LIVE2blue
4LIVE3green
5LIVE4blue

<tbody>
</tbody>

After row added (Query data refresh)
ABCDEFG
1statusproduct typecolour
2LIVE0Yellowabadae
3LIVE1Red
4LIVE2blue
5LIVE3green

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
Joe4,

Yes it's located in excel spreadsheet. Is there a way to connect D-G columns?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top