J
Joe
I am attempting to create an inventory tracking database and having
some problems with creating the structure for it. There are 2 primary
issues that I am unsure how to resolve and wondering if anyone could
provide some input.
First, has to do with how to record an entry. I need to provide the
background of the form design to properly frame the question. The
database is meant to track as much as 500 inventory parts as they are
transferred from one job location to another job location. Each time
inventory is added or moved a ticket is created outlining the location
the inventory came from and where it went to and in what quantity.
Each ticket can contain multiple part transfers. New inventory is
also added via a ticket and always comes from one of 2 locations a
yard where it is stored or a lease company.
The input form for the Ticket entry has a main form and sub form. The
main form allows a user to record a ticket # (auto #) and date that
tracks the overall ticket transaction. The sub form provides a place
to select a part# to be transferred, quantity of transfer, "From job
#” and "To job #”. This entry needs to serve as the basis for 2
entries; what job # had a “Transfer From” (reduction in inventory) and
the other as what job number had “Transfer To” (increase in inventory)
and record the date.
This is where I am getting stuck:
- There are 500 part numbers. For every new job I would have to
update my inventory table to have 500 records matching the 1 new job #
with the 500 part #’s and the balance of each part would have to be
set to “0”. I am not sure; if this is a great way to configure my
table records as over time there will be too many records in this one
table, and if this is the right table structure how do I create a new
recordset for each new job.
- Inventory balances need to be increased and reduced via 1
transaction. Specifically, the ticket # form allows users one line on
the sub form per transfer where they enter the part # and the To Job#"
and "From job #". For example; Ticket # 1 has a transfer of 100 units
of part # abc, going to job 123, from job 456. This entry should
record 2 transactions in the inventory table; an increase in inventory
for job 123 and a decrease in inventory for job 456 for the same part
#.
- Of lesser importance and more difficulty; I also need a report that
can tell me whenever a Jobs inventory changes how many days was the
balance of inventory held before the change occurred? For example if
starting inventory on 7/1 is 100 units and is reduced by 20 units on
7/15, I need to know that for 14 days this location held 100 units.
This quantity will be used to generate a cost calculation. This is
tough (impossible?) to calculate for a report on the fly. So I am
assuming an entry needs to be in some table.
I am pretty sure that my data tables are not setup correctly; I have a
Parts, Ticket and Inventory Transfer table. I did not create an
inventory table as of yet. I was trying to store all the ticket
transactions in the Inventory Transfer table and then perform query
calculations to get the right balance but that did not work as there
is no starting inventory balance and access hates calculations that
resolve to null.
Any help or links to help would be much appreciated.
Thanks
some problems with creating the structure for it. There are 2 primary
issues that I am unsure how to resolve and wondering if anyone could
provide some input.
First, has to do with how to record an entry. I need to provide the
background of the form design to properly frame the question. The
database is meant to track as much as 500 inventory parts as they are
transferred from one job location to another job location. Each time
inventory is added or moved a ticket is created outlining the location
the inventory came from and where it went to and in what quantity.
Each ticket can contain multiple part transfers. New inventory is
also added via a ticket and always comes from one of 2 locations a
yard where it is stored or a lease company.
The input form for the Ticket entry has a main form and sub form. The
main form allows a user to record a ticket # (auto #) and date that
tracks the overall ticket transaction. The sub form provides a place
to select a part# to be transferred, quantity of transfer, "From job
#” and "To job #”. This entry needs to serve as the basis for 2
entries; what job # had a “Transfer From” (reduction in inventory) and
the other as what job number had “Transfer To” (increase in inventory)
and record the date.
This is where I am getting stuck:
- There are 500 part numbers. For every new job I would have to
update my inventory table to have 500 records matching the 1 new job #
with the 500 part #’s and the balance of each part would have to be
set to “0”. I am not sure; if this is a great way to configure my
table records as over time there will be too many records in this one
table, and if this is the right table structure how do I create a new
recordset for each new job.
- Inventory balances need to be increased and reduced via 1
transaction. Specifically, the ticket # form allows users one line on
the sub form per transfer where they enter the part # and the To Job#"
and "From job #". For example; Ticket # 1 has a transfer of 100 units
of part # abc, going to job 123, from job 456. This entry should
record 2 transactions in the inventory table; an increase in inventory
for job 123 and a decrease in inventory for job 456 for the same part
#.
- Of lesser importance and more difficulty; I also need a report that
can tell me whenever a Jobs inventory changes how many days was the
balance of inventory held before the change occurred? For example if
starting inventory on 7/1 is 100 units and is reduced by 20 units on
7/15, I need to know that for 14 days this location held 100 units.
This quantity will be used to generate a cost calculation. This is
tough (impossible?) to calculate for a report on the fly. So I am
assuming an entry needs to be in some table.
I am pretty sure that my data tables are not setup correctly; I have a
Parts, Ticket and Inventory Transfer table. I did not create an
inventory table as of yet. I was trying to store all the ticket
transactions in the Inventory Transfer table and then perform query
calculations to get the right balance but that did not work as there
is no starting inventory balance and access hates calculations that
resolve to null.
Any help or links to help would be much appreciated.
Thanks