OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] Global repetative SQL Command Without Macros?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
sainttomn
OOo Enthusiast
OOo Enthusiast


Joined: 18 Feb 2011
Posts: 131
Location: Two gangster planets and a cowboy world

PostPosted: Fri Mar 18, 2011 9:57 am    Post subject: [Solved] Global repetative SQL Command Without Macros? Reply with quote

Hi all! I've got another question on my long-running database, that I could use a hand with.

I've come up with this code, that works for what I want it to do on a one-time basis:

UPDATE "Monitor" Set "Greet Score" = 10 WHERE "GreetMNN" = 'Met'

However, I need this to essentially repeat upon every update of the table. Is there a way to do this globally, or can a button on a form be assigned to do this with a macro?

A little background, right now when the user selects a radio button they get an output of Met, Not met, or N/A. The code, above, will find this data and assign a numeric value to it, which can be used as a score (which I later total in a query).

Any help or suggestions are appreciated.

Thank you,
-Tom Very Happy


Last edited by sainttomn on Fri Apr 01, 2011 11:01 am; edited 2 times in total
Back to top
View user's profile Send private message Visit poster's website
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Fri Mar 18, 2011 10:23 am    Post subject: Reply with quote

Hello

You can do it in the way you describe.

Test the SQL before you write a macro.

Print the code of the macro for the SQL before you run the macro.

Then test the complete macro.

Romke
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Mar 18, 2011 10:33 am    Post subject: Reply with quote

Just to add something to the great post by RPG above . . .

I would recommend, for efficiency that you make sure your field "GreetMNN" is an INDEX. That is, each time the UPDATE is needed, the database engine can go in by the index, rather than having to read the complete table.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
sainttomn
OOo Enthusiast
OOo Enthusiast


Joined: 18 Feb 2011
Posts: 131
Location: Two gangster planets and a cowboy world

PostPosted: Fri Mar 18, 2011 10:50 am    Post subject: Reply with quote

Thank you both!

I really appreciate the help! I'm brand-new to writing macros. What I'm going to be doing (thanks to Romke) is creating a button, or aybe adding to an existing button) to run the macro. I have 17 sets of radio buttons, which have 3-5 options for Met, Not Met, N/A (and a fe with "Not Met (10)" for example)

As I understand it, the code I posted can be tweaked to accomodate each field and button, and I think I know how that will work. The small snippet I posted worked when I used it as a global SQL.

I'm running into a little trouble when I post it in the macro portion of things. The system is returning some errors. Do macros use a different language than SQL? I'm using an embedded HSQL database (Base standard).

I appreciate the suggestion of telling the code to search the INDEX, rather than the form (thanks Sliderule), but I'm not certain how to do that?

Do you gentlemen have any suggestions as to how to start the code?

Thank you again, I really appreciate the help.
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Mar 18, 2011 11:10 am    Post subject: Reply with quote

sainttomn:

You said / asked:

sainttomn wrote:
I appreciate the suggestion of telling the code to search the INDEX, rather than the form (thanks Sliderule), but I'm not certain how to do that?


Let me address ONLY the above comment.

  1. Open your OpenOffice database file ( *.odb )
  2. On the left, press the Tables icon
  3. Under the Tables list in the middle, right click on your table "Monitor" and from the pop-up, choose Edit
  4. On the toolbar, click on Index Design
  5. On the Indexes window, click on the New Index icon
  6. You can assign a name if you want, or just accept the default name
  7. For the Index field drop down button, choose "GreetMNN"
  8. Do NOT press the Unique check box
  9. Click Close and Save It.

Explanation: Think of your database table as a telephone book. Because records are entered in a random order, but, if you want to find ( Update ) a particular record, by having an INDEX, like an telephone book that is printed in alphabetical order, you can find the name you want quickly. This INDEX will be maintained by the database.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Fri Mar 18, 2011 11:12 am    Post subject: Reply with quote

Hello

Did you print the SQL you generated?
Maybe you can find here and here more information.

Romke
Back to top
View user's profile Send private message
sainttomn
OOo Enthusiast
OOo Enthusiast


Joined: 18 Feb 2011
Posts: 131
Location: Two gangster planets and a cowboy world

PostPosted: Fri Mar 18, 2011 11:23 am    Post subject: Reply with quote

Sliderule, you are my hero.

I set-up the index (very easy). I went ahead and set-it up to take into account the GreetMNN, as well as the 16 other fields it should search for.

This is excellent, as my co-worker and I were discussing how the database would handle this calculation after several months/years, and we were expecting some slowdown in the search. It sounds like this will help prevent that quite a bit.

Thanks for the links, Romke, I'm going to read through those and see what I can some up with.

More to come in a little while.

Thank you again.
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Mar 18, 2011 11:32 am    Post subject: Reply with quote

sainttomn:

You said:

sainttomn wrote:
I set-up the index (very easy). I went ahead and set-it up to take into account the GreetMNN, as well as the 16 other fields it should search for.


I would suggest that perhaps your database design needs to be re-examined, but, I am not / canNOT design your database for you.

Slideurle
Back to top
View user's profile Send private message
sainttomn
OOo Enthusiast
OOo Enthusiast


Joined: 18 Feb 2011
Posts: 131
Location: Two gangster planets and a cowboy world

PostPosted: Fri Mar 18, 2011 12:09 pm    Post subject: Reply with quote

Quote:
I would suggest that perhaps your database design needs to be re-examined, but, I am not / canNOT design your database for you.


Is it bad that I have this indexed for all the fields the eventual macro will need to search?

I just wanted to say thank you.
Back to top
View user's profile Send private message Visit poster's website
sainttomn
OOo Enthusiast
OOo Enthusiast


Joined: 18 Feb 2011
Posts: 131
Location: Two gangster planets and a cowboy world

PostPosted: Wed Mar 23, 2011 9:27 am    Post subject: Reply with quote

Hi all, I'm back for more after wrestling with this issue and trying a few other things.

Macro writing is confusing and terrible and difficult and I'm really lost on that front.

I'm wondering, since I have this SQL written:
UPDATE "Monitor" Set "Greet Score" = 5 WHERE "GreetMNN" = 'Met'

And it works when I use it globally, but doesn't repeat when the new data entry form is completed each time (without re-entering the SQL over and over), is there any way to do this without macros?

I changed the the score (text) box mentioned in the initial post to a List Box, which seems to afford me the ability to assign SQL to the field, but I'm not sure if that will help.

I'm willing to try a macro (and have been trying), but I get lost everytime I start writing the macro.

Help, please! And thank you.
Back to top
View user's profile Send private message Visit poster's website
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Wed Mar 23, 2011 9:54 am    Post subject: Reply with quote

Hello

http://www.oooforum.org/forum/viewtopic.phtml?t=87703&highlight=executeupdate+sub
http://www.oooforum.org/forum/viewtopic.phtml?t=97086&highlight=executeupdate+sub

http://www.oooforum.org/forum/viewtopic.phtml?t=76327&highlight=executeupdate+sub

Romke
Back to top
View user's profile Send private message
sainttomn
OOo Enthusiast
OOo Enthusiast


Joined: 18 Feb 2011
Posts: 131
Location: Two gangster planets and a cowboy world

PostPosted: Fri Apr 01, 2011 10:59 am    Post subject: Reply with quote

Hi again, thank you for your help. I ended up going about this in a different way. Rather than use a macro, I set-up some dumy tables that related my "Met" data to a numeric value, and then wrote a query that pulls the numbers to my query/report.

I simply couldn't wrap my head around the macro way of doing things.

-Tom
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group