Background - assume dim monkey pounding on keyboard created this question. I have a wide but very shallow self taught IT background... :-)
I have a simple two table site.
What they need expressed in SQL is
SELECT
Allocations.AccountNumber,
Allocations.Amount
FROM Allocations
INNER JOIN Voucher ON Allocations.VoucherNumber = Voucher.VoucherNumber
GROUP BY Allocations.AccountNumber
WHERE Voucher.TransactionDate > "USER SUPPLIED VALUE" AND
Voucher.TransactionDate < "USER SUPPLIED VALUE";
I have created a view ->
SELECT
Allocations.AccountNumber,
Allocations.Amount,
Voucher.TransactionDate
FROM Allocations
INNER JOIN Voucher ON Allocations.VoucherNumber = Voucher.VoucherNumber
GROUP BY Allocations.AccountNumber
That creates a list and export page where they can specify the date range they want, and get the output they need, but it is split up one row for each transaction date. I need only one amount per account number.
I can't figure out the approach to do it.
If I remove the transaction date from the view, they can't search on it.
I did try a 'pivot table report' that partially works, with account number on one axis and transaction date month on the other, but there's no 'export' option on reports, and they may need better granularity than per month.
Sure would appreciate a pointer in the right direction...