MS Access 2003 - 将多个表的最后一条记录合并到一个查询或表中？

发布于 2024-08-23 14:29:51 字数 891 浏览 6 评论 0原文

我有几个表是交易表，我想为比较余额制作一个简单的数据透视图......这恰好是每个表在名为“余额”的字段中的最后一条记录。

所以我知道如何使用 SQL 语句 rs.movelast 将其填充到表单上，但我不知道如何在不将其放入表中的情况下访问数据透视图...

谢谢！

编辑：

这就是我用的！谢谢雷穆！

(SELECT TOP 1 TransactionID, Balance  
FROM tblTrans001
ORDER BY TransctionID  DESC) 
UNION  
(SELECT TOP 1 TransactionID,  Balance  
FROM tblTransaction02 
ORDER BY TransactionID DESC) 
UNION
(SELECT TOP 1 TransactionID, Balance
FROM Tranaction03
ORDER BY TransID DESC)

现在我只需要找到一种方法将文本字符串插入到相应的字段中，以标识该值来自哪个表。

例如，上面的查询返回

TransID     Balance
123         $1000.00
234         $20000.00
345         $300000.00

，我需要：

TransID     Balance        Table/Account
123         $1000.00       tblTransaction01
234         $20000.00      tblTransaction02
345         $300000.00     tblTransaction03

谢谢！

原文

I have a couple of tables that are transaction tables, and I would like to make a simple pivot chart for comparative balances....which happen to be the last record of each of these tables in a field called "balance".

so i know how to populate this on a form using a SQL statement, rs.movelast, but i do not know how to get to the pivot chart without having this into a table....

thanks!

EDIT:

This is what I used! Thanks Remou!

(SELECT TOP 1 TransactionID, Balance  
FROM tblTrans001
ORDER BY TransctionID  DESC) 
UNION  
(SELECT TOP 1 TransactionID,  Balance  
FROM tblTransaction02 
ORDER BY TransactionID DESC) 
UNION
(SELECT TOP 1 TransactionID, Balance
FROM Tranaction03
ORDER BY TransID DESC)

Now I just need to find a way to insert a text string into the corresponding fields that identifies what table the value is coming from.

for example, the above query returns

TransID     Balance
123         $1000.00
234         $20000.00
345         $300000.00

and I need:

TransID     Balance        Table/Account
123         $1000.00       tblTransaction01
234         $20000.00      tblTransaction02
345         $300000.00     tblTransaction03

thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

紧拥背影 2024-08-30 14:29:51

最后一条记录的定义是什么？假设它是创建日期，并且创建日期是唯一的，那么您可以使用下面的 SQL。请注意，括号很重要。

(SELECT TOP 1 CrDate , Balance , "TranA" As FromTable
FROM  TransactionsA
ORDER BY  CrDate DESC)
UNION 
(SELECT TOP 1 CrDate , Balance , "TranB" As FromTable
FROM  TransactionsB 
ORDER BY  CrDate DESC)

What do you define last record? Let us say it is the date created and that the date created is unique, then you could use the SQL below. Note that the parentheses are important.

(SELECT TOP 1 CrDate , Balance , "TranA" As FromTable
FROM  TransactionsA
ORDER BY  CrDate DESC)
UNION 
(SELECT TOP 1 CrDate , Balance , "TranB" As FromTable
FROM  TransactionsB 
ORDER BY  CrDate DESC)

回复收藏 0 原文

~没有更多了~