[Solved] Trying to figure out my inventory database project

[jpm92]

First of all, thanks to everyone contributing to this forum, it has helped me a lot learning to handle ooBase. Right now I'm working on an inventory database for my company, since the software we used to use it's no longer suitable for us. Basically, I want to have a registry of every product that comes in or out the warehouse, and also a query inventory to stay informed about the stock. I think I have most of it figured out, but I need to solve these two problems:

1 - As you can see, qInventario shows nothing...why is that?
2 - Also, I want every model in the queries to be shown, even if there's have been no movement...I just want the query to show "model: X" "inputs: 0"

You can find the project attached, big gracias in advance!

[Villeroy]

download/file.php?id=24572

[jpm92]

Thanks! Actually that's the database I've based mine one, but I don't understand the inventory query, what's the function of the coalesce and jo words in the sql code? I can't make it work on mine, the query is empty

EDIT: Also this example doesn't work for me, since each of my products has a unique serial number that I have to keep track of, thereby I have to introduce them one by one. This database is based on SUM, but I have to COUNT number of entries, and that's where it seems to be not working

[Villeroy]

This is plain and simple SQL. You can not query any relational database without knowing the mere basics about SQL.
The SQL for your HSQL database of version 1.8 is documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html but it is almost the same SQL as in any book on SQL.

Code: Select all

SELECT SUM( "Input"."Quantity" ) AS "IN", "Articles"."ID" 
FROM { OJ "Input" RIGHT OUTER JOIN "Articles" ON "Input"."Article_ID" = "Articles"."ID" } 
GROUP BY "Articles"."ID"

Is the most trivial way to show the SUM of inputs for each article, even if there is no matching input for an article (right join).
When you run the above SQL with my database you see that article #40 has no input record. You see the article ID but the quantity is a null value (empty). Any operation with a null value returns null which is why I coalesce these values to zero.

Code: Select all

SELECT SUM( COALESCE ( "Input"."Quantity", 0 ) ) AS "IN", "Articles"."ID" 
FROM { OJ "Input" RIGHT OUTER JOIN "Articles" ON "Input"."Article_ID" = "Articles"."ID" } 
GROUP BY "Articles"."ID"

This query and the same query for the output are referenced by qInventory. It selects all articles and all corresponding in/out quantites subtracting the output quantities from input quantities.

Code: Select all

SELECT "Articles"."Name", "qInput"."IN", "qOutput"."OUT", "qInput"."IN" - "qOutput"."OUT" AS "DIFF" 
FROM "Articles" AS "Articles", "qInput" AS "qInput", "qOutput" AS "qOutput" 
WHERE "Articles"."ID" = "qInput"."ID" AND "qOutput"."ID" = "Articles"."ID"

or equivalent variant with JOIN:

Code: Select all

SELECT "Articles"."Name", "qInput"."IN", "qOutput"."OUT", "qInput"."IN" - "qOutput"."OUT" AS "DIFF" 
FROM "Articles" INNER JOIN "qInput" ON "Articles"."ID" = "qInput"."ID"
  INNER JOIN "qOutput" AS "qOutput" ON  "qOutput"."ID" = "Articles"."ID"

[jpm92]

Thanks for the help Villeroy, I have understood what the coalesce and right outer join functions are for, but I can't manage to use them with the count component. I leave this to the will of gods

[chrisb]

hello jpm92,

i am not entirely sure as to your requirements but have created a simple query which counts the number of entries of unique items in the tables 'entradas' & 'salidas'.
copy & paste the code here>>>> 'Database>Queries>Create Query in SQL View'
menu:Edit>Run SQL command directly or hit the SQL icon (this is not essential but will keep the formatting when saved).
hit execute.

Code: Select all

select M."Modelo",
coalesce(E."Entradas", 0) "Entradas",
coalesce(S."Salidas", 0) "Salidas",
coalesce(E."Entradas", 0) - coalesce(S."Salidas", 0) "Unidades"

from "modelos" M

left join
(select "idModelo", count(*) "Entradas" 
from "entradas"
group by "idModelo"
) E
on E."idModelo" = M.ID

left join
(select "idmodelo", count(*) "Salidas"
from "salidas"
group by "idmodelo"
) S
on S."idmodelo" = M.ID

order by "Modelo"

[jpm92]

Hello chrisb, it works perfect, just what I needed
I'd like to know though why are you using that way of calling the tables (M."Modelo", E."Entradas"). Shouldn't it be "modelos"."Modelo"?

Thanks once again,

[Villeroy]

Please learn SQL.

[jpm92]

That's what I'm trying to do, but it's gonna take time (we need to start using the inventory ASAP). I guess we'll have to find another option. Do you have any links where I can learn the SQL that ooBase is actually using?

I appreciate your help, thanks!

[Villeroy]

I'm not willing to pay for your lack of qualification and bad planning. Database design is development work usually done by well paid professionals. There are thousands of SQL tutorials online and dozends in your local book store (if you are living in a city with real book stores). Chris' SQL statement is exactly the same as mine but merged into one query where I used 3 queries.

Code: Select all

SELECT... FROM (SELECT...FROM "Table1")AS M JOIN (SELECT...FROM "Table2")AS E ON M.A=E.B

where M and E are the same as the separately stored "qInput" and "qOutput" in my query.

[chrisb]

hello jpm92,

getting to grips with base is not easy. you have come a long way & all without asking a single question on this forum previous to this topic.
the truth of the matter is that in order to progress further some knowledge of SQL is essential. this is not a SQL forum but SQL is an integral part of database construction.
your project is quite ambitious for a relative newbee but over time & with perseverance it is achievable.
the code posted by Villeroy does indeed demonstrate the method we all must use in order to produce an inventory(IN/OUT/Balance).
the difference between the two databases is that Villeroy i presume (i have not looked) moves in/out a variable number of units but you move in/out only one item at a time because each item possesses a unique serial number.
this means that Villeroy sums() his items but you count() yours.

you yourself created three queries very similar to those posted by Villeroy in an attempt to solve your issue.
its perfectly natural that you felt uncertain as to whether to use sum() or count().
i also created three queries but condensed them into one. you & i both used the count() function. i omitted some of your code which was unnecessary.

as to the letters M, E & S
M is an alias for the table "modelos". 'from "modelos" M' is eqivalant to 'from "modelos" as M'. its neater & saves on typing.
E & S are identifiers for the sub queries.
we could just as easily use X, Y, XY, XYZ etc. i used the first letter of the table name which makes remembering what it references easy.

i do also agree that the objective of those who contribute to this forum should be to offer help & guidance to others rather than provide a complete solution but even so all things relative.

[jpm92]

Well, I think I should've explained this before. I'm no computer technician. I studied the basics of databases in college and I thought: hey, maybe I can solve my problem with that. I see it's more difficult than I thought and I need to learn SQL, or hire someone to do it. The thing is, we are just starting and every coin you save is well saved. Also I'm kind of curious about databases and their potential, so I'd like to do it on my own. It wasn't my intention to make you pay for my lack of knowledge Vileroy, I was just asking for help without really knowing what was behind. As soon as you replied I started diggin into some SQL tutorials and actually understood the whole query of chrisb and adapted it to my database (it has now changed since the version I uploaded). Now I'm trying to make it work adding a colour column in the query, I amost have it (I hope ). Thanks both for your help, specially chrisb, since you actually made me learn. Have a good day!

[Villeroy]

... the difference between the two databases is that Villeroy i presume (i have not looked) moves in/out a variable number of units but you move in/out only one item at a time because each item possesses a unique serial number.
this means that Villeroy sums() his items but you count() yours.

That's right. I sum up a "Quantity" field. When you replace SUM(...) with COUNT(...) in my queries, you get the count of individual transactions for each item and then the difference between ingoing and outgoing transactions regardless of the quantities.