如何组合3个表 [英] How to combine 3 tables

问题描述

我有三个表，tblAP，tblPayroll和tblAllocation。每个记录三个单独的费用。所有这三个都与FK ECHOID相关，每个都有共同的字段，如AccountNo，FundNo，GrantNo，SubClassNo，ProjectNo和Amount


最初我创建了这3个并且能够生成组合报告使用查询。但我无法使用这些查询并将其绑定以创建一个报告，该报告将并排显示收入，费用和余额。我遇到了外部连接模糊问题，我不知道如何解决。


我想知道我是否可以创建一个名为tblGrantExpenses的第4个表，它将自动合并这些费用表格可能允许我使用tblGrantBudget（收入）和tblGrantExpenses（费用）创建报告。


有人可以给我一些指导吗？非常感谢。

I have three tables, tblAP, tblPayroll and tblAllocation. Each records three separate expenses. All three are related by FK ECHOID, each has common fields such as AccountNo, FundNo, GrantNo, SubClassNo, ProjectNo and Amount

Originally I created these 3 and was able to generate combined reports using queries. But I have been unable to use these queries and tie it to create a report that will show Revenue, Expenses and balance side by side. I am running into outer join ambiguity problem which I don''t know how to solve.

I was wondering if I can create a 4th table called tblGrantExpenses that will automatically combine expenses from these tables perhaps it will allow me to create a report using tblGrantBudget (for revenue) and tblGrantExpenses (for expenses).

Can someone please give me some guidence? Many thanks.

推荐答案

所有三个表都有收入，费用和余额字段吗？


关于什么的具体细节你想要完成的确会有所帮助：

报告所需的确切字段名称

这些字段所在的表格（它们都是全部的吗？ ）


你能描述一下你想要的结果吗？我不理解''并排''你提到

do all three tables have Revenue, Expenses and balance fields in them?

any specifics on what you are trying to accomplish would really help like:
which exact field names you are needing for the report
which tables do those fields reside in (are they all of them?)

and can you describe the result your looking for a bit more? I''m not understanding the ''side by side'' you mentioned

只要您的FK ECHOID字段出现在所有三个表中，您应该能够进行多次连接你喜欢。也许您可以发布工作代码的示例以及它开始给您带来麻烦的示例吗？

as long as your FK ECHOID field is present in all three tables you should be able to do as many joins as you like. Perhaps can you post an example of the working code and an example of where it began giving you trouble?

Yarborough40：


感谢您回复我的问题。


样本数据

tblAp

ApID - PK（自动） - 1

VendorID - 数字 - 1（从tblVendor提取数据）

InvoiceDt - 日期/时间 - 2009年6月7日

InvoiceNo - 文本 - 002765

AccountID - 数字 - 5821（从tblAccounts提取数据）

GrantFundID - 数字 - 872（从tblGrantFund提取数据）

GrantNo - 文本 - MN900242（自动填写一次）选择GrantFundID

SubClassID - 数字 - 0999（从tblSubClass中提取数据

ProjectID - 数字 - 63740 9（从tblProjects中提取数据）

ApAmount - 货币 -

Yarborough40:

Thanks for responding to my questions.

Sample data
tblAp
ApID - PK (auto) - 1
VendorID - Number - 1 (Pulling data from tblVendor)
InvoiceDt - Date/Time - 6/7/09
InvoiceNo - Text - 002765
AccountID - Number - 5821 (pulling data from tblAccounts)
GrantFundID - Number - 872 (pulling data from tblGrantFund)
GrantNo - Text - MN900242 (automatically gets filled in once GrantFundID is selected
SubClassID - Number - 0999 (pulling data from tblSubClass
ProjectID - Number - 63740 9 (Pulling data from tblProjects)
ApAmount - Currency -

这篇关于如何组合3个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！