• Registered: 2020-03-13
  • Posts: 19

Topic: Manipulating tables data

Hello everybody.

I'm coding noob, still learning. I was searching forums but can't make it work. I'm trying to make vehicle tracking database.
I have two tables: vehicles and fuel_fillings. In table fuel_fillings there is relationship field with table vehicles. One of the fields also is monthly_qty of fuel filled. What I want to to do is, for example, in from1 table grid1 I want to show summary for all vehicles where one of the column is cumulative avg fuel filings (vehicle1 - fuel qty month1 + month2..../number of months) - Cumulative AVG for all fuel qty  per vehicle. Basically what I'm trying to do is to show footer(AVG) for field monthly_qty for each vehicle in summary tablegrid1.
Is it possible to do it with calculated field or some other way?
Also, it would be great if someone know  if there is some link where I can learn about tables data manipulation.

Thanks all of you for helping me.

Stay well

amadeus.sync's Website

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,675

Re: Manipulating tables data

Hi,
Can you upload your project?
Then someone could have a look with some suggestions,
Derek

3 Reply by amadeus.sync (edited by amadeus.sync 2020-03-28 14:32:16)

  • Registered: 2020-03-13
  • Posts: 19

Re: Manipulating tables data

Here it is. It' in Serbian, pls ask if there is some explanation needed. You'll see I tried in Form1, colum cumulative avg, but that's not what I wanted.

Post's attachments

Attachment icon vehicles_database.rar 302.34 kb, 204 downloads since 2020-03-28 

amadeus.sync's Website

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,675

Re: Manipulating tables data

Hello Amadeus,
I'm not sure if this is the sort of thing you are after (please see the attached).
Rather than change your tablegrid, I have just used a second 'summary' grid and put a number of calculated fields in there.
Because it is a 'summary', I am displaying columns to 0 decimal places so there may be slight rounding differences (but you can always amend the calculations to give better accuracy if you need).
Hope this helps,
Derek.

Post's attachments

Attachment icon vehicles_database - Copy.zip 349.62 kb, 211 downloads since 2020-03-28 

  • Registered: 2020-03-13
  • Posts: 19

Re: Manipulating tables data

derek wrote:

Hello Amadeus,
I'm not sure if this is the sort of thing you are after (please see the attached).
Rather than change your tablegrid, I have just used a second 'summary' grid and put a number of calculated fields in there.
Because it is a 'summary', I am displaying columns to 0 decimal places so there may be slight rounding differences (but you can always amend the calculations to give better accuracy if you need).
Hope this helps,
Derek.

This is better than I asked for. Now I have examples for all calculations I may need. Great!

Thank you very much.

amadeus.sync's Website

  • Registered: 2020-03-13
  • Posts: 19

Re: Manipulating tables data

derek wrote:

Hello Amadeus,
I'm not sure if this is the sort of thing you are after (please see the attached).
Rather than change your tablegrid, I have just used a second 'summary' grid and put a number of calculated fields in there.
Because it is a 'summary', I am displaying columns to 0 decimal places so there may be slight rounding differences (but you can always amend the calculations to give better accuracy if you need).
Hope this helps,
Derek.

Hi Derek,

I have one more problem.  I need calculated field which will take MAX from filed 'stanje_km_sata' - table 'servis' but only if field 'options' from table 'service_types' = 'Редован сервис'.  I tried somethink like this but it's not correct:

(select max(stanje_km_sata) from servis where servis.id_vozila = vozila.id and servis.id_service_types like 'Редован сервис')

I'll attach project and thanks in advance.

Post's attachments

Attachment icon vehicles_database.rar 303.69 kb, 207 downloads since 2020-03-29 

amadeus.sync's Website

7 Reply by derek (edited by derek 2020-03-29 01:17:31)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,675

Re: Manipulating tables data

Hi,
If I understand your project correctly, your calculated field to show the max(stanje_km_sata) should be in table 'vozila' and not in table 'servis'.
It's also perhaps slightly more efficient to use the servis.id_service_types = 1 rather than to read the service_types table and select where type = 'Редован сервис'.
Regards,
Derek.

Post's attachments

Attachment icon vehicles_database - Copy (2).zip 350.84 kb, 240 downloads since 2020-03-29 

8 Reply by amadeus.sync (edited by amadeus.sync 2020-03-29 09:27:49)

  • Registered: 2020-03-13
  • Posts: 19

Re: Manipulating tables data

derek wrote:

Hi,
If I understand your project correctly, your calculated field to show the max(stanje_km_sata) should be in table 'vozila' and not in table 'servis'.
It's also perhaps slightly more efficient to use the servis.id_service_types = 1 rather than to read the service_types table and select where type = 'Редован сервис'.
Regards,
Derek.

Yea, that's it. You are great, thank you very much for help.

amadeus.sync's Website