ACCESS数据库中怎样获取某一行中所有字段的值叠加在一起

Access数据库中是用VBA来编写自定义的函数，需要通过Dataobject来调用SQL语句才能使某一行中所有字段的值叠加在一起。

Access中是用VBA来编写自定义的函数，需要通过Dataobject来调用SQL语句。Access中用VBA编写的函数可以用在Access查询中。

我在Access中的实现方法如下：

新建一个Module，在里面定义自己的函数。函数定义如下：

FunctionAddString(partstringAsString)AsString

'声明查询语句字符串和字段相加的字符串变量

DimoriginalStr,queryStrAsString

'声明ADO的一个记录对象，注意用New来声明并生成实例

DimmyRecordsetAsNewADODBRecordset

originalStr=""

'查询语句字符串是通过连接零件编号生成的，注意SQL语句需要引号

queryStr="selectfrommaterialwhereparts="""+partstring+""""

myRecordsetOpenqueryStr,CurrentProjectConnection

DoWhileNotmyRecordsetEOF

originalStr=originalStr+myRecordset("module")＋","

myRecordsetMoveNext

Loop

'VBA语法，把返回值赋给函数名

AddString=originalStr

EndFunction

看到一个网友解决同样问题的做法，他不是用ADO对象，他用了下面一句：

SetFF=CurrentDbOpenRecordset(queryStr)

他实际上是隐性用了DAO对象。不过，如果你在VBA中显性声明DAO对象的话，有可能会碰到编译错误，这是因为AccessVBA默认没有包含DAO库的引用。

函数定义好后，就可以用在查询里了，SQL视图下，我的例子的SQL语句如下：

SELECTAddString(parts)ASAllMod,materialparts

FROMmaterial

GROUPBYmaterialparts

ORDERBYmaterialparts;

MSSQLServer中的例子就很多啦，也是自定义一个函数，然后把自定义的函数用在SQL语句中，我把例子也记下来吧。

createfunctionfn_AddString(@partnumbervarchar(50))

returnsnvarchar(1000)

as

begin

declare@snvarchar(1000)

set@s=''

select@s=@s+module+','frommaterialwhereparts=@partnumber

return@s

end

go

在SQL语句中调用该函数

selectparts,dbofn_AddString(parts)asAllModfrommaterial

groupbyparts

————————————————

版权声明：本文为CSDN博主「gracexu」的原创文章，遵循CC40BY-SA版权协议，转载请附上原文出处链接及本声明。

原文链接：>

遍历集合，查找重复数据，将其中重复数据信息存放到Hashtable或Dictionary集合中。实现方法举例如下。

例查找List<int> 集合中重复的数据项，将数据项重复信息存放到Dictionary集合，最后输出结果。

using System;

using SystemCollectionsGeneric;

namespace ConsoleApplication1

{

    /// <summary>

    /// RepeatInfo用来描述重复项

    /// </summary>

    class RepeatInfo

    {

        // 值

        public int Value { get; set; }

        // 重复次数

        public int RepeatNum { get; set; }

    }

    class Program

    {

        static void Main(string[] args)

        {

            // 整型列表集合。集合中有重复值

            int[] a = { 1, 1, 1, 2, 2, 2, 2, 1, 1, 1, 1, 2, 4, 3, 1, 2, 2, 1 };

            List<int> list =new List<int>(a);

            // 显示整型列表集合

            foreach (int v in list)

            {

                ConsoleWrite("{0} ", v);

            }

            ConsoleWriteLine();

            // result集合存放扫描结果

            Dictionary<int, RepeatInfo> result = 

                    new Dictionary<int, RepeatInfo>();

                    

            // 遍历整型列表集合，查找其中的重复项

            foreach (int v in list)

            {

                if (resultContainsKey(v))

                {

                    result[v]RepeatNum += 1;

                }

                else

                {

                    RepeatInfo item = 

                        new RepeatInfo() { Value = v, RepeatNum = 1 };

                    resultAdd(v, item);

                }

            }

            

            // 获取并打印出重复的数据

            ConsoleWriteLine("集合中重复的数据：");

            foreach (RepeatInfo info in resultValues)

            {

                if (infoRepeatNum > 1)

                {

                    ConsoleWriteLine("  数据项{0} 重复次数{1}", 

                        infoValue, infoRepeatNum);

                }

            }

        }

    }

}

奥巴马的竞选资金是一点点从选民那里募集来的。如获党内提名,可得政府拔款，但也没多少。美国大选不仅禁外国人捐款,而且禁止公司机构捐款,而只允许个人捐款。不仅如此,还为个人捐款限制了上限,防止富人捐过多的款而影响未来的公平执政。

不仅富人自己不能多捐，如果某个老板呼吁自己的员工给某人捐钱或投票支持他，都是犯法的。因此，想要筹到几千万竞争资金，唯一的办法是争取更多选民支持，一点点募集。所以,中国、公司、大笔捐款，这三条都是犯法的。

我记得以前已经有华人闹过这种丑闻了。美国的选举法就是要严防少数人企图用几个臭钱影响美国的政治。所以我们作为外国人就更别去自讨没趣了。

导入包

In [1]:

import numpy as npimport pandas as pdfrom pandas import Series,DataFrame

方便大家 *** 作，将月份和参选人以及所在政党进行定义

In [2]:

months = {'JAN' : 1, 'FEB' : 2, 'MAR' : 3, 'APR' : 4, 'MAY' : 5, 'JUN' : 6, 'JUL' : 7, 'AUG' : 8, 'SEP' : 9, 'OCT': 10, 'NOV': 11, 'DEC' : 12}of_interest = ['Obama, Barack', 'Romney, Mitt', 'Santorum, Rick', 'Paul, Ron', 'Gingrich, Newt']parties = { 'Bachmann, Michelle': 'Republican', 'Romney, Mitt': 'Republican', 'Obama, Barack': 'Democrat', "Roemer, Charles E 'Buddy' III": 'Reform', 'Pawlenty, Timothy': 'Republican', 'Johnson, Gary Earl': 'Libertarian', 'Paul, Ron': 'Republican', 'Santorum, Rick': 'Republican', 'Cain, Herman': 'Republican', 'Gingrich, Newt': 'Republican', 'McCotter, Thaddeus G': 'Republican', 'Huntsman, Jon': 'Republican', 'Perry, Rick': 'Republican' }

读取文件

In [3]:

table = pdread_csv('data/usa_electiontxt')tablehead()

C:\jupyter\lib\site-packages\IPython\core\interactiveshellpy:2785: DtypeWarning: Columns (6) have mixed types Specify dtype option on import or set low_memory=False interactivity=interactivity, compiler=compiler, result=result)

Out[3]:

cmte_id cand_id cand_nm contbr_nm contbr_city contbr_st contbr_zip contbr_employer contbr_occupation contb_receipt_amt contb_receipt_dt receipt_desc memo_cd memo_text form_tp file_num

0 C00410118 P20002978 Bachmann, Michelle HARVEY, WILLIAM MOBILE AL 36601e 08 RETIRED RETIRED 2500 20-JUN-11 NaN NaN NaN SA17A 736166

1 C00410118 P20002978 Bachmann, Michelle HARVEY, WILLIAM MOBILE AL 36601e 08 RETIRED RETIRED 500 23-JUN-11 NaN NaN NaN SA17A 736166

2 C00410118 P20002978 Bachmann, Michelle SMITH, LANIER LANETT AL 368633e 08 INFORMATION REQUESTED INFORMATION REQUESTED 2500 05-JUL-11 NaN NaN NaN SA17A 749073

3 C00410118 P20002978 Bachmann, Michelle BLEVINS, DARONDA PIGGOTT AR 724548e 08 NONE RETIRED 2500 01-AUG-11 NaN NaN NaN SA17A 749073

4 C00410118 P20002978 Bachmann, Michelle WARDENBURG, HAROLD HOT SPRINGS NATION AR 719016e 08 NONE RETIRED 3000 20-JUN-11 NaN NaN NaN SA17A 736166

In [8]:

#使用map函数 字典，新建一列各个候选人所在党派partytable['party'] = table['cand_nm']map(parties)tablehead()

Out[8]:

cmte_id cand_id cand_nm contbr_nm contbr_city contbr_st contbr_zip contbr_employer contbr_occupation contb_receipt_amt contb_receipt_dt receipt_desc memo_cd memo_text form_tp file_num party

0 C00410118 P20002978 Bachmann, Michelle HARVEY, WILLIAM MOBILE AL 36601e 08 RETIRED RETIRED 2500 20-JUN-11 NaN NaN NaN SA17A 736166 Republican

1 C00410118 P20002978 Bachmann, Michelle HARVEY, WILLIAM MOBILE AL 36601e 08 RETIRED RETIRED 500 23-JUN-11 NaN NaN NaN SA17A 736166 Republican

2 C00410118 P20002978 Bachmann, Michelle SMITH, LANIER LANETT AL 368633e 08 INFORMATION REQUESTED INFORMATION REQUESTED 2500 05-JUL-11 NaN NaN NaN SA17A 749073 Republican

3 C00410118 P20002978 Bachmann, Michelle BLEVINS, DARONDA PIGGOTT AR 724548e 08 NONE RETIRED 2500 01-AUG-11 NaN NaN NaN SA17A 749073 Republican

4 C00410118 P20002978 Bachmann, Michelle WARDENBURG, HAROLD HOT SPRINGS NATION AR 719016e 08 NONE RETIRED 3000 20-JUN-11 NaN NaN NaN SA17A 736166 Republican

In [10]:

#party这一列中有哪些元素table['party']unique()

Out[10]:

array(['Republican', 'Democrat', 'Reform', 'Libertarian'], dtype=object)

In [ ]:

#使用value_counts()函数，统计party列中各个元素出现次数，value_counts()是Series中的，无参，返回一个带有每个元素出现次数的Series

In [11]:

table['party']value_counts()

Out[11]:

Democrat 292400Republican 237575Reform 5364Libertarian 702Name: party, dtype: int64

In [12]:

#使用groupby()函数，查看各个党派收到的政治献金总数contb_receipt_amttablegroupby(by='party')['contb_receipt_amt']sum()

Out[12]:

partyDemocrat 8105758e 07Libertarian 4132769e 05Reform 3390338e 05Republican 1192255e 08Name: contb_receipt_amt, dtype: float64

In [13]:

#查看具体每天各个党派收到的政治献金总数contb_receipt_amt 。使用groupby([多个分组参数])tablegroupby(by=['party','contb_receipt_dt'])['contb_receipt_amt']sum()

Out[13]:

party contb_receipt_dtDemocrat 01-AUG-11 17528100 01-DEC-11 65153282 01-JAN-12 5809880 01-JUL-11 16596100 01-JUN-11 14545900 01-MAY-11 8264400 01-NOV-11 12252987 01-OCT-11 14897700 01-SEP-11 40329762 02-AUG-11 16451011 02-DEC-11 21605696 02-JAN-12 8974360 02-JUL-11 1710500 02-JUN-11 42245300 02-MAY-11 39667500 02-NOV-11 14718381 02-OCT-11 6260562 02-SEP-11 13794841 03-AUG-11 14705302 03-DEC-11 8130402 03-JAN-12 8740697 03-JUL-11 598200 03-JUN-11 32017620 03-MAY-11 26181911 03-NOV-11 11930456 03-OCT-11 36306102 03-SEP-11 4559800 04-APR-11 64023512 04-AUG-11 59878423 04-DEC-11 7279510 Republican 29-AUG-11 94176923 29-DEC-11 42850142 29-JAN-11 75000 29-JAN-12 7522002 29-JUL-11 23342335 29-JUN-11 134070429 29-MAR-11 3887500 29-MAY-11 836320 29-NOV-11 40732264 29-OCT-11 8192401 29-SEP-11 161279452 30-APR-11 4300480 30-AUG-11 91554858 30-DEC-11 49247045 30-JAN-12 25520480 30-JUL-11 1224904 30-JUN-11 274493263 30-MAR-11 5024000 30-MAY-11 1780360 30-NOV-11 80901483 30-OCT-11 4391316 30-SEP-11 488633176 31-AUG-11 101773502 31-DEC-11 109437672 31-JAN-11 600000 31-JAN-12 86989041 31-JUL-11 1278102 31-MAR-11 6247500 31-MAY-11 30133980 31-OCT-11 73460183Name: contb_receipt_amt, Length: 1183, dtype: float64

In [14]:

def trasform_date(d): day,month,year = dsplit('-') month = months[month] return "20" year '-' str(month) '-' day

In [17]:

#将表中日期格式转换为'yyyy-mm-dd'。日期格式,通过函数加map方式进行转换table['contb_receipt_dt'] = table['contb_receipt_dt']apply(trasform_date)

In [18]:

tablehead()

Out[18]:

cmte_id cand_id cand_nm contbr_nm contbr_city contbr_st contbr_zip contbr_employer contbr_occupation contb_receipt_amt contb_receipt_dt receipt_desc memo_cd memo_text form_tp file_num party

0 C00410118 P20002978 Bachmann, Michelle HARVEY, WILLIAM MOBILE AL 36601e 08 RETIRED RETIRED 2500 2011-6-20 NaN NaN NaN SA17A 736166 Republican

1 C00410118 P20002978 Bachmann, Michelle HARVEY, WILLIAM MOBILE AL 36601e 08 RETIRED RETIRED 500 2011-6-23 NaN NaN NaN SA17A 736166 Republican

2 C00410118 P20002978 Bachmann, Michelle SMITH, LANIER LANETT AL 368633e 08 INFORMATION REQUESTED INFORMATION REQUESTED 2500 2011-7-05 NaN NaN NaN SA17A 749073 Republican

3 C00410118 P20002978 Bachmann, Michelle BLEVINS, DARONDA PIGGOTT AR 724548e 08 NONE RETIRED 2500 2011-8-01 NaN NaN NaN SA17A 749073 Republican

4 C00410118 P20002978 Bachmann, Michelle WARDENBURG, HAROLD HOT SPRINGS NATION AR 719016e 08 NONE RETIRED 3000 2011-6-20 NaN NaN NaN SA17A 736166 Republican

In [19]:

#查看老兵(捐献者职业)DISABLED VETERAN主要支持谁 ：查看老兵们捐赠给谁的钱最多table['contbr_occupation'] == 'DISABLED VETERAN'

Out[19]:

0 False1 False2 False3 False4 False5 False6 False7 False8 False9 False10 False11 False12 False13 False14 False15 False16 False17 False18 False19 False20 False21 False22 False23 False24 False25 False26 False27 False28 False29 False 536011 False536012 False536013 False536014 False536015 False536016 False536017 False536018 False536019 False536020 False536021 False536022 False536023 False536024 False536025 False536026 False536027 False536028 False536029 False536030 False536031 False536032 False536033 False536034 False536035 False536036 False536037 False536038 False536039 False536040 FalseName: contbr_occupation, Length: 536041, dtype: bool

In [21]:

old_bing_df = tableloc[table['contbr_occupation'] == 'DISABLED VETERAN']

In [22]:

old_bing_dfgroupby(by='cand_nm')['contb_receipt_amt']sum()

Out[22]:

cand_nmCain, Herman 30000Obama, Barack 420500Paul, Ron 242549Santorum, Rick 25000Name: contb_receipt_amt, dtype: float64

In [23]:

table['contb_receipt_amt']max()

Out[23]:

194404243

In [24]:

#找出候选人的捐赠者中，捐赠金额最大的人的职业以及捐献额 通过query("查询条件来查找捐献人职业")tablequery('contb_receipt_amt == 194404243')

Out[24]:

cmte_id cand_id cand_nm contbr_nm contbr_city contbr_st contbr_zip contbr_employer contbr_occupation contb_receipt_amt contb_receipt_dt receipt_desc memo_cd memo_text form_tp file_num party

176127 C00431445 P80003338 Obama, Barack OBAMA VICTORY FUND 2012 - UNITEMIZED CHICAGO IL 60680 NaN NaN 194404243 2011-12-31 NaN X SA18 763233 Democrat

来源：>

1什么是pandas numpy模块和pandas模块都是用于处理数据的模块。 numpy主要用于针对数组进行统计计算,处理数字数据比较方便。 pandas除了可以处理数字数据,还可

以上就是关于ACCESS数据库中怎样获取某一行中所有字段的值叠加在一起全部的内容，包括:ACCESS数据库中怎样获取某一行中所有字段的值叠加在一起、pandas透视之后如何表头很奇怪、c# 怎么获取一个集合中重复的数据等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！