使用Access巧妙合并多个Excel文件.docx
《使用Access巧妙合并多个Excel文件.docx》由会员分享，可在线阅读，更多相关《使用Access巧妙合并多个Excel文件.docx（33页珍藏版）》请在冰豆网上搜索。
使用Access巧妙合并多个Excel文件
使用Access巧妙合并多个Excel文件
Webjx介绍：
合并多个Excel工作表是一项常见的工作任务，当然，也有许多技巧来帮助完成这项任务。
下面是一个简单的方法，利用Access来合并多个Excel文件。
合并多个Excel工作表是一项常见的工作任务，当然，也有许多技巧来帮助完成这项任务。
下面是一个简单的方法，利用Access来合并多个Excel文件。
在Access中创建对Excel工作簿的链接：
在Access中选择“外部数据——导入——Excel”
在“获取外部数据”对话框中选择“通过创建链接表来链接到数据源”。
必须为每个Excel工作簿重复该过程直到将它们都链接到数据库。
结果如下图所示。
在SQL视图中开始新的查询，单击“创建——其他——查询设计”。
选择“查询工具-设计”中的“SQL视图”
在SQL视图中，为所有链接的Excel表输入Select语句，在每个Select语句间使用Union操作符，在SQL视图中输入选择查询语句
运行查询后，结果将是包含从所有链接的文件中获取并合并后的数据。
只要链接的Excel文件在相同的位置，每次打开数据库时Access将自动刷新链接以获取最新数据。
这意味着只需设置一次，然后在每次需要重新组合Excel文件时运行联合查询。
然而，默认情况下，UNION操作符会移除数据中的任何重复行。
如果知道数据中有重复行，并且想保留它们，那么需要使用UNIONALL，以允许传递重复行。
sql中执行select'a'asf1unionallselect'b'asf1会返回2条记录而再access中却报错误：
“查询输入必须至少包含一个表或查询”如果现在存在2个表a1,b1字段只有一个都是f1,记录都只有一条，分别是a和b再执行selectf1froma1unionallselectf1fromb1却可以成功，难道access中的union语句中一定要含有目标表吗，即一定要有from语句吗？
一、基本语句
1、ACCESS中一般的select
Select要查询的字段（全部查询则为“*”）fromTabel名where查询条件
selectID,姓名,性别,生日from会员信息;
ACCESS中的字符串可以用双引号分隔,建议用单引号作为字符串分隔符.
ACCESS中日期查询
Select*FromTab1Where[Date]>#2010-1-1#;
例：
Select记录时间From记录Where时间>=#2009-11-1#;
Select记录时间From记录Where时间<#2009-11-1#;
注意:
ACCESS中的日期时间分隔符是#而不是引号
2、ACCESS中UPDATE
UPDATETab1a,Tab2bSETa.Name=b.NameWHEREa.ID=b.ID;
即:
ACCESS中的UPDATE语句没有FROM子句,所有引用的表都列在UPDATE关键字后.
3、delete
DeletefromTabel名where删除条件
若将表中记录全部删除则：
Delete*fromTabel名
例：
deletefrom记录Where时间<=#2005-4-1#;
二、基本函数
1、计算函数
1）函数count（）—计算纪录数
Selectcount（*）From会员信息;
Selectcount（ID）From记录Where时间>=#2009-11-1#;
2）函数sum（）
Selectsum（记录数）From记录;
Selectsum（记录数）From记录Where时间>=#2009-11-1#;
3）max（）—求最大值函数
Selectmax（记录数）From记录;
Selectmax（记录数）From记录Where时间>=#2009-11-1#;
2、日期函数
1）Date（）返回一个表示当前系统时间的格式化字符串
select记录时间,mid（date（）,1,7）from记录;
※Format（）将一个日期变体或一个数值转化成格式化字符串
select记录时间,format（记录时间,'yyyy/mm/dd'）from记录;
select记录时间,mid（format（时间,'yyyy/mm/dd'）,1,7）from记录;
select记录时间from记录wheremid（format（记录时间,'yyyy/mm/dd'）,1,7）='2005-11';
selectdistinctmid（format（记录时间,'yyyy/mm/dd'）,1,7）from记录;
2）Year（Date）返回日期中的年值
selectYear（记录时间）from记录;
3）Month（Date）返回日期中的月份值（1到12）
selectMonth（记录时间）from记录;
3、数值函数
1）Abs（n）返回n的绝对值
select记录数,abs（记录数）from记录;
2）Int（n）返回数字的整数部分
select记录数,int（记录数）from记录;
4、转换函数
1）Format（）将一个日期变体或一个数值转化成格式化字符串
select记录时间,format（记录时间,'yyyy/mm/dd'）from记录;
select记录时间,mid（format（记录时间,'yyyy/mm/dd'）,1,7）from记录;
select记录时间from记录wheremid（format（记录时间,'yyyy/mm/dd'）,1,7）='2005-11';
selectdistinctmid（format（记录时间,'yyyy/mm/dd'）,1,7）from记录;
2）Val（n）把字符串转换为数值值
select编号,val（编号）from记录;
3）CSng（）将表达式转换成字符
select记录数,csng（记录数）from记录;//数值转换字符
select记录时间,csng（记录时间）from记录;//日期转换字符
4）字符串处理函数
关键字
说明
示例
Ucase
将字符串中的字母改为大写
Ucase（"Kim"）返回KIM
Lcase（）
将字符串中的字母改为小写
Lcase（"Kim"）返回kim
Len（）
确定字符串的长度（以字符为单位）
Len（"Mississ"）返回7
Right（）
返回字符串右部指定个数的字符
Right（"Budapest",3）返回pes
Left（）
返回字符串左部指定个数的字符
Left（"Budapest",4）返回Buda
Mid（）
从某一指定起始点开始返回字符串中指定个数的字符
Mid（"Sommers",4,3）返回mer
trim（）
去除字符串前后空格
Rtrim（）
去除字符串前右边空格
Ltrim（）
去除字符串前左边空格
InStr（）
从一个较大字符串中查找另一个字符
start%=InStr（"bob","bobby"）start%变量返回值1
String（）
重复指定字符串中的字符，返回新的字符串
String（8,"*"）返回********
Asc（）
返回指定字母的ASCII代码
Asc（"A"）返回65
Xor（）
对两个数字执行“异或”操作，其返回值可用来对文本进行加密、解密
65Xor50返回115115Xor50返回65
5、函数min（）—求最小值
Selectmin（记录数）From记录;
Selectmin（记录数）From记录Where记录时间>=#2009-11-1#;
三、关键字
1、@@IDENTITY变量
@@IDENTITY变量是一个全局的SQL变量，用户可以用它来提取使用COUNTER数据类型的列的最后使用值。
用户在提取@@IDENTITY变量时，不能指定表的名称。
返回值总是最近的通过代码插入了数据的表的COUNTER域。
SELECT@@IDENTITY;
要给@@IDENTITY值加上某个值，要将该变量用方括号括起来。
SELECT[@@IDENTITY]+1;
注意上面SQL语句中的@@IDENTITY变量只能通过JetOLEDBprovider和ADO执行提取，通过AccessSQLView用户界面提取的值将是0，另外，该值只有在通过代码插入记录时才会改变，如果通过用户接口，不管是数据表单、窗体还是AccessSQLView窗口中的SQL语句，@@IDENTITY返回值都是0。
因此，@@IDENTITY的值只有在刚刚通过代码加入了记录是才是准确的。
2、DISTINCT关键字
DISTINCT关键字用来控制结果集中重复的值如何进行处理，那些对于指定的列来说用户相同值的行将被过滤掉。
如果多于指定的列大于一，则所有指定的列的结合将作为过滤条件。
例如，如果用户查询Customers表中姓氏不同的记录，则返回的值都将是唯一的，任何重复姓氏的名字都将以结果集中的一个记录作为其结果。
SELECTDISTINCT[LastName]FROMtblCustomers;
尤其要注意的是，使用DISTINCT关键字的查询所返回的结果集不能更新，即是只读的。
例：
selectdistinctIDfrom记录;
3、ORDERBY
例：
select*from记录ORDERBY编号;
4、IN操作
IN操作是用来判断一个表达式的值是否属于一个指定列表中的值。
如果这个表达式等于列表中的一个值，IN操作的返回值为True。
而当没有找到，IN操作返回值为False。
让我们假设我们想找到所有住在华盛顿州或乔治亚州的销售部成员。
我们可以写一个带着长长的WHERE子句，并使用AND逻辑操作符的SQL语句，而使用IN操作符会缩短我们写的语句。
SELECT*FROMtblShippingWHEREStateIN（'WA','GA'）;
例：
select*from记录where编号in（'06-03','06-04'）;
通过使用NOT逻辑操作符，我们可以检索出IN操作的反操作结果，这个语句会返回所有不住在华盛顿州的销售部成员。
SELECT*FROMtblShippingWHEREStateNOTIN（'WA'）;
例：
select*from记录where编号notin（'06-03','06-04'）;
5、BETWEEN操作
BETWEEN操作用于判断一个表达式的值是否介于一个特定的范围之间。
如果这个表达式的值介于这个特定范围之间，包括范围开始和结束的值，这个BETWEEN操作返回True。
如果这个表达式得值不属于这个范围，则BETWEEN操作返回值为False。
假设我们想找到所以金额介于50美圆到100美圆之间的所有发票。
我们最好在WHERE子句使用BETWEEN操作以及关键字AND设定范围。
SELECT*FROMtblInvoicesWHEREAmountBETWEEN50and100;
通过使用NOT逻辑操作符，我们可以检索出BETWEEN操作的反操作结果，找到不在范围中的所有发票数量。
SELECT*FROMtblInvoicesWHEREAmountNOTBETWEEN50and100;
注意这个范围可以设定为相反的顺序并依旧得到相同的结果（BETWEEN100和50），但许多的适用于ODBC的数据库要求这个范围遵从从头到尾的顺序。
如果你设计你的应用程序可以兼容或升级为适用于ODBC的数据库，你就应该总是按照从头到尾的方法使用。
6、LIKE操作
LIKE操作一个样式就是就是一个完全的字符串或是一个包含有一个或多个通配符的部分字符串。
通过使用LIKE操作，你可以在一个结果集里查找一个域并找到所有符合特殊的样式的值。
为了返回所有的名字以字母J开头的顾客，我们使用星号通配符。
SELECT*FROMtblCustomersWHERE[LastName]LIKE'J%';
例：
select*from记录where编号like'06%';
通过使用NOT逻辑操作符，我们可以检索出LIKE操作的反操作。
SELECT*FROMtblCustomersWHERE[LastName]NOTLIKE'Johnson';
例：
select*from记录where编号notlike'06%';
你在LIKE操作样式里可以使用多种通配符，如下表所示：
通配符
描述
%（百分号）-----匹配所有字符并可以被用在结构字符串的任何位置。
?
（问号）--匹配任何单个字符并可以被用在结构字符串的任何位置。
_（下划线）---匹配任何单个字符并可以被用在结构字符串的任何位置。
（只适用于ADO和theJetOLEDBprovider）
#（数字符号）---匹配任何单个数字并可以被用在结构字符串的任何位置。
[]（方括号）---匹配任何被包括在方括号里面的单个字符，并可以被用在结构字符串的任何位置。
!
（感叹号）---匹配任何不属于被方括号所包含的字符列表中的单个字符。
-（连字符）---匹配任何一个在方括号中的字符。
注意:
上表所述的“%”和“_”通配符只能通过JetOLEDBprovider或ADO运行。
如果通过.AccessSQLView用户界面运行它们将获得一个空的结果集。
7、ISNULL操作符
空值就是指无值或不可知值。
ISNULL操作符被用于判断一个表达式的值是否和一个空值相等。
SELECT*FROMtblInvoicesWHEREAmountISNULL
通过添加NOT逻辑操作符，我们可以检索ISNULL操作符的反操作。
在这个例子里，SQL语句将会除掉所有包含丢失的或未知值的发票记录。
SELECT*FROMtblInvoicesWHEREAmountISNOTNULL;
8、SELECTINTO语句
SELECTINTO语句，也可以理解为一个表单创建查询，可以用来从一个或多个已存在工作表中创建一个新的工作表。
它所创建的工作表可以基于任何有效的SELECT语句。
SELECTINTO语句可以用来存储记录、创建备份表单或在一个外部数据库里创建新的工作表。
当你用SELECTINTO语句创建一个新工作表时，所有的新工作表里的域都继承于原始工作表。
然而，不包括其他的工作表属性，如主关键字或索引都是在新工作表中被创建。
一旦新的工作表被创建，你当然可以使用ALTERTABLE语句添加这些属性。
如果你要创建一个新的工作表，可以使用一个带有你希望在工作表种包含的列的域列表和你新工作表的名称的SELECTINTO语句，并在FROM子句里提供数据资源。
SELECT*INTOtblNewCustomersFROMtblCustomers;
为了为新的工作表指定确定的域，把域名列表里的原始工作表的域名用星号代替，并用AS关键字来命名新的工作表中的各列。
SELECT[FirstName]&''&[LastName]ASFullNameINTOtblNewCustomerNames
FROMtblCustomers
如果要在一个已经存在的外部数据库里创建新的工作表，你可以用IN关键字。
如果外部数据库不存在，SELECTINTO语句将会返回一个错误信息。
SELECT*INTOtblNewCustomersIN'C:
\Customers.mdb'FROMtblCustomers;
四、其他查询
1、子查询
子查询就是在用在另一个SELECT、SELECTINTO、INSETTINTO、DELETE或UPDATE语句内部的SELECT语句。
它可以帮助你对基于另一个结果集的结果进行进一步的限制。
这叫做嵌入，并且因为一个子查询就是一个SELECT语句，你也可以把一个子查询嵌入到另一个子查询里面。
当你在一个SQL语句中使用一个子查询的时候，它可以作为一个域列表、WHERE子句或者HAVING子句的一部分。
这里由三种基本的子查询形式，并且每种都使用不同种类的谓词。
IN子查询
IN子查询用于检索这样的一组值，即其中记录的某一列的值都为另一个工作表或查询中的一列的值包含。
它从其它工作表中只能返回一列，这是一个限制条件。
如果返回的多于一列就会产生一个错误。
使用发票数据库例子，我们可以写出一个返回所有拥有发票的顾客的列表的SQL语句。
SELECT*FROMtblCustomersWHERECustomerID
IN（SELECTCustomerIDFROMtblInvoices）
通过使用NOT逻辑操作符，我们可以检索和ＩＮ子查询相反的记录，从而可以获得所有没有任何发票的顾客列表。
SELECT*FROMtblCustomersWHERECustomerID
NOTIN（SELECTCustomerIDFROMtblInvoices）
1）ANY/SOME/ALL子查询
ANY、SOME和ALL子查询谓词被用于比较主查询的记录和子查询的多个输出记录。
ANY和SOME谓词是同义词并可以被替换使用。
当你需要从主查询中检索任何符合在子查询中满足比较条件的记录时可以使用ANY或SOME谓词。
谓词应该恰好放在子查询开始的括号前面。
SELECT*FROMtblCustomersWHERECustomerID=ANY
（SELECTCustomerIDFROMtblInvoices）
注意由上面SQL语句所返回的结果集和IN子查询例子所返回的那个相同。
而与ANY和SOME谓词的不同之处就在于它们都可以使用等于（=）以外的操作符，比如大于（>）和小于（<）。
SELECT*FROMtblCustomersWHERECustomerID>ANY
（SELECTCustomerIDFROMtblInvoices）
当我们想在主查询中检索满足子查询比较条件的所有记录时使用谓词ALL。
SELECT*FROMtblCustomersWHERECustomerID>ALL
（SELECTCustomerIDFROMtblInvoices）
2）EXISTS子查询
EXISTS谓词是用于子查询来在一个结果集中检查所以存在值的记录。
换句话说，就是如果子查询没有返回任何行，这个比较就False。
而如果它返回了一行或多行，这个比较就为True。
SELECT*FROMtblCustomersASAWHEREEXISTS
（SELECT*FROMtblInvoicesWHEREA.CustomerID=tblInvoices.CustomerID）
注意在前面的SQL语句里，tblCustomers工作表使用了一个别名。
这就是为何我们可以在后来的子查询中引用它的原因。
当一个子查询以这种格式与一个主查询联接时就称相关查询。
通过使用NOT逻辑操作符，我们可以检索和EXISTS子查询相反的记录，从而可以得到所有没有任何发票的顾客的结果集。
SELECT*FROMtblCustomersASAWHERENOTEXISTS
2、连接
在一个如同Access的相关数据库系统中，你会常常需要同时从多个工作表中摘出信息记录。
这可以通过使用一个SQLJOIN语句来实现。
JOIN语句可以让你从已经定义了相互关系的工作表中检索记录，而不用管记录和工作表之间的关系是一对一、一对多还是多对多。
1）内部连接
内部连接，也被理解为对等连接，就是被使用的连接里最一般的形式。
这种连接通过匹配一个各个工作表中共有的域值来从两个或更多的工作表中检索记录。
你所连接的域必须具有类似的数据类型，但你就不能对MOMO和OLEOBJECT数据类型进行连接。
为了建立一个INNERJOIN语句，在SELECT语句的FROM子句里使用INNERJOIN关键字。
让我们使用INNERJOIN建立所有拥有发票的顾客的结果集，并带上那些发票的时间和金额。
SELECT[LastName],InvoiceDate,AmountFROMtblCustomersINNERJOINtblInvoices
ONtblCustomers.CustomerID=tblInvoices.CustomerIDORDERBYInvoiceDate
注意工作表名被INNERJOIN关键字所分开，并且相关的比较是在ON关键字的后面。
对于相关的比较，你也可以使用<、>、<=、>=或<>操作符，并且你也可以使用BETWEEN关键字。
同时注意各个工作表只在比较关系中使用的ID域，它们都不是最后结果集的组成。
如果要进一步的限制SELECT语句我们可以在ON子句中的比较连接后面使用WHERE子句。
在下面的例子中我们通过只包括1998年1月1日以后的发票来缩小结果集。
SELECT[LastName],InvoiceDate,AmountFROMtblCustomersINNERJOINtblInvoices
ONtblCustomers.CustomerID=tblInvoices.CustomerIDWHEREtblInvoices.InvoiceDate>#01/01/1998#ORDERBYInvoiceDate
在希望连接多个工作表的案例中，你可以嵌入INNERJOIN子句。
在这个例子里，我们将在过去的一个SELECT语句的基础上产生我们的结果集，但我们也将通过为tblShipping工作表添加INNERJOIN使结果包括每个顾客的所在城市和国家。
SELECT[LastName],InvoiceDate,Amount,City,StateFROM（tblCustomersINNERJOINtblInvoicesONtblCustomers.CustomerID=tblInvo
升级会员 