The data update function of access

Originally, it took a lot of effort to help an access blind build a database, and the bounty points were not attractive enough, and few people did this kind of thing. However, for the sake of your love of the Three Kingdoms and the songs you listen to are distinctive, I will challenge the difficulty, come on.

First of all, the material of ** is temporarily assumed to be unchanged, after all, the Expo is only 7 months old. Even the adjustments are small and can be adjusted manually, so the database is not designed to take this aspect into account.

The first step is to create a table

Establish the "Bill of Materials" table, which includes the material number (primary key), the material name, the material unit, the description 、......

Establish "Material Storage", the content includes date, material number, purchase quantity, ** point ......;

Establish "Material Issuance", the content includes date, material number, amount issued, ** point ......;

Note, if you only calculate the quantity, the database is very simple; If the unit price is calculated, and the unit price remains unchanged in these 7 months, then as long as a unit price is added to the "bill of materials", it is also very simple; If the unit price is to change, the database will be complex, and you may not be able to complete the ...... in a short time

The second step is to create a query

1. Establish your own warehouse inventory quantity inquiry:

Use the wizard to automatically generate a detailed query of material incoming materials (only select the material number and purchase quantity when selecting the field), and then, right-click Summarize in the design view, the material number is grouped, and the purchase quantity is summed. In this way, the purchase volume is summed up.

In the same way, a summary of shipments is automatically generated.

Do these two queries first, if successful, and then establish the association query of these two queries (build a connection with the item number), and subtract the inventory.

2. Establish the quantity query of each ** point;

Basically the same as above, add ** point field, use grouping.

Ning Cangya is bored What to play.

With access

There are three ways to import external data (excel):

Right-click the table and select Link Table Manager.

Select the linked table that needs to be updated, and then click OK.

Function of automatic update of external data linked to access: Right-click on the table and select Link Table Manager. Bichai selects the link table that needs to be updated, and then regrets clicking "OK".

The programming skills are too thin, and I don't have much time to study them one by one**. Now I mainly rely on excel** to do monomer data, and then use access to package it into a source, and finally rely on the powerful retrieval function of fox table to meet the needs of daily work.

The purpose of access is twofold:

Used for data analysis: Access has powerful data processing and statistical analysis capabilities, and can easily perform various statistics such as summary and average by using the query function of ACCESS. In addition, the conditions for statistics can be flexibly set.

For example, when statistically analyzing data of tens of thousands of records, hundreds of thousands of records and above, it is fast and easy to operate, which cannot be compared with Excel. This is reflected in the fact that Zhaolian will use access, which improves work efficiency and work ability.

1. Open Access, and click on the menu bar in turn: Create - Query Design.

2. In the pop-up dialog box that displays the table, we select the order table, and then click Add. That is, the table to be updated is the order table.

3. Drag and drop the customer ID into the field name below.

4. Click the update button on the menu bar to open the finger dust design window of the update query.

5. You can see in the customer table that the ID of Guohao is 7, and the ID of Guangming Magazine is 11.

6. So, enter 10 in the Update to this column and 7 in the Condition column.

7. Click the Save button on the menu bar.

8. Enter the name of the query in the pop-up dialog box, and then click OK, so that the data table will be updated.

In Access, updating is different from adding.

Version: Access 2013

1. Update data.

The original data is as follows:

To change the column "Relationship with bad" in "08 Physics" to "Bad's only fan", you can use the following statement:

update new table set [relation to bad] ='Bad fan' where name='08 Physics'

Updated Results:

2. Add data.

In the preceding table, you need to add an entry with id set to 4, name to Q&A group Xiaohui, and relationship with bad to Nothing to Related. The following statement can be used:

insert into new table(id,name,[relationship with bad]) values (4,'Q&A group Xiaohui','It doesn't matter')

Post-execution result:

Here's how:

Method 1: Enter the database, open the table you want to add, and enter it directly in the data table view.

The name of the insert into table (column 1, column 2,..values(value 1, value 2,...)

Note] 1If it is textual, it should be quoted.

2.Add on both sides of the date