链接Excel数据以附加ACCESS表 [英] Link Excel Data to append ACCESS table

问题描述

我正在研究数据库以记录与各种拨款相关的费用。要记录工资核算费用，我使用以下字段创建了 tblPayroll ：

展开 | 选择 | Wrap | 行号

解决方案

我没有链接到许多Excel工作表，但这似乎是一个合理的路径。我将创建一个表单，其中包含需要由用户填写的相关字段，然后更新查询将能够使用来自excel的信息并更新Access表。

我相信将包含以下字段。我可能会包含excel电子表格中的数据，以便您查看它。

展开 | 选择 | 换行 | 行号

< blockquote> Denburt：


当你说：

$ b $时，我不太明白怎么做b我将创建一个表单，其中包含需要由用户填写的相关字段，然后更新查询将能够使用来自excel的信息并更新Access表。

输入工资核算数据不涉及手动数据输入。因为它是一个从服务器转储的大文件（我们使用会计软件PeopleSoft）。你提到的是什么形式？


tblPayroll有GrantFundID取代了FundID。 Excel文件有基金号码，我如何更新tblPayroll以便它可以识别基金号码并自动关联与该基金号码相关联的拨款号码？


当人们获得报酬时，tblPayroll中的这些数据必须每两周更新一次。


这对我来说有点复杂。你可以给我一步一步的指导吗？


谢谢。

从工资表中的链接开始，为了此示例名称链接表sheet1（因此以下查询将起作用）。在设计视图中从查询开始，可以单击菜单栏上的查询按钮并更改所需的查询类型。由于我有你的数据库的副本，我帮你了一点。启动一个新查询并在SQL视图中查看它，然后将以下内容粘贴到它中，然后在数据表或设计视图中查看它，然后在运行查询时，它应该添加您在数据表视图中看到的记录。我想一旦你这样做，你就会有更好的理解。


我还想指出你没有使用这些表的任何属性，如果你退后一步，我会告诉你这个字幕使用查找外键等，然后在设计表单时会有所帮助。

展开 | 选择 | Wrap | 行号

I am working on a DB to record expenses related to various grants. To record Payroll expenses I created tblPayroll with these fields:

Expand|Select|Wrap|Line Numbers

解决方案

I haven''t linked to many Excel sheets but this seems like a logical path to take. I would create a form that had the pertinent fields that needs to be filled in by the user then the update query will be able to take that with the information from excel and update the Access table.
I believe the would contain the following fields. I would probably include the data from the excel spreadsheet just so you can review it.

Expand|Select|Wrap|Line Numbers

Denburt:

I didn''t quite understand how to do this when you say:

I would create a form that had the pertinent fields that needs to be filled in by the user then the update query will be able to take that with the information from excel and update the Access table.

There is no manual data entry involved to input the payroll data. Because it''s a large file that gets dumped from the server (we use accounting software PeopleSoft). What form you are refering to?

tblPayroll has GrantFundID that replaced FundID. Excel file has Fund numbers, how am I going to update tblPayroll so that it can identify the Fund number and automatically associate the Grant Number that''s associated with that fund number?

This data in tblPayroll will have to be updated every two weeks as people get paid.

This is a little complex for me. Can you please give me the step by step guide to it?

Thanks.

Start with linking in the payroll sheet and for the purpose of this example name that linked table sheet1 (so the following query will work). Start with the query when you are in design view you can click on the query button on the menu bar and change the type of query you want. Since I have a copy of your DB I helped you out a little. Start a new query and view it in SQL View, then paste the following into it and it and then view it in Datasheet or design view look it over then when you run the query it should add the records you see in Datasheet view. I think once you do that you will have a better understanding.

I also wanted to point out that you aren''t using many if any of the properties for these tables and I''ll tell you this, if you go step back and add the captions use the look up for your foreign keys etc. then it will help when you design your forms.

Expand|Select|Wrap|Line Numbers

这篇关于链接Excel数据以附加ACCESS表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！