[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
darkshark
General User

Joined: 08 Feb 2010
Posts: 32

 Posted: Mon Feb 08, 2010 10:59 am    Post subject: Please help me with this data elaboration Hi to all :) i have a table where i track the production data of my factory, the table is something like this: lot.n date.bag1 weight.bag1 date.bag2 weight.bag2.......... date.bag25 weight.bag25 The lot.n is the lot number of the product, date.bag1 is the timestamp of the end of the production of first bag of material of the lot, and weight.bag1 is the weight of the bag, the table cover the production of a maximum of 25 bag, but we can have less than 25 bags in a lot. I already have a query that sum all the time needed to product a lot by datediff of the first date and the last, and the total weight of the lot, i also have the kg/h at witch we have producted the lot. This elaboration is focused on the producion of the lots, but now i need to make a query that sum all the production of a single day (it colud be sum of the weight of bags that comes from one, two or more different lots of product). The output should be a table with a row for each day where we have product something, where i have a field with the total kg of product that we have done that day. I hope that my poor english will let you understand my problem... :) I use ooo Base with the HSQL engine with an incorporated database. Thanks a lot for any kind of advices! :) Edit: it could help me even if someone knows how to transform a table in this way: - Orignal table lot.n date.bag1 weight.bag1 date.bag2 weight.bag2..... 1 08/02/10 12.00 900 08/02/10 13.00 850 2 08/02/10 17.00 800 09/02/10 8.00 700 - modified table lot.n date weight 1 08/02/10 900 1 08/02/10 850 2 08/02/10 800 2 03/02/10 700
BigAndy
OOo Enthusiast

Joined: 03 Jan 2010
Posts: 150

 Posted: Mon Feb 08, 2010 1:59 pm    Post subject: you have strongly denormalized your data by combining in one table all fields. Therefore you should create a lot of queries by each peer of field and then join them by union state query
d4rksh4rk
General User

Joined: 08 Jul 2009
Posts: 16

 Posted: Mon Feb 08, 2010 3:22 pm    Post subject: ok... do you have some advices on a better way to organize the data? i've not much experence about db (this is the first one i made) so any kind of advice is welcome. By the way, i've put all the data in a single table because all the different date and weight are all relative to a single lot of product, in this way i was able to made a form where i can write all the data relative to a specified lot. I you know a better way even to build a form to add all the data of the different bags that characterize a lot when the data are on different tables please tell me that too
BigAndy
OOo Enthusiast

Joined: 03 Jan 2010
Posts: 150

Posted: Mon Feb 08, 2010 10:52 pm    Post subject:

It may be different ways/
One of:
first table assume named lot:
 Code: id integer primary key lot varchar(20) comment varchar

Second one assume named bags:
 Code: id integer primary key lot_id ' this field would refer to lot.id field as foreign key bag_number varchar ' or integer. As you want date date weight numeric (10,4) comment varchar

Then make form with subform and join two tables by fields lot.id -> bags.lot_id and input related data
In this case you can make select query from joined tables:
 Code: select lot.id, bags.date, bags.weight from lot join bags on lot.id=bags.lot_id [where|having] bags.date=
BigAndy
OOo Enthusiast

Joined: 03 Jan 2010
Posts: 150

Posted: Tue Feb 09, 2010 12:07 am    Post subject:

 Quote: i have a table where i track the production data of my factory

Btw, i should admonish against using hsqqldb for corporate using/ It's to ugly and you can loose data with a good chance. Ever you ar using backup. maintain data and application in one container is bad idea (refer to HSQLDB+OOO and M\$Access)
Our enterprise uses postgresql+OOBase and DB2+OOBase.
Works perfectly
d4rksh4rk
General User

Joined: 08 Jul 2009
Posts: 16

 Posted: Tue Feb 09, 2010 12:42 am    Post subject: Thanks a lot for your advices. I'll try as soon as i can to normalize the data in my db, sadly there are a bunch of other elaboration that for now i make with all the data put in a single table, that i don't know how to transform in elaborations done on a column (given a group of rows), like the calculation of the time elapsed between the first bag and the last bag of one lot, or the calculation of the time when the machine is not working by the sum of the datediffs between the various stop and restart time that we have got during the production of a lot. Sigh... there are so many thing i need to learn... edit: i've read your post about the internal HSQL db. There's a guide that explain how to convert an internal ooo base db in an external db like you said? btw i would thank you again for you precious advices, i've got to learn to make a db and use it for work at the same time, and any kind of suggestion like yours to improve my db are really really useful
BigAndy
OOo Enthusiast

Joined: 03 Jan 2010
Posts: 150

Posted: Tue Feb 09, 2010 1:49 am    Post subject:

 d4rksh4rk wrote: Thanks a lot for your advices. i've read your post about the internal HSQL db. There's a guide that explain how to convert an internal ooo base db in an external db like you said?

By exporting tables into csv files (you can find out here comlete recomendations with examples or can learn at hsqldb.org documentation page.
Import into other RDBMS depends on RDBMS. For example in Postgresql you can use COPY command and IMPORT comand in DB2 engine.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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