Question about demands to products

[Mehvan]

Hello everyone

I buy industrial irons from products and I give them demands in one month maybe 5 times


for example : I will give to those products 200 tons on 200$ but these 200 tons will be several measurements , and he will tells me after one month your demand will be ready , and maybe I will give him after 10 days another demand 300 tons on 220$ and also will be ready my demand after one month .

How I will know when he send to me my demands I will know is that tons already on that price send to me or not ?

[Bulzie]

Can you give example of your tables and fields and sample data in those including any linking fields? Also try to explain again in another way what you are trying to do.

[Mehvan]

okay
now you have a products , and I will come to your factory to buy Irons and I will give you order
and I have many kinds of irons maybe more than 500 sizes , We sale and buy about them thickness
for example : I will give you order about these kinds of pipes ( pipe 40*80*1.50mm = 50 tons ,,, pipe 50*100*2mm = 40 tons ....) and you will give me price of these kind , for example : you will say ( 1.50 mm ) by 200$ and ( 2 mm ) by 180$ also you will say after one month your order will be ready , because maybe you have other orders
and after one week I will give you another order but you will tell me price are rising ( 1.50 mm ) by 220$ and ( 2 mm ) by 200$ and also you will tell me your second order will be ready after one month or more , also I will give you order on that price too and more orders .

How I will know When you sent to me my orders , I will know price and tons are identical ?
when I enter my data in program : pipe 40*80*1.50mm = 50 tons already be on 200$ when come more than or less than about that price it tells me
for example : When you sent to me 30 tons by 200$ and other 10 tons by 220$ it tells me you have 10 tons by 200$ He didn't sent to you .

[Bulzie]

Sorry still don't understand what you are asking. Maybe someone else can see what is needed? Need to see your data table/field structure and sample data in a column/row format.

So you have a table with something like:

Product, Size, Quanitty (tons), Price, DateOrdered, DateDelivered
Iron, 1.50mm, 50, $200, 1/1/2016, 2/1/2016
Iron, 2.00mm, 100, $300, 10/1/2016, 12/1/2016

And what is it you want to do?

[Mehvan]

thanks a lot for you help

But It wasn't my meaning ,,, maybe my English isn't very good , because of that you can't understand me well

Goods Name	Tons	Price	total
pipe 20*20*1.50	20	$ 200	$ 4,000
pipe 25*25*1.50	10	$ 200	$ 2,000
pipe 40*80*1.50	30	$ 200	$ 6,000
pipe 50*100*1.50	20	$ 200	$ 4,000
pipe 40*80*2	40	$ 180	$ 7,200
pipe 50*100*2	30	$ 180	$ 5,400
pipe 100*100*2	10	$ 180	$ 1,800
pipe 20*40*1	15	$ 220	$ 3,300
pipe 40*40*1	20	$ 220	$ 4,400
pipe 30*30*1	10	$ 220	$ 2,200

When several of this order come to me , I will enter this order even they appear in Inventory
and When I enter them automatically be less ( tons , price and total )
for example : pipe 40*80*1.50 come to me ( 20 tons ) it's automatically tells me , you have ( 10 tons ) didn't come till now to you .

[CJ_London]

going back to your original question

How I will know When you sent to me my orders , I will know price and tons are identical ?

you should know because when you sent the order to the supplier, you would have provided them with an order number. When they send goods to you, they would include that order number in the paperwork.

in a simple setup, your tables might look like this

tblOrders
OrderPK autonumber
OrderNum text/number
OrderDate date
SupplierFK long link to supplier table



tblProducts
ProductPK autonumber
ProductDesc text


tblTransactions
TransactionPK autonumber
TransactionDate date
TransactionType text (order/goods in/goods issued/stock adjustment etc)
OrderFK long link to tblOrders (only complete for an order or goods in for an order)
ProductFK long - link to tblProducts
Qty number
Price currency

TransactionPK..TransactionDate..TransactionType..O rderFK..ProductFK..Qty..Price
1 01/01/2016........order.................3...........4... ...........10....$200
2 01/01/2016........order.................3...........5... ...........20....$100
3 01/02/2016........goods in.............3...........4..............5....$20 0
4 01/02/2016........goods in.............3...........5..............12....$1 00
5 01/03/2016........goods issued.......0...........4..............-3....$200

query to determine outstanding orders would be something like

SELECT O.OrderFK, O.ProductFK, TransactionDate, Price, Qty, Recd, Qty-Recd as Outstanding
FROM tblTransactions O LEFT JOIN (SELECT OrderFK, ProductFK, sum(Qty) as Recd FROM tblTransactions WHERE TransactionType='goods in' GROUP BY OrderFK, ProductFK) R
    ON O.OrderFK=R.OrderFK AND O.ProductFK=R.ProductFK
WHERE TransactionType='order'

Query to determine current stock would be something like

SELECT ProductFK, sum(qty) as currentstock
FROM tblTransactions
WHERE transactionType<>'order'
GROUP BY ProductFK

[Mehvan]

Thank you so much for your help Mr.Ajax

But I didn't understand Code well

I created Tables , Query and Relationship





I'm sorry to ask you a lot of questions ,, I'm new here and I don't have many information about Microsoft Access
and I love Microsoft Access so much .

[Mehvan]

And also this is query I made
But I didn't understand it well

[CJ_London]

But I didn't understand Code well

other than the slight naming differences - (transaction>transcustion/transuction, Ordernum>ordername etc) you should just be able to copy and paste the code I provided.

[Mehvan]

thanks a lot
but I should write tables smilar your tables
even code works correct

and also these code , I will enter in Forms When I created forms for these tables .

[CJ_London]

sorry, you've lost me

rule is one table one form.

main form tblorders
subform tblsuppliers to display supplier information (single form)
subform tbltransactions to display and enter order lines - this will include a combo to select the product

take a look at some of the access templates to see how they work

[Mehvan]

okay
thank you for your help