You are not logged in. Please login or register.
My Visual Database → General → filter on tablegrid
i have a tablegrid with several rows of entries. each row consists of a name and several columns of figures. the same name will appear in several rows with different data in the columns. the rows consist of more than one name and data. how can i show only the latest occurrence of each name and data?
Please attach your project.
Hello lhimes,
Assuming you have a table called test with 4 fields like : name, fig1, fig2, fig3, if you want to get just one line for each 'name' with the newest data (one, so the one with the biggest id available for each DISTINCT name), you can do something like :
SELECT
name,
fig1,
fig2,
fig3
FROM
test
GROUP BY
name
HAVING
MAX(id)Cheers
Mathias
hello Dmitry, Mathias
Dmitry,
as noted before you dont understand my project so to include the whole thing would probably be fruitless. i do include a screen shot of the form i have questions about so you can get an idea of what i am talking about. as you can see, names appear several times down the list. i want to clean the list up and show only the latest occurrence of a name.
Mathias,
is that a line i would put in the filter area of the tablegrid settings?
thanks for your help
lee
Query1.zip 72.53 kb, 476 downloads since 2016-11-09
hello Dmitry, Mathias
Dmitry,
as noted before you dont understand my project so to include the whole thing would probably be fruitless. i do include a screen shot of the form i have questions about so you can get an idea of what i am talking about. as you can see, names appear several times down the list. i want to clean the list up and show only the latest occurrence of a name.
Mathias,
is that a line i would put in the filter area of the tablegrid settings?
thanks for your help
lee
Please make simple test project for this case.
Hello,
No, you can not use the standard tablegrid for your need.
You can either populate manually the tablegrid (with a script), or use the SQLQuery button.
Attached is a small project with sample data. The first tablegrid automatically filled with all the records, the second is filled by the button when you click on it, and the query is "behind" the button.
Have a look in the object inspector to see how to use the button.
have a good day
Cheers
Mathias
MAX_ID.zip 334.91 kb, 464 downloads since 2016-11-10
Dmitry,
this is sample. i want only the names with the highest acctot to show on the grid
testcase.zip 5.88 kb, 432 downloads since 2016-11-12
Dmitry,
this is sample. i want only the names with the highest acctot to show on the grid
Check out this SQL query
SELECT
name,
(SELECT p.r1 FROM player p WHERE p.name=player.name ORDER BY p.id DESC LIMIT 1),
(SELECT p.r2 FROM player p WHERE p.name=player.name ORDER BY p.id DESC LIMIT 1),
(SELECT p.r3 FROM player p WHERE p.name=player.name ORDER BY p.id DESC LIMIT 1),
(SELECT p.r4 FROM player p WHERE p.name=player.name ORDER BY p.id DESC LIMIT 1)
FROM player GROUP BY nameBut actually you have wrong database structure for this case (you need two table, for players and for scores)
Here you can find info about First Normal Form
https://www.youtube.com/watch?v=NScuEk7CSNo
Lee, In Dimitry's defense, MVD is a RAD tool to quickly put together a basic application without doing any coding. You really can do that. I have done it myself. However, from my memory of your project, you are trying to develop a rather complex application with a unique design for your first project. There is no tool out there that can create an application design of this nature without some sort of coding or scripting. If you had to develop an application from scratch using the C programming language, you would be coming back to MVD in a heartbeat. Having said all this, if a person has some experience in application and SQL database design and with some skills in programming, you can create some rather complex applications with MVD. I knew nothing about Pascal when I started with MVD a year ago, but I have experience in other programming languages which helped in learning it. Since you feel you do not want to learn another development language, I suggest that you reach out to this forum to see if anybody would develop the project you want. You may have to pay for it, but you'll get what you want. Somebody with MVD experience could possibly develop it in a rather short time frame. Maybe Derek could help you put it together, if he has time, since he is somewhat familiar with your project already.
Don't be upset with Dimitry. What he says on the website is not false advertising.
mr Ehwagner,
actually my project wasnt all that complicated. i think everyone got scared when they saw the words billiards and 8 ball. you could just as well put in the word soccer or just about any other sport. all of the statistics are just basic math. you could have said how many times a player handled the ball, how many times he passed it and how many time he made a goal instead of how many rounds he won or lost, or what is total score for the night was. i did in fact many months ago ask about someone doing the project for me and got no response. about 30 years ago i majored in computer science and the teaching language at that time was pascal. i loved it then. havent used it at all since and dont remember a bit of it. i was going to do this in visual basic and microsofts database but saw that it was going to take learning sql for most of it. i chose mvd so that i wouldnt have to learn a new language and yet here i am.
Hello Ihimes,
I'm glad you are back because your project kind of interested me and I was sorry to see you go in such a state of mind. Maybe "I'd like to do that" instead of "I want" or "you don't understand my project" could help a little if I may add but, that said, there is almost always a solution to a problem with MVD.
As ehwagner mentioned, you can do pretty much anything with MVD (I scrap a web API to get text and images and store them in a database) but for some specific needs the built in function in MVD are not enough and you have to put your hand "under the hood" (not sure this last expression is fully English but anyway...).
The initial question in your post was about getting all last records per person. Whatever the number of records for x players, you wanted to retrieve only the more recent. There is no ready to use filter (yet) for that, but a simple search query button could have done the trick (like I showed in my example) : the is no code requiered other than a SQL query with MAX(id). Or you could had a field in your records with the date it was registered and query only the newest. Or a Boolean tag set to true for the newest record and set to false for all the others when a new one is recorded and then the regular tools could be used with just a filter on the "last_record = True" if we want to call it that.... countless easy solutions, all depending on were you are going with your app.
Don't give up, it's easy and... fun. And many people are whiling to help you.
Talk to you soon
Mathias
My Visual Database → General → filter on tablegrid
Powered by PunBB, supported by Informer Technologies, Inc.
Theme Hydrogen by Kushi