conjunction Table Data Entry

[raffi]

hi, lets say i have 2 tables, "Items" and "Locations" and i created one to many relation from each table to 1 conjunction table, and Added one extra field called quantity into the conjunction table, so now the conjunction table has 4 Fields, ID AutoNumber and "Item" "location" "quantity" Fields, ok and i turn it into query then put the fields in form and use it as a data entry



im looking for a way, where if I choose an item From the item combobox, the locations where this item has quantity in it, would be available in the locations combobox, so i can add on the existing quantities

or to make it eazier, when i choose an item, it would let me choose all the location names which have this item next to it, so i may ( edit and add) the previously set quantity field

[June7]

If you want to create records in conjunction table, use a form bound to table and comboboxes to select Item and Location then enter quantity.

Why would you need to change the quantity of all existing conjunction records for the same location and item?

[raffi]

i want to add new items which have arrived into a warehouse, and the warehouse have shelves, and shelves have names such as "A1 L1" "A2 L1" so im trying to merge items with shelve names, some items go into same shelves and others different new shelves, and i have to add the quantity of the items into already available ones or create new shelf for new item which is a new record in the junction table

[June7]

Sorry don't understand the issue. If you want a new record then enter a new record.

[raffi]

ok what im trying to do is much like the form i created named frmStockIn but with TblStockInLoc but this time instead of the SupplierName i want to add items to locations and with quantities

in frmStockIn when i add a quantity to an item it is being saved, when i select the same item it would show me how much Quantity is available from that item.

but when i created FrmStockInLoc im faced with a situation where many items can have 1 location and many locations can have 1 item so im not sure how to save the locations to items and items to locations in a way so i can add quantities

[June7]

Your data structure doesn't make sense to me. Consider:

tblLocations

tblSuppliers

tblItems
SupplierID

tblStock
TransactionDate
TransactionType
ItemID
LocationID
Quantity

Might find this of interest http://allenbrowne.com/AppInventory.html

Might also look at the MS Desktop Product Inventory database template.