FD's Diary: Getting to grips with Microsoft Access

there are at least 2 ways to create a form in excel (v2000).

Via the VBA editor create a user form, add controls and input boxes - then use VBA to display the form, capture data, and pass it to the spreadsheet.

OR

Design a form in a worksheet, and use VBA to control data capture and then pass it to a data table.

I believe a third option is to create a form in Access and then make use of Access forms in Excel (I have never attempted this, but am sure I have seen somthing in the help that describes how to do this).

Access and Excel can talk to each other via VBA making use of DAO (older but quite fast) or ADO (newer). I have used the former to create both forms and reports where data is kept in Access tables, but reported and manipulated in Excel.

Example, a monthly management accounts pack with a button in Excel which ran code to connect to source applications(OBDC)to capture monthly data, and a selection button to choose which month to display.

Example, an expenses form, which displayed lookups taken from the main time and fees application (accessed by right click on the appropriate cell), and an export button which produced a file for import to the main time and fees application, plus some programmed controls to deal with VAT and rate calculations.

I'm no tax expert, but thanks for this question.

As I read it s660a can't apply.

First this company started as a 50/50 partnership.

Then it was incorporated 50/50. So I can't see the gift.

Third, they both worked full time (well, give or take golf and other such distractions) but there's no doubt contributions weren't that different, even if in different areas.

Fourth, neither work now.

I can't see a settlement in that. Can you?

exactly right! but worth the effort

I think that your thoughts on Access are pretty near the mark - you certainly need to have a clear idea of what data you are going to store and how it all fits together. Once you have done this, though, it is certainly beneficial. With Access, there is nothing to stop you querying the data into Excel and playing around with the results (if I may put it like that, but you know what I mean). I must admit that for ad-hoc things Excel is better, but for data storage ease, Access is the thing. Its horses for courses at the end of the day.

Roland
You mention using Access and Excel in combination. Maybe there is some more mileage in this.
We had a "Reporting Challenge" last year where managers entered timesheets onto an Excel database and they wanted better reports.
Using pivot tables we could give them all the reports they wanted. But they turned it down because they disliked having to enter data into Excel in "List" mode - i.e. one line per record.
You can't enter create a Form in Excel to enter data (I think). Maybe the answer would have been to create a data entry form for them in Access to enter the data, then flow it all back again into Excel for reporting.

I can understand your irritation but maybe that needn't extend so far as conceding an adjustment. For, without data, I can't see how the auditors can come up with an adjustment that they can be confident is materially right.

So either you're going to have to find the data (which it sounds as if you want to do anyway) and then you and the auditors can form an opinion about any adjustment or stay without data and the auditors will have to make a judgement as to whether the failure to provide is likely to be material or not.

If they take a view it is not material then no adjustment. Of course, if they think its material and there is no provision, the resulting qualification may not be quite what you wanted!

sounds like one of the side benefits of changing auditors?

Too true Alastair. I know I wanted a good auditor - but this bright? :-)

what with all the sal / div arguements are you sure that the above is not relevant to you as i dont remember you nentioning it

I would have thought that if you cannot identify specific instances then no provision is necessary/required. Also is this likely to be material? If so you would be addressing the issue on cost grounds having seen how efficient you are elsewhere.