Export invoices to accounting system

[Guest]

We want to create sales invoices in our operational system (Access) then
export the necessary fields (about ten of them) into our accounting system
(Sage, a UK package).
I have the invoice structure and table all set up.
What I need is some way of identifying which invoices have not yet been
exported, then ideally clicking just one button to create an export file
(csv) which can then be imported into Sage. Sage has a good import facility
so that's not a problem once the csv has been created. Clearly the invoices
will need to be flagged with an "exported" status once they have been
included in a batch, to prevent them being sent again.
I would be really grateful for some detailed advice please - or any demo mdb
from which I could get some ideas.
Many thanks
CW

 

[Guest]

As you said, you will need a field in your table that will indicate whether
the record has been exported. A simplistic way would be to create an
additional query with the same selection criteria as the export query that
would be an update query to set the field to the value that would indicate it
has been exported.

The problem with that approach, however, would be that you would not have a
way at the time of the export to know whether it has been successfully
received by the Accounting package.

To ensure the systems stay in balance, it may be wise to create an export
from the accounting package that would a list of newly imported transactions.
Then base the update in the Access inventory system on that. Then, you
would know which transactions were successfully transfered.

 

[Guest]

Dave -

Many thanks for your response.

(1) I'm OK with the basics of building an update query but I'm struggling a
bit when it comes to this specific one - what it needs to do is find those
invoices where the "Date exported" field is null, and populate those fields
with the current date.
I have made a couple of attempts but didn't have any success at all. Would
you be so kind as to suggest exactly how I need to build this? I would be
really grateful!

(2) Re checking what has actually arrived in the accounting system, good
point and happily it shouldn't be a problem as Sage has a fairly useful
inbuilt report generator and I will use that to tell us what it has received.

Thanks again
CW