I wish to make navigational summary table by joining different tables:
Just brief you about the table (User ID will be unique):
: here we are having a table where user can load amount in his prepaid instrument and consume this amount, consumption amount will never more than load amount.
(I had limited the data with semi-colon for illustration)
User ID; Load Amount
123; ₹ 2,000.00
345; ₹ 1,000.00
: Here user will use the load amount in multiple number of times, e.g User 123 had loaded his prepaid instrument with Rs. 2K and now can use this amount in multilpe transaction (Rs. 1000 + Rs. 500) or in one go.
User ID; Consumption amount
123; ₹ 1,000.00
123; ₹ 500.00
Summary of Load & Purchase
: now I want to have summary in below format
Summary of Load and Purchase
User ID; Load Amount; Consumption amount; Unused amount
123; ₹ 2,000.00; ₹ 1,500.00; ₹ 500.00
345; ₹ 1,000.00; ₹ 0.00; ₹ 1,000.00
But what Im getting after making relationship and running the query:
- User 123 had loaded ₹ 4,000 (it might be because user had consumed its load in two transactions hence access added 2K +2K. i'm getting this calculation across my summary table.
- User 345 is not reflecting in summary table as it had not made any purchase. In my summary table only those users are reflecting who had made atleast one purchase transaction. But I want history of Users like user 345
Aside this I also want to make navigation fields where employee can enter the User ID and User's total load and total consumption history appears and employee may download this history in excel by clicking the button only( Don't know if it is possible! )