Hello from China (moved from Introductions because it has questions and answers)

[minn]

Hi, everyone

I work for a company having export business as accountant in China, and I am required by my boss to help find a solution to get sales data from sales department and send to accounting model. what I am thinkiing about is to use access to facilitate such need. however, I don't know access much, but, I believe in access can do the job.

we are using a local accounting software ( only general ledger ) developed by a Chinese software firm. but, I found the tool here, which is capable of populate data from access/excel/sql server or anything to the general ledger of the accounting model, and the accounting entry could be automatically created in the data transmition.

Given this task, what can I do? develop the sales model using access by learning all the technique from you here in this forum? probably that is a solution, but, looks not really quick helping me get the job done; or, maybe get some education and consultancy from some gurus here, I think my boss can pay some bill if the task could be arrange effectively, however, I am not quite sure about this yet; and the last solution is to contract a projector so that I can get the turnkey project run in a very short time period, for this, I definitely will ask my boss to pay, but, depands on the size of the charge.

Since I don't have the IS knowledge to deliver the job, and in China, we are usually tended to be assigned some unrealistic job. so, let me jump into the river without knowing how to swim, and your help is gratefully expected.


Kindest Regards
Minn

 

[Galaxiom]

Re: Hello from China

Hello Minn and welcome to AWF. Things are bit quiet here because it is Christmas but you have certainly come to the right place. We have many lifesavers here.

If you are just trying to convert data from a sales systems there is good chance that we will be able to get you though this without spending money on a consultant since there would be minmal user interface requirments.

Certainly the ability of the general ledger program to import from Access is a very good start.

The task of getting the data into Access depends very much on the nature of the data you are provided with by your sales department. There are a number of ways of handling it depending on how it is structure.

I expect they can provide you with text based files containing the data. This is often produced using a text report which presents a table that can be saved in a csv file or similar. Access can often import these files directly to tables. Sometimes it requires a bit of manipulation on the way to remove the report headers and other extraneous lines.

Once this is done Access can be used to manipulate the data into the structure required by the ledger import system.

The first thing you need to discover the structure of the data provided by the sales department and the table structure required by the general ledger.

BTW. What part of China are you working? I visited China for work in 1999 and 2001 and had a wonderful time.

 

[minn]

Re: Hello from China

Hi,GalaxiomAtHome

Thanks a lot for the welcome and your advisary. currently, sales department distribute sales data in excel spreadsheet, and I want it to be in access, reasons :

1] which looks more like a database, and excel seems not a database to me
2] I also wish the sales department maintain the sales data in a structure that meets requirement of the interface specs ( the interface is made ready ), and the data source is not open to change without authorization
3] order confirmation, packing list, and invoice can be automatically distributed to customers through ms outlook
4] the sales data is maintained in a secure manner, meaning a definition of authorization profile as control point
5] multiuser instances

access seems a good solution; but, I don't know how to use access. my first question is if learning access to achieve above requisit by myself is realisable in two or three months, of course, I have full time job to handle with in the meantime. if the answer is positive, probably can you please direct me to some quick resource. I previously spent some time learning the textbook, but, could not pull the pieces into a practicable exercise. so, it might be nice to follow up a case study step by step.

I am not sure if I made my points understandable, coz my English is really very limited. particular when talking about IS terminologies, I truelly feel weak in that capacity......

Kindest Regards
Minn

 

[Galaxiom]

Re: Hello from China

First thing to do is try to link a spreadsheet to an Access table. There is a specific import facility for Excel in the External Data of Access 2007. Just follow the Wizard. Once you achieve this the process can be refined but the basic import or link is your first goal.

 

[minn]

Re: Hello from China

Hi,GalaxiomAtHome

I followed up your recommendation, it seems a successful experience, that's really fantastic, thank you, GalaxiomAtHome.

I really wish could attach my exercise here, not sure if it is advisable to do so ...... ( everything in my exercise is fictional )

Maybe the next step for me is to design a form where our sales clerk can input the data, and then, send the data into the table I created today?

Meanwhile, this seems an introduction thread, probably I need to change my thread somewhere, a bit concern is if I will be able to keep in touch with your help, if I am not over requesting ? I knew you are very busy, and I am really appreciated your kind help.


Kindest Regards
Minn

 

[Galaxiom]

Re: Hello from China

I really wish could attach my exercise here, not sure if it is advisable to do so ...... ( everything in my exercise is fictional )

It is possible to attach a database to your posts.

First step is to delete all but a few records. Keep just enough records to show the nature of the data. Ideally, save the database as an mdb format so more people will be able to open it as not everyone has 2007.

Then do Compact and Repair. In 2007 this is available in the Manage section under the round Office button. This step removes the deleted records completely and reverts everything to its base level.

Then zip the database.

You can then attach the zip file from the Advanced Editor in the forum.

Maybe the next step for me is to design a form where our sales clerk can input the data, and then, send the data into the table I created today?

A form to do that is reasonably advanced as it required VBA coding especially if you want to navigate to a document. Before then you need to agree with your sales department on what will be in each report and how you will ensure that the same data is not imported twice. It can be as simple as knowing that a certain date is the indicator but it needs to be defined with their cooperation. A record number field that uniquely identifies each record is is another very reliable way to ensure no repeats.

The imported data is usually just a staging table to hold the report for processing. Your main table will hold the accumulated data from all the imports. We will write a query to move the data across while ensuring that we do not repeat records.

How many records would be typically imported on each report?

I would also test the import process to the GL program is working as expected. This will reveal any special needs it might have with regards to formatting.

You will need to run a test environment for that program as GLs don't usually let you remove records. They just write another record to cancle the first. This would annoy your accountant.

Meanwhile, this seems an introduction thread, probably I need to change my thread somewhere, a bit concern is if I will be able to keep in touch with your help

Yes that would be good. It is also good to try and keep the subect of each thread fairly focussed and open anonther for new questions.

You can paste a link in this thread so we can follow you to the new question.

 

[minn]

Re: Hello from China

Hi,GalaxiomAtHome

I am glad that I can follow you. I will create a new thread next time login, when I will be ready to specify the sales needs in the report, and, of course, after compacting my mdb file.


Kindest Regards
Minn

 

[minn]

Re: Hello from China

Hi, Galaxiom

I opened a new thread in Microsoft Access Discussion > General under the title of "Learning Access from scratch "

I hope could see you in my thread. since my posts are less than the minimum of 10 before can paste a link here, so, apologise for the inconvenience.

Kindest Regards
Minn

It is possible to attach a database to your posts.

Yes that would be good. It is also good to try and keep the subect of each thread fairly focussed and open anonther for new questions.

You can paste a link in this thread so we can follow you to the new question.