How To Summarize multiple name into one where the date is equal to now

[Lieutenant_1]

Hello , i am using vb6.0 can some one help me with my problem. I want to summarize a database with multiple name and display it into one data where the date is same today. thank you for any one would response.

for example. below is a table where multiple names created.

In database

Name            Total Payment           Date
lius--             100---               07/01/22
lius--             100---               07/01/22
lius--             50--                 07/01/22 
era--              60--                 07/03/22
era--              50--                 07/03/22

So when it executed in listview form it would look like on the below sample

Name            Total Payment           Date
lius--             250--                07/01/22
era--              110--                07/03/22

Here is my code for executing my database in listview form

Set rxb = New ADODB.Recordset
rxb.Open "Select * from History order by trn", con, 3, 3

with rxb
    On Error Resume Next
    Do While Not .EOF
        ListView2.ListItems.Add , , !TRN, 1, 1
        ListView2.ListItems(ListView2.ListItems.count).SubItems(1) = "" & !Emp
        ListView2.ListItems(ListView2.ListItems.count).SubItems(3) = "" & !hdate
        ListView2.ListItems(ListView2.ListItems.count).SubItems(4) = "" & !htime
        ListView2.ListItems(ListView2.ListItems.count).SubItems(5) = "" & !purchases
        ListView2.ListItems(ListView2.ListItems.count).SubItems(6) = "" & !payment
        ListView2.ListItems(ListView2.ListItems.count).SubItems(7) = "" & !rBalance
        ListView2.ListItems(ListView2.ListItems.count).SubItems(8) = "" & !adjustment
        ListView2.ListItems(ListView2.ListItems.count).SubItems(9) = "" & !Pm
        ListView2.ListItems(ListView2.ListItems.count).SubItems(10) = "" & !Cashier
        .MoveNext
    Loop
    .Close
End With

[Lieutenant_1]

Also forgot to mention. and also the total amount would sum total.

[rproffitt]

If you wish to sum() then sum(). See https://www.w3schools.com/sql/sql_count_avg_sum.asp I would NOT write code to iterate over the list and sum it up.

[Lieutenant_1]

Thank you both for the response, however i think it'll work but i cant figure it out how to properly use the code because i am using MS access as my Database.

[rproffitt]

Please supply such details up front or as soon as possible. There's a big issue here is that VB6 is dead and gone. My memory of it is fading fast since we end of life'd the last apps 2 years ago. Yes we were open to keep going but the client thought since it was old the cost for support should be discounted. Sorry, no, just like petrol, the costs have gone up.

I can't guess why you are working on VB6 but my advice is it's over.

As to the Access, you can use SQL statements with Access as the database. It's been capable for decades.

-> Maybe you don't want guidance but are looking for code or the solution written for you? There's outsourcing like Fiverr and such for that.

[Lieutenant_1]

Thank you for the advise rproffit, i think i found a way how to manipulate it.
ill try to open 2 tables from my database called "Employee" as my primary database for the encoded employee and "History" where all the payments transactions saved. then compare them to sum the amount of their transactions then add it to another listview. maybe i'll get back to you guys. thank you very much.

[Reverend Jim]

is there a way how to code it when i am using ms acess as my database

Except for minor differences in syntax, the only thing that should change is the connection string. Depending on what you named the date field you may need to surround the field name in single quotes or square brackets (date may be a reserved word). Unless you are required to use msaccess I'd avoid it in favour of something like sqlite. You can always get the free sqlite spy (GUI front end for management) here.

[Lieutenant_1]

Hello Guys, i found a way how to sort date using Query Display in access
here is my code

SELECT History.Emp, History.eName, Sum(History.payment) AS payment, Sum(History.Purchases) AS Purchases, Last(History.rBalance) AS rBalance, History.hDate 
FROM History 
GROUP BY History.Emp, History.eName, History.hDate ORDER BY History.hDate;

However, it'll only sort the date day by day. so i try to format date code below

SELECT History.Emp, History.eName, History.payment, Sum(History.Purchases) AS Purchases, Last(History.rBalance) AS rBalance, Format([hDate],"mm\,yyyy") AS expr1 FROM History GROUP BY History.Emp, History.eName, History.payment, Format([hDate],"mm\,yyyy")

however i am not sure if it'll work because of the hdate code. and i got some error saying "Expected: End of Statement" on "mm\,yyyy".

can you help me fix it ? or i am doing it in a wrong way?

[pritaeas]

If Format() doesn't work, you might be able to use the functions YEAR(hDate) and MONTH(hDate) to get what you want.

[Reverend Jim]

try

SELECT Name, SUM(TotalPayment) AS Total, Date
  FROM mytable
  GROUP BY Date

[Reverend Jim]

it'll only result the same

In order to avoid ambiguity please show us exactly what you want the query to return based on the data that you previously posted. In your first post your table had only three columns. Your last query had six. We can't give a complete answer based on incomplete information.

[Schol-R-LEA]

If you don't mind me asking, how is it that you are writing new VB 6.0 code in 2022? I would strongly advise against trying to beat that dead horse any further, if you have any choice in the matter.

[Lieutenant_1]

Hello All,

Thank you for all your help. I do find some tips from a friend and now done.

Here is what i did,

I have 11 field in my database that had to be shown in listview. so then i did this,

to sort, i try to select query as specific to sort it all.

To sort it day by day:
"rxb.Open "SELECT Emp, eName, Sum(payment) AS payment, Sum(Purchases) AS Purchases,Sum(Adjustment) as Adjustment, Last(rBalance) AS rBalance, hDate FROM History GROUP BY Emp, eName, hdate ORDER BY hDate;", con, 3, 3"

Here to sort Month and Year, i add some field that will only save Month number and Year.
Daten for Month and hYear for only save the year.

To sort it Month by month:
"rxc.Open "SELECT Emp, eName, Sum(payment) AS payment, Sum(Purchases) AS Purchases, Sum(Adjustment) as adjustment, Last(rBalance) AS rBalance,Daten FROM History GROUP BY Emp, eName, Daten ORDER BY Daten DESC;", con, 3, 3"

To sort it Year by Year:
"rxc.Open "SELECT Emp, eName, Sum(payment) AS payment, Sum(Purchases) AS Purchases, Sum(Adjustment) as adjustment, Last(rBalance) AS rBalance, hYear FROM History GROUP BY Emp, eName, hYear ORDER BY hYear DESC;", con, 3, 3"