Build a small Access database record _access

Today, after confirming that interns couldn't help build a database, they tried to set up a small Access database based on business requirements.

Main purpose: To store historical monthly data, avoid the trouble of downloading data from company database every time, save data pull time.

Two kinds of data with the Acct ID as the primary key are built.

1 Customer information, including

Manually update the aggregated data by the business person, such as customer area, business information, etc.

Customer information crawled by the machine, such as customer account status, exclusive business personnel information, etc.

The above data is stored in different table according to regular monthly update and weekly update.

2 customer transaction information, the plan includes:

Summarize monthly information, such as monthly turnover, trading volume, etc.

Tables with multiple dimensions, such as week information, transaction type, transaction source, and so on, this data refresh time period is different, and according to business needs, there will be various dimensions of demand.

In addition, in order to facilitate, the database common "Jan-2014" into the "year" and "Month" two variables, so that data import output, do not bother with data format problems, in Excel, directly with date (Year,month,day) You can quickly synthesize dates.

By building multiple tables, the data that has been consolidated in Excel is split into multiple tables, and the refresh rate between the tables is different, reducing the time we use Excel VLOOKUP to match.

And I've created two new queries.

1 The integration of basic customer information, including information used by business personnel, as well as the information crawled by the machine. (Here the account unique identification code as the associated identity, and modify the connection properties, that is, the so-called left connection right)

2 The summary of the monthly information with the customer's basic information, combined into a large table. The large table is then imported directly into Excel as part of the PivotTable report.

In addition, regarding the operation, presently groped out:

1 Delete query: You can delete the data that do not meet my requirements (such as import data every month, delete the current month data, avoid duplication of data)

2) Additional query: Mainly used for customer transaction information, periodic additional monthly/quarterly data.

The issues that need to be addressed now include:

1 How to quickly find additional customer information after importing new account information, and append (plan to find unmatched)

2 How to delete the already unqualified data, and the union between the tables, as well as the decision of subsequent data import work. (believe that the code can be implemented, but not yet explored)

3 to build a friendly access interface, or write a document to save the manuscript

Summarize:

Access is not commonly used at the beginning of our data processing. But as the volume of data expands, we naturally use multiple databases.

Starting with access, it's easier for individuals to find out.

As well, active, representative, to study hard. When the task was assigned to interns, not a satisfactory answer, but their own toss, on the basis of familiar with the business, but also barely able to complete a database with the general requirements. In the future, we need to try more and standardize the template.

And again, than a year ago for a team to build a CRM system for access to the Greek, this time after a lot of thinking, very smooth completed the framework of the building, to praise their own one! \ (^o^)/~