如何计算一个表中的记录并动态填充值 [英] How can I count the records from one table and populate a record with the value dynamically

问题描述

我使用的是MS Access 2007.

我正在收集城镇地区不同类型企业的数据，按街道，商家排列。

在TableA中，我列出了不同的特定街道，没有重复，字段：街道;和另一个字段：NumOfBusinesses。

在TableB中，我有一个不同的特定业务，

 TableA 
 
 STREET .................. NUMOFBUSINESSES 
 
高街
羊街
银街
金街
 
 TableB 
 
 BUSINESS ................ STREET 
 
 McD's High Street 
 Hair Dresser High Street 
 Shoeshop Gold Street 

我想要做的是计算每个街道从TableB的商业量，并添加

我有超过600条街道，所以我不想手动通过每个单独的计数，并将其添加到

是否有我可以使用的查询类型，甚至是我可以放入作为Num ... sses字段的默认值的代码，因此它会根据记录中的相关街道动态地更改查询。

因此，本质上，我希望TableA看起来像这样自动

 TableA 
 
 STREET .................. NUMOFBUSINESSES 
 
高街2 
 Sheep Street 0 
银街0 
金街1 
 

解决方案

您可以从查询中获取结果，那么您真的需要更新字段吗？

  SELECT Tablea.Street，计数（业务）
 FROM TableA 
 INNER JOIN Tableb 
 ON TableA.Street = TableB.Street 
 GROUP BY Tablea.Street 
  

通常不建议您存储计算数据。

如果您真的必须存储计数，您可以说：

  UPDATE tablea 
 SET numofbusinesses = 
 DCount（*，tableb ，street ='& Replace（street，'，''）& '）
 WHERE Street is Not Null 
  

I am using MS Access 2007.

I am collecting data of different types of businesses within a town area, arranged by streets, the business.

In TableA, I have a list of different specific streets, no duplicates, field: Street; and another, field: NumOfBusinesses.

In TableB, I have a list of different specific businesses, Business; and what street they are on, Street.

eg

TableA

STREET..................NUMOFBUSINESSES

High Street
Sheep Street
Silver Street
Gold Street

TableB

BUSINESS................STREET

McD's                    High Street
Hair Dresser             High Street
Shoeshop                 Gold Street

What I want to be able to do, is count the amount of business per street from TableB and add that into the TableA, corresponding to each street.

I have over 600 streets, so I don't want to have to manually go through each individual count and add it in manually.

Is there a type of query I can use, or even code I could put in as the default value of the "Num...sses" field, so it dynamically changes the query depending on the relevant street in the record?

So in essence, I want TableA to look like this automatically

TableA

STREET..................NUMOFBUSINESSES

High Street             2
Sheep Street            0
Silver Street           0
Gold Street             1

解决方案

You can get the results from a query, so do you really need to update a field?

SELECT Tablea.Street, Count(Business) 
FROM TableA 
INNER JOIN Tableb
ON TableA.Street = TableB.Street
GROUP BY Tablea.Street

It is not usually recommended that you store calculated data.

If you really must store the count, you can say:

UPDATE tablea 
SET numofbusinesses=
   DCount("*","tableb","street='" & Replace(street,"'","''") & "'")
WHERE Street Is Not Null

这篇关于如何计算一个表中的记录并动态填充值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！