Inserting Records In The Middle Of An Access Table

Re: Inserting Records In The Middle Of An Access Table

Hi there,

In terms of adding new records to a table in an Access Database, why do you care about the order? Surely, as long as the relevant ID field has the appropriate value, the order of the record in the database should not make a difference?

Later on, you can always tweak the order all you want in the queries and reports based on this data.

:ninja:

Re: Inserting Records In The Middle Of An Access Table

well, you can import the zero balances in as well, then create delete query to remove the zero balances.

Re: Inserting Records In The Middle Of An Access Table

Hi there "Humble Access VBA Newbie".

The first issue you need to address is the structure of your table.

You should set a key and in this instance, in order to maintain multiple records per client, you will need a composite key made up of CusomerID and Date. If the key were to be just CustomerID, you would be only able to maintain one record per customer and just update date and amount - unless this is what you are trying to do, although this is not how I have interpreted your post. So with the composite key, you will be able to maintain an entry for each customer, for each period that they have an outstanding amount.

Next we address getting the data into the table and this raises a question. Are you wanting to do this programmatically, or are you happy to do it manually?

Manually, as long as the columns in the spreadsheet are in the same order as the table ie CustomerID, Date, Amount, even if there are other columns in the spreadsheet, you can copy and paste the data straight into your table (leaving out the column headers). I am hoping that the previous poster was taking your post title a little too literally as you will be adding these records to whatever records already exist ie to the end not inserting into the middle somewhere. Access can take care of the sequence, especially if you use the composite key I have suggested above. When retrieving the data in a report or query, it is simple enough for the result to be ordered by customer and date.

If you are wanting to do it programmatically, this humble, somewhat experienced but not expert Access/VBA programmer suggests that you are perhaps aiming a little high when it comes to chosing a first project for learning Access/VBA. If you are in a hurry to resolve this then I would suggest you recruit a professional to do it for you.

If you are not in a hurry, but are happy to utilise the manual method I have outlined for a while, I am considering producing a "template" Access app which will demonstrate how data can be moved between Access and Excel. Either getting data from Excel or pushing it to Excel seems to be a pretty common issue and I think deserves this sort of attention. I would seek to perhaps have this sample added as a "stickie" or at least post it into the sample code forum on this site.

I have stated I am considering this as I am both a student and a tutor at one of the local universities. This, plus maintaining a small number of private clients, means that spare time is somewhat precious. It may be that someone else has already done this so I suggest a troll through the forum here which offers sample code - I will certainly do so before embarking on building a sample of my own.

Hope the above helps.

Regards

Rowan