Question ms Access QUERY HELP !!

[Rema]

Hello everyone
I have a question about MS ACCESS , i a,m designing a warehouse management system, and I want to calculate the balance of materials in the warehouse
But the units are different
For example, the user entered 2 tons of material x
And then enters 25 kg of the same material
Then pull put 700 kg (from x )from the warehouse
!!!
I have an Operation details Table
(material- quantity - the unit - the unit price -..)
I have a query to calculate the balance but it does not take into account the units ,:banghead:

it calculates the balance only by numbers !!
Any ideas ?!

 

[Solo712]

Hello everyone
I have a question about MS ACCESS , i a,m designing a warehouse management system, and I want to calculate the balance of materials in the warehouse
But the units are different
For example, the user entered 2 tons of material x
And then enters 25 kg of the same material
Then pull put 700 kg (from x )from the warehouse
!!!
I have an Operation details Table
(material- quantity - the unit - the unit price -..)
I have a query to calculate the balance but it does not take into account the units ,:banghead:

it calculates the balance only by numbers !!
Any ideas ?!

Would like like to show us your query, perhaps ? Only some of us here are psychics.

Best,
Jiri

 

[JLCantara]

Hello Rema!

Simple solution:
1 - Create a table Units.
2 - Fields:
Unit - AutoNumber.
UnitName - Text (10).
UnitKg - Single: this is the trick since this field is used to convert whatever unit you use into Kg.
3 - In queries, link the Units table; then you use the following conversion:
..., Weight*UnitKg as WeightKg.
4 - That's it!!!

Good luck, JLCantara.

 

[JLCantara]

Hello Solo712!

I am not a psychic, just have 40 years experience in programming!!!!

Good day, JLCantara.

 

[nfk]

Another approach would be download Visual Studio Community and SQL Server Express and build everything from scratch, that would save countless hours of troubleshooting in the future and mayor grief...

 

[spikepl]

Nfk another approach again would be to sit on your urge to advise until it goes away or until you come up with advice befitting the recipients here.

 

[nfk]

Nfk another approach again would be to sit on your urge to advise until it goes away or until you come up with advice befitting the recipients here.

Ok, let put it this way:

Another approach would be download Visual Studio Community and SQL Server Express and build everything from scratch

Is that not a valid option?, is not a short easy option, but it still something the op might want to take in account...

You're harassing me because I'm a well known Microsoft Access hater... This is racism! I will not stand for this violation of my human rights!

 

[The_Doc_Man]

Let's try this... where the user would enter the value, have a two-part field - a text box for the number and a combo box for the units, and you can default to whichever unit is most favorable to your situation. (Here, I'm not psychic enough to be sure.) Then when you go to store the record, you can consult the text box and the units specification.

Now, if I were being sneaky, what I might do is have the record bound to the form but to make the weight value hidden. Like, maybe make it not visible and move it underneath (move-to-top/move-to-bottom design option) something else. Then the text box you see would be unbound as would the units selector. When you do a lost focus out of either one, recompute the value of the hidden box (that is the REAL value of the field) and store that immediately. Lost_Focus is a good event choice for this but of course not the only choice. Then, when you go to save that record, it is properly bound and has a value computed from your text box and units selector.

The only other question would be to decide what units you would use for the real value - be it kg or metric tons or Troy ounces. If the value is DOUBLE format, I would just pick the unit that is the smallest amount anyone could ever check out in your operation and then make all other values multiples of that unit. I'm suggesting "smallest unit" because if you went the other way, you would have to use lots of fractions, and in terms of binary floating numbers, fractions are tougher to resolve exactly.

 

[Rema]

Hello Rema!

.
3 - In queries, link the Units table; then you use the following conversion:
..., Weight*UnitKg as WeightKg.
4 - That's it!!!

thank you JLCantara
It was a very useful idea, and i did it , but
at the STEP 3 , i am having a problem
i think it is syntax or something !!
I ll write the SQL statements , hoping you ,or somebody , can help me ,:banghead:

((
SELECT materials.[material_Name], Nz((SELECT Sum([unit_convertor]*[Amount]) AS Expr1
FROM Transation_Table INNER JOIN([Transation_detalis_table] INNER JOIN [units_Tables] ON [Transation_detalis_table].[unit] = [units].[unit_id] ) ON [Transation_detalis_table].[ID] = [Transation_Table].[ID]
WHERE (((Transation_Table .[transaction_TYPE])=1) AND(([materials].ID)=[Transation_detalis_table].[Material_name])) ; ,0) AS In ,
Nz((SELECT Sum([unit_convertor]*[Amount]) AS Expr1
FROM Transation_Table INNER JOIN([Transation_detalis_table] INNER JOIN [units_Tables] ON [Transation_detalis_table].[unit] = [units].[unit_id] ) ON [Transation_detalis_table].[ID] = [Transation_Table].[ID]
WHERE (((Transation_Table .[transaction_TYPE])=1)
AND (([materials].ID)=[Transation_detalis_table].[Material_name])) ; ,0) AS Out, [in]-[out]AS balance
FROM Materials ;
))
:banghead:
and many thanks to you

 

[JLCantara]

Hello Rema,

Your query is not easy to under stand!!! Can you send the tables used for it, with or without data? I thing that conversion might be done at the wrong place, so I need the table structure.

Good day, JLCantara.

 

[Rema]

Hello Rema,

Can you send the tables used for it, with or without data?
.

hey JLCantara,
sure I can, But they are in Arabic language ..i tried to translate them for you , I hope you understand them
i have these Tabels :
transaction (Id, CLIENT,date, Transaction-type )
transaction_details (id , TRANSCATION_ID , material, AMOUNT, unit,unit_price,warehouse,total,destination )
Materials (iid, material_code , material_name ,purchase_price)
Customers (id, Name, Email, supplyOrCOSTOMer, ...)
Drivers (id. name ....etc)
Ware-houses (id, name,location ..)
transactionTypes(transaction_code, transaction_type ) // 1 for IN //2 for out
Units (id, name, convertor ) (thank you for it )
I am attach a photo , for the relations

many many THANKS

 

[JLCantara]

Hi Rema!

You have
...JOIN [units_Tables] ON [Transation_detalis_table].[unit]...
details - spelling error!

Good day, JLC.

 

[JLCantara]

Hi Rema!

Here is your query:

"SELECT TransactionDetails.Material,
Sum(IIf([TrType]=1,
[TransactionDetails].[Amount]*[Units].[Factor],
-[TransactionDetails].[Amount]*[Units].[Factor])) AS AmountKg
FROM Units INNER JOIN (Transactions INNER JOIN TransactionDetails ON Transactions.Transaction = TransactionDetails.TransactionID) ON Units.Unit = TransactionDetails.Unit
GROUP BY TransactionDetails.Material;"

if what you want is the balance per material.

Good luck, JLC.