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_|

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!

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.

Don't know how you can do it in Delphi, but in paradox you can wirte a script like that:

What do you mean by writing in pradox?I`m new to paradox so is it a scripting software or what should i use ?

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...

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

Don't worry; as my father teached me when i was a little boy, "There's no lame question, only lame answers..." ...

Bye!

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.

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 ??

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!

Thanks ByteRyder52,
I have only one problem, I cant find a copy of paradox.

Why don't you buy it directly from Corel? www.corel.com section store.

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!