In RMS 2.0 I would like to create a view over the TenderEntry and Transaction table that shows me total tender amounts by tender type and transaction date. I would like to use this view to import the totals directly into an Excel.
I can get all the details but can figure out what to do to get the totals.
Any help is greatly appreciated.
You will need to aggregate the values and use a GROUP BY Clause to specify grouping. This is pure SQL stuff, not specific to RMS, so you should be able to find plenty of documentation...
a simple case would be:
Select [Tender].Code, SUM(TenderEntry.Amount) From Tender Inner Join TenderEntry on Tender.ID = TenderEntry.TenderID GROUP BY Tender.Code
My problem is that the TenderEntry table needs to be linked to the transaction table to get the transaction date. The question there is how to aggregate the amount by tender type.
And for my Excel import I already run a SQL statement for the daily sales details. I need to change that to a VB script that imports the daily sales details AND after that the tender type totals. This was the reason for wanting to create a view.
I'm a bit of a newbie with sql and had to research grouping by a datetime field - it was a little tricky, but try this and see if you can work with this result - if you can, use it on your view creation (create view as....) - hope this helps
select YEAR(time) AS year, M> I need the Transaction Date, TenderEntry.Description, and the total of
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.