Access Application log & lt; 1 & gt;

Today, I tried to build a small access Database Based on my business needs after I confirmed that my intern could not help set up the database. The main purpose is to store historical monthly data to avoid the trouble of downloading data from the company database every time and save the data pulling time. Set up two types of data with acctid as the primary key. 1) customer information, including Manual updates by business personnel

Today, I tried to build a small access Database Based on my business needs after I confirmed that my intern could not help set up the database. The main purpose is to store historical monthly data to avoid the trouble of downloading data from the company database every time and save the data pulling time. Set up two types of data with acct id as the primary key. 1) customer information, including Manual updates by business personnel

Today, I tried to build a small access Database Based on my business needs after I confirmed that my intern could not help set up the database.

The main purpose is to store historical monthly data to avoid the trouble of downloading data from the company database every time and save the data pulling time.

Set up two types of data with acct id as the primary key.

1) customer information, including

The business personnel manually update the summarized data, such as the customer's region and business information.

Customer Information captured by machines, such as the customer account status and exclusive business personnel information.

The above data is regularly updated every month and updated every week, and stored in different tables.

2) Customer transaction information, plans include:

Summarize monthly information, such as monthly turnover and transaction volume;

Table in multiple dimensions, such as weekly information, transaction type, and transaction source. The data refresh period varies with business requirements.

In addition, for convenience, the common "Jan-2014" in the database is subdivided into two variables: "Year" and "Month". In this way, when data is imported and output in the future, in excel, you can use date (year, month, day) to quickly synthesize dates without worrying about the data format.

By building multiple tables, we split the data that has been integrated into multiple tables in excel, and the refresh frequency between the tables is different, which reduces the matching time in excel vlookup.

At the same time, I created two queries.

1) integrate basic customer information, including information used by business personnel and information captured by machines. (Here, the account unique identifier is used as the association identifier, and the connection attribute is modified, that is, the so-called left connection and right connection)

2) combine the summary monthly information with the customer's basic information into a large table. This large table will be directly imported into excel as part of the pivot table.

In addition, the following operations are available:

1) Delete query: You can delete data that does not meet my requirements. (For example, you can delete the data of the current month when importing data every month to avoid data duplication)

2) append query: it is mainly used for customer transaction information and regularly append monthly/quarterly data.

The following problems need to be solved:

1) How to quickly find the customer information to be appended and append the information after importing the new account information (it is planned to find unmatched items)

2) how to delete Unqualified Data, join tables, and determine subsequent data import. (I believe the code can be implemented, but I have not explored it yet)

3) Build a friendly access interface or write a document

Summary:

Access is not commonly used in the initial stage of data processing. However, as the data size expands, we naturally need to use multiple databases.

Starting from access, I think it is relatively easy.

As well as being proactive, it means you must study hard. I didn't get a satisfactory response when I assigned this task to an intern, but I had to work on it myself. On the basis of being familiar with the business, I was barely able to complete a database meeting the general requirements. In the future, you need to try more and standardize the template.

Furthermore, I knew nothing about ACCESS when I set up a CRM system for a TEAM a year ago. This time, after many thoughts, I successfully completed the establishment of the framework and praised myself! \ (^ O ^ )/~