lookup in query

[steve111]

hi
I have attached part of my database as an example
what I have been trying to do without success is get a "allocated qty " next to and "order qty"

the form "alloaction1" has a "material " and an " order no " field as well as an "qty allocated" field.
the idea is when booking a part out of the stores it is allocated to the order no and qty recorded

in the query " allocated" it shows all the materials required to fulfil ALL orders along with the " batch no" these fields are in the table ( order details)
what I want if possible is another formula in that query that will put the "allocated qty" along side the order qty

therefore
in that query I want to see all the 3444 records with the allocated qty ie 2 next to the record below

 [FONT=Arial][COLOR=#000000] [B]allocated[/B][/COLOR][/FONT]   [FONT=Arial][COLOR=#000000]Qty[/COLOR][/FONT] [FONT=Arial][COLOR=#000000]BatchNo[/COLOR][/FONT] [FONT=Arial][COLOR=#000000]StockNumber[/COLOR][/FONT] [FONT=Arial][COLOR=#000000]Material[/COLOR][/FONT]   [FONT=Arial][COLOR=#000000]14.80[/COLOR][/FONT] [FONT=Arial][COLOR=#000000]PCO0095[/COLOR][/FONT] [FONT=Arial][COLOR=#000000]2096B1341[/COLOR][/FONT] [FONT=Arial][COLOR=#000000]Epoxy Hardener HX701C/NC (185kg drum)[/COLOR][/FONT]

then 2



any help appricated
steve


[

 

[jdraw]

It appears that some tables (Customers) is not present in this copy of the data base.

 

[steve111]

hi,

file attached with customer table included

 

[jdraw]

Steve,

Do you not have a working data model for this database? I see a number of tables (Allocation, Customers and StockList, Types, Product Details, Order Details) - and queries (What if, Allocated) but nothing that seems to bring these together.
Is the stocknumber from some recognized codification scheme?

I'd like to help, but there seems to be a lot of background that readers would have to guess at to provide and focused comments.

I see you have 165 posts in this forum, so can you tell us a bit about the database and the business it is meant to support. Getting the tables and relationships set up to support the business is key.

 

[steve111]

hi,

bit of background
I inherited this database as the creator has left the company . I only know little about access but can get by

what do we do , what did it do , and what have I done.

we make capacitors. the systems used was access and excel and bits of paper but I suggested to them we should not if possible use both systems and put in all in access

what did it do, it creates sales orders, using the tables / orders, order details, stocklist,
the assemblies are defined by tables , "products" and "product parts" selected from the stocklist

the query looks at the orders , identifies what products are required and them populates the parts from the stocklist. to cover the orders on the system.

what we look at is all the orders up to 3months in advance so we can order more parts in at the same time

the query looks at all orders up to 3 months in advance and totals the quantity of all the parts required , it the looks at the "stock" level,"onorder" level and comes up with the amount to purchase , so if 100 of part "a" are required to cover all orders up to 3 months and there is 20 in stock and 20 already on order the query would say order 60

then they would have done this purchase order for the parts in excel as would be the order invoice, and acknowledgment of the order

what am I doing

I have now appended the parts from the query to a table so we now have a record.
from that I create a requisition then a purchase order . now all in access

as the system always runs on "order qty" - "stock"+"on order qty"
so stock needs to be kept up todate all the time

as I don't know how to adjust the stock level automatically we are doing it manually
ie qty booked in after purchase and then when allocated for an order to the shop floor to assemble

so example

100 orders to satisfy could mean 10000 parts required of which are repeated per order.
Each order has a product number therefore all 10000 parts will have a product number assigned to it

when allocating the part to an order it is done by the product number ( there is a table /form I have created)

so I would like to know whats the qty of parts have I purchased . what have I allocated what was the order requirement against each product no.
then attach a Subdatasheet that tells me what has been the total requirement for parts for all orders, total allocation.






as I don't know how to tell the system to alter the stock qty when parts booked in or out of the stores I wanted to create is something I can just check. this would then tell me if the stock qty is correct against parts order / parts allocated

hope this helps

steve

 

[Clifton]

Have you tried looking at the northwind database. This will show you code which updates your stock automatically as orders are created and new stock comes in