Query without running a Query
Quote from m.burdess on July 30, 2023, 8:12 pmIs it possible to have a query that tells me the number of record that meet it, and still shows all record.
If I have a record with Towns in it, it will tell me the number of records with that town.
Plymouth 10
London 12
Durham 19
yours
Mike
Is it possible to have a query that tells me the number of record that meet it, and still shows all record.
If I have a record with Towns in it, it will tell me the number of records with that town.
Plymouth 10
London 12
Durham 19
yours
Mike
Quote from susan on July 31, 2023, 8:18 amI am not an SQL expert, and am new to VisualNEO Win, so someone might have a proper answer, but I don't think it is possible as a single query. I imagine you can do something this,
SELECT COUNT(Towns) FROM mytable WHERE Towns = 'Plymouth';
But put it within a loop where you run through all the towns (which you have got from a previous query) in a variable, and you keep track of the results for each town?
I am not an SQL expert, and am new to VisualNEO Win, so someone might have a proper answer, but I don't think it is possible as a single query. I imagine you can do something this,
SELECT COUNT(Towns) FROM mytable WHERE Towns = 'Plymouth';
But put it within a loop where you run through all the towns (which you have got from a previous query) in a variable, and you keep track of the results for each town?
Quote from Vadim on July 31, 2023, 8:21 am@m-burdess
Good day!!! You can use the dbpQuery command of the NeoDBpro plugin (SinLios), the number of records found (matching the query) will be in the variable [ID.Table.$RecCount]
https://neodbprohelp.visualneo.com/SearchQuery.html
https://neodbprohelp.visualneo.com/SpecialVariables.html
Good day!!! You can use the dbpQuery command of the NeoDBpro plugin (SinLios), the number of records found (matching the query) will be in the variable [ID.Table.$RecCount]
https://neodbprohelp.visualneo.com/SearchQuery.html
https://neodbprohelp.visualneo.com/SpecialVariables.html
Quote from Gaev on July 31, 2023, 4:07 pm@m-burdess
tells me the number of record that meet it, and still shows all record
Not sure what you mean by "shows all record" ...
- all fields for all Town records associated with a City record ?
or
- all fields within each City record ?
Perhaps you can post a mock-up of the grid rows/columns you had in mind.
As mentioned by @susan, "I don't think it is possible as a single query" (although there might be a way using multiple advanced SQL commands) ... but you could show 2 grids ...
- one with all the Town records (sorted by City and Town)
- another with just one City record, with a count ... this web page ... https://www.w3resource.com/sql/aggregate-functions/count-with-group-by.php ... will give you an idea for doing this 'count grid'.
tells me the number of record that meet it, and still shows all record
Not sure what you mean by "shows all record" ...
- all fields for all Town records associated with a City record ?
or
- all fields within each City record ?
Perhaps you can post a mock-up of the grid rows/columns you had in mind.
As mentioned by @susan, "I don't think it is possible as a single query" (although there might be a way using multiple advanced SQL commands) ... but you could show 2 grids ...
- one with all the Town records (sorted by City and Town)
- another with just one City record, with a count ... this web page ... https://www.w3resource.com/sql/aggregate-functions/count-with-group-by.php ... will give you an idea for doing this 'count grid'.
Quote from m.burdess on July 31, 2023, 8:41 pm@vadim
I have tried the dbpQuery command of the NeoDBpro plugin, but this comes out with, first a count of all records and then when the query is run, the correct number is shown. Also if you have a list below the input section of the screen, so you can check if this record has already be entered, you can only see the record relating to the last query that was run.
@susan and @gaev
I have been looking at SQL today, and this look's good. I am left with one question, How would you show a place that does not have a record within the database yet? This is best explained using a census Database, this is taken every ten years from 1841 to 1921 (this is what is available to date), so if you do not have a record for 1871, it will not be listed but, this information is needed, even if it's zero records.
I have tried the dbpQuery command of the NeoDBpro plugin, but this comes out with, first a count of all records and then when the query is run, the correct number is shown. Also if you have a list below the input section of the screen, so you can check if this record has already be entered, you can only see the record relating to the last query that was run.
I have been looking at SQL today, and this look's good. I am left with one question, How would you show a place that does not have a record within the database yet? This is best explained using a census Database, this is taken every ten years from 1841 to 1921 (this is what is available to date), so if you do not have a record for 1871, it will not be listed but, this information is needed, even if it's zero records.
Quote from Gaev on July 31, 2023, 9:48 pm@m-burdess
How would you show a place that does not have a record within the database yet? This is best explained using a census Database, this is taken every ten years from 1841 to 1921 (this is what is available to date), so if you do not have a record for 1871, it will not be listed but, this information is needed, even if it's zero records.
As mentioned before, it is very confusing to understand what you are asking without knowledge of the detailed (relevant field) information in the records, as well as a mockup of the desired output.
I have no idea where the year fits into all this.
P.S. If there is no record for a particular year, no SQL command can be made to count/report it unless you first insert an empty record.
How would you show a place that does not have a record within the database yet? This is best explained using a census Database, this is taken every ten years from 1841 to 1921 (this is what is available to date), so if you do not have a record for 1871, it will not be listed but, this information is needed, even if it's zero records.
As mentioned before, it is very confusing to understand what you are asking without knowledge of the detailed (relevant field) information in the records, as well as a mockup of the desired output.
I have no idea where the year fits into all this.
P.S. If there is no record for a particular year, no SQL command can be made to count/report it unless you first insert an empty record.
Quote from m.burdess on July 31, 2023, 11:24 pm@gaev
Sorry for not making it clear, but I work with the information and what I think is clear may not always be so to others. I think I will leave this problem for later, and work on the system layout.
Sorry for not making it clear, but I work with the information and what I think is clear may not always be so to others. I think I will leave this problem for later, and work on the system layout.
Quote from ebear on August 2, 2023, 8:44 amHello,
Please have a look into the Database documentation you use and search for GROUP and SUM.
So first you group all the Town names with will give you a view of all available Towns after this look into SUM to give you for each town the sum of records with the Town name in it.
Best regards,
Eric
Hello,
Please have a look into the Database documentation you use and search for GROUP and SUM.
So first you group all the Town names with will give you a view of all available Towns after this look into SUM to give you for each town the sum of records with the Town name in it.
Best regards,
Eric