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

Please help me with this data elaboration

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
darkshark
General User
General User


Joined: 08 Feb 2010
Posts: 32

PostPosted: Mon Feb 08, 2010 10:59 am    Post subject: Please help me with this data elaboration Reply with quote

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
Back to top
View user's profile Send private message
BigAndy
OOo Enthusiast
OOo Enthusiast


Joined: 03 Jan 2010
Posts: 150

PostPosted: Mon Feb 08, 2010 1:59 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
d4rksh4rk
General User
General User


Joined: 08 Jul 2009
Posts: 16

PostPosted: Mon Feb 08, 2010 3:22 pm    Post subject: Reply with quote

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 Smile
Back to top
View user's profile Send private message
BigAndy
OOo Enthusiast
OOo Enthusiast


Joined: 03 Jan 2010
Posts: 150

PostPosted: Mon Feb 08, 2010 10:52 pm    Post subject: Reply with quote

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=<target_date>
Back to top
View user's profile Send private message
BigAndy
OOo Enthusiast
OOo Enthusiast


Joined: 03 Jan 2010
Posts: 150

PostPosted: Tue Feb 09, 2010 12:07 am    Post subject: Reply with quote

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. Very Happy 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
Back to top
View user's profile Send private message
d4rksh4rk
General User
General User


Joined: 08 Jul 2009
Posts: 16

PostPosted: Tue Feb 09, 2010 12:42 am    Post subject: Reply with quote

Thanks a lot for your advices. Very Happy
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... Sad

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 Smile
Back to top
View user's profile Send private message
BigAndy
OOo Enthusiast
OOo Enthusiast


Joined: 03 Jan 2010
Posts: 150

PostPosted: Tue Feb 09, 2010 1:49 am    Post subject: Reply with quote

d4rksh4rk wrote:
Thanks a lot for your advices. Very Happy

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.
Back to top
View user's profile Send private message
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