Can this be done?

[sicSRT8]

Is this possible?

I have two tables Job Entry and Inventory.

I select Item # from Drop down of available items in inventory. It auto populates qty avail in fields Item#1 Item#2 Item#3.

Underneath each item is a field for data entry to input amount used on job for that specific item.

How do i get it to update the inventory with the correct amount left. If I use a field called cRemaining for each row on Job entry page it will show the remaining amount in all three instances for each item.

I need to be able to pull up that item again in Item#3 with the correct amount on the same job. Basically Im recording when an item was used in a job which is why I am doing it this way.

Thanks in advance

Example: <...> Signifys Fields

-------Job Entry Table------------------

<-Drop Downs from avail inventory.

------Inventory Table------

<----------How can i get this to update based on the item selected from the job entry? I can get everything to work but this one thing.

Edited February 17, 2009 by Guest

[bcooney]

Your structure is completely incorrect, sorry. Search this forum for inventory samples. You should have at minimum, the following tables:

Customers (or People)

Jobs

JobLineItems

Inventory

most likely will also need...

PO

Vendors (using People)

Adjustments

Receipts (same table as Adj)

Once you have the correct table setup, then you can calc the qty's in inventory.

Edited February 22, 2009 by Guest

[LaRetta]

It auto populates qty avail in fields Item#1 Item#2 Item#3.

As Barbara says, your structure isn't correct. In addition to lack of tables, you are using multiple fields where records should exist. Any time you find yourself creating multiple 'like' fields, it probably indicates improper structure and if these items are lineitems on an job, it CERTAINLY points to incorrect structure.

Item#1 Item#2 Item#3 should be individual related records. Otherwise, you 1) cannot create proper summary reports, 2) you have to add more fields if the number of items increases and 3) you run up against problems when wanting to search or segregate/update only a specific item (as you are struggling with now).

Seriously, get your base structure right. People here on FM Forums can help you through the transition if you get stuck on creating the proper relationships. :smile2:

Edited February 22, 2009 by Guest
Changed incorrect to correct. ;-/

[Vaughan]

I call these "you can't get there from here" designs. Classic rookie trap.

All of the data is in there -- I can see it, for goodness sake, it's right there <¡pointing wildly!> -- but you can't do anything with it!

Do what Barbara* and LaRetta recommend. They know. With them, the Force is.

*I was watching an episode of Scrubs last night on dvd and there is a scene where Dr Kelso says to Dr Cox "You're like House without the walking stick." Cracked me up. I love that show. Absurdly serious.