J
Joe
One Form Entry / One Table / Multiple Record Updates
Hello All,
I am building an application that requires having a form where a
single line entry will record multiple records to one table and I am
wondering if / how this can be done. Here are the particulars.
There is a main form that has Ticket # and Date fields and a sub form
that allows the user to transfer inventory parts from one job to
another. The sub form has the following fields; Job# from, Job # to,
Part # and Quantity. The sub form can have multiple line items that
represent a transfer of inventory from one job to another.
For each line item on the sub form, I would like to make 2 entries
into my “Inventory Transaction” table. The first entry would record
Job # from, Part #, Quantity (negative value) and Date. The second
entry would record Job # To, Part#, Quantity (positive value) and
Date.
As mentioned, the sub form can contain multiple line items. For each
line item on the sub form I need to record 2 entries. For example, if
the sub form had 4 entries (Four to/from transfers) I would need a
total of 8 entries (4 line items * 2 entries)
Is this possible? If so, here is the other part of the problem.
I then need to use my “Inventory Transaction” table in conjunction
with my “Inventory” table to calculate a ‘point-in-time’ inventory
balance fo each job/part # combination.
For example, if I started with 100 units of Part A on Job #1 as of
7/1/09 and then transferred 50 units to a different job on 7/15/09; I
need to know that there were 100 units of Part A on Job#1 from for 14
days 7/1 – 7/15). This value will be used on a report to determine a
cost calculation.
In essence each time inventory (job/part combo) is changed I need to
be able to determine what quantity of the part was on the job for how
many days so I can bill the customer for usage of that. This
information is needed on a historical basis.
I know this is fairly complex so I am trying to get an understanding
of if this can be done and how this would be best approached.
Thanks, Joe
Hello All,
I am building an application that requires having a form where a
single line entry will record multiple records to one table and I am
wondering if / how this can be done. Here are the particulars.
There is a main form that has Ticket # and Date fields and a sub form
that allows the user to transfer inventory parts from one job to
another. The sub form has the following fields; Job# from, Job # to,
Part # and Quantity. The sub form can have multiple line items that
represent a transfer of inventory from one job to another.
For each line item on the sub form, I would like to make 2 entries
into my “Inventory Transaction” table. The first entry would record
Job # from, Part #, Quantity (negative value) and Date. The second
entry would record Job # To, Part#, Quantity (positive value) and
Date.
As mentioned, the sub form can contain multiple line items. For each
line item on the sub form I need to record 2 entries. For example, if
the sub form had 4 entries (Four to/from transfers) I would need a
total of 8 entries (4 line items * 2 entries)
Is this possible? If so, here is the other part of the problem.
I then need to use my “Inventory Transaction” table in conjunction
with my “Inventory” table to calculate a ‘point-in-time’ inventory
balance fo each job/part # combination.
For example, if I started with 100 units of Part A on Job #1 as of
7/1/09 and then transferred 50 units to a different job on 7/15/09; I
need to know that there were 100 units of Part A on Job#1 from for 14
days 7/1 – 7/15). This value will be used on a report to determine a
cost calculation.
In essence each time inventory (job/part combo) is changed I need to
be able to determine what quantity of the part was on the job for how
many days so I can bill the customer for usage of that. This
information is needed on a historical basis.
I know this is fairly complex so I am trying to get an understanding
of if this can be done and how this would be best approached.
Thanks, Joe