| View previous topic :: View next topic |
| Author |
Message |
OpenMartin
General User
Joined: 08 Mar 2012
Posts: 6
|
 Posted: Thu Mar 08, 2012 4:20 pm Post subject: Simple Inventory Database - Solved |
 |
|
Hi:
I have a hand full of parts that come and go frequently. I would like to create a simple database that shows when they come or go, and how much I have in the end in the inventory.
I am pretty new to Base. I have created a table with columns: Date, Part, Amount bought, Amount sold.
I would like to create a form that shows only one specific "Part" on top. And then a field that show: 'Total Parts in Inventory". Basically calculating from my table the specific parts and their current inventory amount.
In the same form should then show the Date, and Amount bought, and Amount sold for only that one part.
How do I basically filter out my table, in a form, so it only shows the info one part at a time and the Total Inventory?
Thanks for your help.
Last edited by OpenMartin on Thu Mar 15, 2012 2:42 pm; edited 1 time in total |
|
| Back to top |
|
 |
Arineckaig
OOo Advocate
Joined: 01 Mar 2004
Posts: 332
|
| Posted: Fri Mar 09, 2012 4:08 am Post subject: |
|
|
Welcome to the forum.
You will find that something similar was discussed in this thread:
http://www.oooforum.org/forum/viewtopic.phtml?t=140994&highlight=
Maintaining running totals is not the simplest of tasks for SQL but a query along the following lines should give you some pointers:
| Code: | SELECT "A"."TransID", "A"."Stamp", "A"."PartID", "A"."In", "A"."Out",
( SELECT SUM( COALESCE ( "In", 0 ) )
FROM "Data" AS "B"
WHERE "A"."PartID" = "B"."PartID" AND "A"."Stamp" >= "B"."Stamp" )
- ( SELECT SUM( COALESCE ( "Out", 0 ) )
FROM "Data" AS "C"
WHERE "A"."PartID" = "C"."PartID" AND "A"."Stamp" >= "C"."Stamp" ) AS "Balance"
FROM "Data" AS "A"
ORDER BY "A"."PartID", "A"."Stamp" |
To give you an idea of how this works I have prepared a simple demo file that you may wish to download from http://db.tt/EmL1H3AI
Please come back if you have any questions or require more detailed explanation.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu |
|
| Back to top |
|
|
OpenMartin
General User
Joined: 08 Mar 2012
Posts: 6
|
| Posted: Fri Mar 09, 2012 2:41 pm Post subject: Thanks, Arineckaig |
|
|
Thanks a lot Arineckaig! Your sample file hit the nail on the head. I already took the liberty and copied your code and modified the tables and form. Looks like I need to know SQL in order to get ahead in OOO. 
I am now hitting another wall. How do make another form that lists all the parts and current totals of them? Basically one column with part names and another with the current Balance. I cannot seem to gather all the info from the two tables into the form so I can list Part Names and total current Balance all on one sheet. A query wont allow the code to be transferred.
After that I will attempt to make a report from that form as well, but that shouldn't (hopefully) not be that hard for me.
Thanks again. |
|
| Back to top |
|
|
Arineckaig
OOo Advocate
Joined: 01 Mar 2004
Posts: 332
|
| Posted: Sat Mar 10, 2012 1:45 am Post subject: |
|
|
| Quote: | | Basically one column with part names and another with the current Balance |
Use an aggregation query as the source for the data form - something along these lines:
| Code: | SELECT "Parts"."PartName", SUM( "In" ) - SUM( "Out" ) AS "Balance" FROM "Data"
JOIN "Parts"
ON "Data"."PartID" = "Parts"."PartID"
GROUP BY "Parts"."PartName"
ORDER BY "Parts"."PartName" ASC |
I have added an example to the demo file that you can download from the same link as my previous post. In that file's form document you can update records in the upper data form table for any particular part, but the 'Balances' data form will not update automatically for such changes. I have added a 'Refresh button' to the 'Balances' data form so that the balances can be updated without the need to reload the whole form document.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu |
|
| Back to top |
|
|
OpenMartin
General User
Joined: 08 Mar 2012
Posts: 6
|
| Posted: Sat Mar 10, 2012 2:49 pm Post subject: |
|
|
Sorry, but I must be doing it wrong. When I try to open the form, I get the message: The data could not be loaded: Syntax error in SQL exssion.  |
|
| Back to top |
|
|
OpenMartin
General User
Joined: 08 Mar 2012
Posts: 6
|
| Posted: Sat Mar 10, 2012 4:41 pm Post subject: |
|
|
I am working on a form for the Data table so I can enter the parts but it always asks me for the PartsID which I understand is part of the SQL code. Is there a way to not have to use the PartsID in the form but the actual parts names from a combo box? Would that mean a different SQL code? I wish I could attach the file on here, so you can see. How did you do that?
Thanks so much for your help. |
|
| Back to top |
|
|
Arineckaig
OOo Advocate
Joined: 01 Mar 2004
Posts: 332
|
| Posted: Sun Mar 11, 2012 4:18 am Post subject: |
|
|
| Quote: | | I am working on a form for the Data table so I can enter the parts but it always asks me for the PartsID which I understand is part of the SQL code. Is there a way to not have to use the PartsID in the form but the actual parts names from a combo box? |
In the demo file there is a 'one-to-many' relationship between the Parts table and the Data table. Thus, the Data table contains the PartID field that is the Foreign Key link to the Primary Key field in the Parts table. Thus, any form that simply uses the Data table as its data source can only access the PartID field, which tends to be a less than user friendly integer: the more comprehensible PartName field is located in the Parts table which is separate from the Data table. Because the two tables are linked by their foreign and primary keys, the use of a list box in OOo Base overcomes this problem. The list box reads and displays values from the one side Parts table, but is bound to the foreign key field in the many side Data table. Whenever a selection is made from the displayed list, the value of the primary key field of that selection is used to updated the foreign key field. Thus the two tables are kept in sync.
Again in the demo file you will see that the 'Inventory balances' form document contains a form - displayed as the larger upper grid/table. It uses as its data source the 'qryInventoryBalances' Query which merely reads fields from the Data table and the crude output of the query only shows the less than user-friendly PartID foreign key field. On the other hand, the PartID column of the grid/table is a list box. It displays the more user friendly values of the PartName field read from the Parts table.
The data tab properties of this PartID list box includes this SQL:
| Code: | | SELECT "PartName", "PartID" FROM "Parts" |
In this way the list box reads both fields from the Parts table. The first field is used for the displayed list from which a selection can be made. Whatever PartName is selected the value of the related PartID primary key field in the Parts table is used to set the value of the PartID foreign key field in the Data table.
In effect you can use this grid/table to add entries to the Data table but only for any part that is already included in the Parts table. If you want to add a new part it must be done in a form that uses the Parts table as its data source. I have updated the same demo file that can be downloaded as above by adding an extra demo form document with a grid/table sourced from the Parts table. The primary key PartID field will be automatically supplied so only the name need be entered. Any new entries added to the Parts table will then appear in the left hand table in the 'Inventory balances' form document.
I suggest this extra entry form because there may well be other fields needed in the Parts Table. If however you are only concerned with the PartName, it can be entered as a new item direct in that left hand table in the 'Inventory balances' form document because that table only reads fields from the Parts table. After adding entries to the parts table you will need to refresh the main upper grid/table by clicking the 'refresh' icon on the Navigation toolbar (a blue semi-circular arrow pointing to the right and down).
| Quote: | | I wish I could attach the file on here, so you can see. |
If you are still having problems send me a PM and I will give you an address to which to send your file.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu |
|
| Back to top |
|
|
OpenMartin
General User
Joined: 08 Mar 2012
Posts: 6
|
| Posted: Thu Mar 15, 2012 5:23 am Post subject: |
|
|
Wow, that's a brilliant form! I get it now! No extra form needed to enter data, it's all there in the main inventory form. Thanks for your help. Makes sense to me now.
The only difficulty now that still puzzles me is when I open the 'inventory balances' form, I still get the error message: "The data could not be loaded: Syntax error in SQL expression." And the lower box for inventory balances and the "refresh balances" button are blank. Do you get that message too?
Thanks,
Martin |
|
| Back to top |
|
|
Arineckaig
OOo Advocate
Joined: 01 Mar 2004
Posts: 332
|
| Posted: Thu Mar 15, 2012 11:05 am Post subject: |
|
|
| Quote: | | Do you get that message too? |
No - most peculiar! Have just downloaded the file from the link in my earlier post and cannot reproduce your error message. I should mention that I used, and am using, XP and the embedded HSQLDB for this rough demo Base file.
It would appear there is a fault in the query "qryBalances" that is the data source for the data form that is showing blank. You could check whether that Query works on its own. If it fails the SQL for that query should be:
| Code: | SELECT "Parts"."PartName", SUM( "In" ) - SUM( "Out" ) AS "Balance"
FROM "Data"
JOIN "Parts"
ON "Data"."PartID" = "Parts"."PartID"
GROUP BY "Parts"."PartName"
ORDER BY "Parts"."PartName" ASC |
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu |
|
| Back to top |
|
|
OpenMartin
General User
Joined: 08 Mar 2012
Posts: 6
|
| Posted: Thu Mar 15, 2012 2:41 pm Post subject: |
|
|
| Yes, now it all works! I went to the query and clicked on "Run SQL Command directly" and saved it as such. Now it all works! Thanks a lot! Very good advice. I know so much more about Base. Thanks. |
|
| Back to top |
|
|
|
