D
DougW via AccessMonster.com
I am a very sporadic Access user and could use some guidance for a new task.
I have an existing Access DB I am using to report on parts we have ordered.
There are many different part types.
We often order hundreds of copies of the same part (type) on one purchase
order.
I refer to those copies of parts as "Ordered Items".
I have one table for Purchase Orders and another table for Ordered Items.
The OrderedItems table
contains a unique record for each "copy" of each part. There is a one-to-
many relationship between PurchaseOrders and OrderedItems.
Now I need to add another layer of information, to allow tracking the status
of OrderedItems over time.
I want to be able to report on completion status of parts, and physical
location (such as when they are shipped to a warehouse). Over time there
will be multiple "tracking" records created for a given OrderedItem. Also
it is very likely that one tracking record will pertain to many OrderedItems.
My thought was to create a new table called OrdItemTracking. The records in
this table would include the date of the tracking record, the physical
location of the parts, the physical status, etc.
I also propose to add another new table called OrdItemTrackArray. The
records in this table would include the primary key from one OrderedItem
record and the primary key from one OrdItemTracking record. I thought this
Array table would allow me to link one Tracking record to any number of
OrderedItems records.
To clarify my question, I have 4 tables of interest.
PurchaseOrders
OrderedItems
OrdItemTracking
OrdItemTrackArray
Viewing it from left to right in my relationship window, I first have
PurchaseOrders.
To the right of PurchaseOrders is OrderedItems
Below OrderedItems is OrdItemTracking
To the right of OrderedItems is OrdItemTrackArray
There is a one-to-many relationship from the primary key of PurchaseOrders to
a foreign key in OrderedItems.
There is a one-to-many rel from the PK in OrderedItems to a FK in
OrdItemTrackArray
There is also a one-to-many rel from the PK in OrdItemTracking to a FK in
OrdItemTrackArray.
I think my table design makes sense (and please advise if it doesn't, I won't
be offended!)
Now I need a means to select a large number of similar OrderedItems from one
PurchaseOrder, create a single OrdItemTracking record, and "attach" it to all
of the selected OrderedItems by creation of a number of OrdItemTrackArray
records.
For example, PurchaseOrder A may link to 100 OrderedItems. I wish to create
one OrdItemTracking record and then select 35 of the 100 OrderedItems and
"attach" the Tracking record to all of them (via creation of 35 new records
in the Array table). This might reflect that those 35 OrderedItems were
shipped to Warehouse B on Sept 15 2008 (for an example).
As far as the data entry form, I would like to select the PurchaseOrder,
generating a list of OrderedItems, and then select multiple individual
OrderedItems from this list - create a single new OrdItemTracking record -
then (hopefully by clicking a single "action button"), create multiple new
records in OrdItemArray to link the individual OrderedItems to the new
Tracking record.
Searching previous threads on this site leads me to believe I may need to use
a form with an embedded subform (?)
I'm thinking the creation of the new Array records will be from an append
query.
I have done that before and am somewhat familiar with that technique.
It's not a show-stopper if I have to first create the new Tracking record
from it's own form.
For the moment my main concern is how to enable the multiple selection of
"some-but-not-all" of the OrderedItems from one particular PurchaseOrder, and
then make use of those multiple selections to drive my Append query.
I would appreciate any suggestions or maybe direction to a similar example
that might be out there.
I hope this wasn't too rambling - thanks in advance for your help!
-- Doug
I have an existing Access DB I am using to report on parts we have ordered.
There are many different part types.
We often order hundreds of copies of the same part (type) on one purchase
order.
I refer to those copies of parts as "Ordered Items".
I have one table for Purchase Orders and another table for Ordered Items.
The OrderedItems table
contains a unique record for each "copy" of each part. There is a one-to-
many relationship between PurchaseOrders and OrderedItems.
Now I need to add another layer of information, to allow tracking the status
of OrderedItems over time.
I want to be able to report on completion status of parts, and physical
location (such as when they are shipped to a warehouse). Over time there
will be multiple "tracking" records created for a given OrderedItem. Also
it is very likely that one tracking record will pertain to many OrderedItems.
My thought was to create a new table called OrdItemTracking. The records in
this table would include the date of the tracking record, the physical
location of the parts, the physical status, etc.
I also propose to add another new table called OrdItemTrackArray. The
records in this table would include the primary key from one OrderedItem
record and the primary key from one OrdItemTracking record. I thought this
Array table would allow me to link one Tracking record to any number of
OrderedItems records.
To clarify my question, I have 4 tables of interest.
PurchaseOrders
OrderedItems
OrdItemTracking
OrdItemTrackArray
Viewing it from left to right in my relationship window, I first have
PurchaseOrders.
To the right of PurchaseOrders is OrderedItems
Below OrderedItems is OrdItemTracking
To the right of OrderedItems is OrdItemTrackArray
There is a one-to-many relationship from the primary key of PurchaseOrders to
a foreign key in OrderedItems.
There is a one-to-many rel from the PK in OrderedItems to a FK in
OrdItemTrackArray
There is also a one-to-many rel from the PK in OrdItemTracking to a FK in
OrdItemTrackArray.
I think my table design makes sense (and please advise if it doesn't, I won't
be offended!)
Now I need a means to select a large number of similar OrderedItems from one
PurchaseOrder, create a single OrdItemTracking record, and "attach" it to all
of the selected OrderedItems by creation of a number of OrdItemTrackArray
records.
For example, PurchaseOrder A may link to 100 OrderedItems. I wish to create
one OrdItemTracking record and then select 35 of the 100 OrderedItems and
"attach" the Tracking record to all of them (via creation of 35 new records
in the Array table). This might reflect that those 35 OrderedItems were
shipped to Warehouse B on Sept 15 2008 (for an example).
As far as the data entry form, I would like to select the PurchaseOrder,
generating a list of OrderedItems, and then select multiple individual
OrderedItems from this list - create a single new OrdItemTracking record -
then (hopefully by clicking a single "action button"), create multiple new
records in OrdItemArray to link the individual OrderedItems to the new
Tracking record.
Searching previous threads on this site leads me to believe I may need to use
a form with an embedded subform (?)
I'm thinking the creation of the new Array records will be from an append
query.
I have done that before and am somewhat familiar with that technique.
It's not a show-stopper if I have to first create the new Tracking record
from it's own form.
For the moment my main concern is how to enable the multiple selection of
"some-but-not-all" of the OrderedItems from one particular PurchaseOrder, and
then make use of those multiple selections to drive my Append query.
I would appreciate any suggestions or maybe direction to a similar example
that might be out there.
I hope this wasn't too rambling - thanks in advance for your help!
-- Doug