Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Query Help

  1. #1
    Join Date
    Jan 2005
    Posts
    48

    Unanswered: Query Help

    Can someone help me with the query for the this table:

    Item#|name|sold|
    1____|soda |___|
    _____|bar__|10_|
    _____|deli__|2__|
    2____|beer_|___|
    _____|bar__|100|
    _____|deli__|10_|

    The problem is that i have around 1000 items and its hard for me to look for sold quantities. I would like to have somthing like this.

    ITEM|BAR|DELI|
    1__ |10__|2__|
    2__ |100_|10_|


    I dont know how to query db so that i can distribute items and quanttites sold into new db or how to update the first db so that it looks like this:

    Item#|name|sold|
    1____|soda |___|
    1____|bar__|10_|
    1____|deli__|2__|
    2____|beer_|___|
    2____|bar__|100|
    2____|deli__|10_|

  2. #2
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    I think you've got a problem on how your tables are structured; if the item column in the first table should, as you seem to need now, be used to cathegorize the records, then you really should have:

    1) ALWAYS a value in item # field and not only when the item number changes
    2) another table in which you define what item numbers mean
    3) a link between the two (that can be optional, but personally i'd insert the referential integrity bond...)

    Then, if you're sure that what the first table means is that until the item # doesn't change it should be repeated, you can do this with a simple scan loop on a tcursor on the table: each time you read a record, choose what to do: if the item # is not null then memorize it in a temp var, if the item # is null then set it to the value you've memorized into the temp var.

    Just be completely sure of the order in which records get processed, since it is crucial !

    Bye!
    The only failure is not trying to do it.

  3. #3
    Join Date
    Jan 2005
    Posts
    48
    Where do i write this loop?What would be the best language to do it?I have the table in paradox and csv file. I know that the table is structured wrong but thats the way i got it. I use datadesktop from delphi and i dont know if can put any loops in code there.

  4. #4
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    Don't know how you can do it in Delphi, but in paradox you can wirte a script like that:

    Code:
    method run(var eventInfo Event)
    var
    	tmptc tcursor
    	tmpvalue longint
    endvar
    	if tmptc.open("tablename") then
    		tmpvalue=-1
    		scan tmptc:
    			if tmptc.("item#")<>"" then
    				tmpvalue=tmptc.("item#")
    			else
    				tmptc.edit()
    				tmptc.("item#")=tmpvalue
    				tmptc.endedit()
    			endif
    		endscan
    		tmptc.close()
    	endif
    endMethod
    The only failure is not trying to do it.

  5. #5
    Join Date
    Jan 2005
    Posts
    48
    What do you mean by writing in pradox?I`m new to paradox so is it a scripting software or what should i use ?

  6. #6
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    You've posted in the Corel Paradox forum and you don't know what Paradox is ?

    Funny!

    Jokes apart, Corel Paradox is a full blown DB application developement suite, with which you can do everything from creating the tables to building a full blown win32 app complete with Form, Reports and, that's it, scripts written in an Object Oriented language known as ObjectPAL which you can run...
    The only failure is not trying to do it.

  7. #7
    Join Date
    Jan 2005
    Posts
    48
    Thanks a bunch. I know its funny, i even feel bad asking this type of questions. But, you were helpfull and i appreciate that you answer lame questions too

  8. #8
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    Don't worry; as my father teached me when i was a little boy, "There's no lame question, only lame answers..." ...

    Bye!
    The only failure is not trying to do it.

  9. #9
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    It seems that there are two issues here: database structure and ObjectPAL syntax - both of which are fairly major. If you need help with database structure, Dennis Santoro has some good articles on his RDA World website. For ObjectPAL, Paradox has built-in help manuals. But the learning curve on ObjectPAL can be pretty steep. So, although the problem seems pretty straightforward, getting the skills to solve it can be some work.

  10. #10
    Join Date
    Jan 2005
    Posts
    48
    I didnt create this db and i know that it looks pretty bad. I got myself a copy of wordperfect office 11 and i dont know which tool i should use to manipulate this db? i thought about converting it to mysql and then i could do somthing with php but since i started with paradox i want finish it because i will have more of this kind in the future. Can you guys give some links or instruct me a bit more in detail ??

  11. #11
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    WordPerfect 11 standard does not include Paradox. You can get Paradox 9 as a standalone application or you can get Paradox 10 or higher with the Professional versions of the WordPerfect suite. Quite a few books were written for Paradox 9 (e.g., Power Programming by Prestwood, which has excerpts published on the web) but there has been little written for later versions. That's okay, because later versions are not that different from 9. With Paradox you will get ObjectPAL, the powerful scripting language that allows database manipulation and creation of forms, reports, libraries, etc. Detailed help manuals come with Paradox, including tutorials and references. Other resources: I think some manuals are available on the Corel website. Books and programs can often be found on eBay.

    In considering your database, perhaps the following structure might be good. If "Item" refers to a product, the Deli, Soda, etc. referes to a site(?), and the final column is sales(?), then three files would cover it.

    Sales.db with fields ItemCode, SiteCode,Count. Primary index on ItemCode and secondary on SiteCode.
    Items.db with ItemCode and ItemName. Primary index on ItemCode.
    Sites.db with SiteCode and SiteName. Primary index on SiteCode.

    There would be 1:Many links from Sales.db to Items.db (on ItemCode) and Sales.db to Sites.db (on SiteCode).

    As noted earlier, you may want to hard-code referential integrity between the tables.

    An ObjectPAL script could accomplish this data transfer. The logic would be something like this: Create the new files (review Create syntax in ObjectPAL); attach a TCursor to each and set to edit (review how ObjectPAL uses TCursors), attach a TCursor to the original file and scan (review Scan syntax in ObjectPAL); within the scan, set up logic to populate the new tables; close the TCursors. Live happily ever after!
    Last edited by ByteRyder52; 02-06-05 at 15:09.

  12. #12
    Join Date
    Jan 2005
    Posts
    48
    Thanks ByteRyder52,
    I have only one problem, I cant find a copy of paradox.

  13. #13
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    Why don't you buy it directly from Corel? www.corel.com section store.
    The only failure is not trying to do it.

  14. #14
    Join Date
    Jan 2005
    Posts
    48
    The thing is that I need it only for this one project and i wont use it anymore.
    Last edited by nicpon; 02-07-05 at 14:32. Reason: missing word

  15. #15
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    Quote Originally Posted by nicpon
    The thing is that I need it only for this one project and i wont use it anymore.
    Then use whatever developing tool you like: nearly every database tool will read in paradox tables, and ALL database tools read CSV files.

    Transfer data to your favorite language/develop environment and then use it to manipulate and correct data.

    That's simple!
    The only failure is not trying to do it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •