G
Guest
The short question:
How do I allow the user to select individual records to be combined into a
single new record in a table? Two fields need to change in the new record,
the ID # (primary key) needs to be concatenated and the Length field needs to
be summed. The rest of the fields match.
The long explanation:
This is our second attempt at implementing a material management database.
The first attempt made us read up on database design, more clearly define the
expected tasks, identify the data, and flowchart the process. I think we are
ready for another foray in to database development.
Our small business designs and manufactures timber frames using a 3-d
AutoCAD environment and a large timber CNC machine to mill the appropriate
parts. We’ve been using the vendor supported “export to excel†functionality
and the resultant spreadsheet to manage our timber order lists but it really
isn’t the right tool for what we would like to accomplish.
A brief, simplified explanation of our process would be in order. A design
is created in AutoCAD using timber entities that have properties for ID #,
thickness, width, length, material, grade, and a number of user defined
fields. The nature of the project design often means that there are a number
of short parts in the AutoCAD environment that are too short to order from a
material supplier. A number of these parts need to be “combined†together
into pieces that are at least 10’ long. I’ll call the individual timber
entities in the AutoCAD model “parts†and the individual timbers in the
purchase order “piecesâ€. Most pieces correspond to individual parts, but
some are comprised of multiple parts. This combined list then needs to be
compiled into a subtotaled purchase order that shows a quantity for each
unique piece type instead of a list of individual pieces. When the timbers
arrive at the shop, they are in an unsorted and unlabeled pile so we need to
figure out which pieces are which parts. In order to do this we need to see
a list of timbers sorted similar to the purchase order, but showing the ID’s.
To accomplish this, currently we use the cad software’s “export to excelâ€
functionality, then import this into an access table. Each project/AutoCAD
model has its own material table in the database. The user then manually
combines parts (records) into pieces by concatenating the ID #’s and adding
the lengths, creating a new record for this combined piece and deleting the
original part records. As you can probably imagine, this is a tedious
process that is prone to error. A query then returns a compiled list of
pieces that is then emailed to our various vendors for a quote.
Since the design is often being modified by clients after our initial export
we need to be very diligent about the order status of our individual pieces
and the cad model needs to be routinely compared to the database table.
In order to accommodate all this we intend to “link†an AutoCAD model with a
database table. This needs to be a 1-1 relationship in order to efficiently
handle the constant reconciliation between the model and the database table.
This reconciliation will be handled with VBA called from the AutoCAD
environment and can be ignored in this discussion.
The next step is to create a “combined†table that is used to generate the
purchase order. This is the essence of my short question above. I need to
use access to help us combine short part records into order length piece
records in this table. For a number of reasons I’d like the user to select
the pieces to be combined rather than have the computer figure it out for us.
For this functionality I envision a module that displays all of the part
records under 10’ in length as a list that allows the user to select which
parts they would like to combine. Maybe checkboxes for the selection of
parts? The user would then click a “Combine Pieces†button that would create
a new record that concatenated the ID’s with a coma between each ID and
summed the length. The original part records would then be deleted. We’ll
need to build some functionality in to routinely compare the linked table
with this combined table but I think that can be addressed separately from
this discussion.
Any advise?
How do I allow the user to select individual records to be combined into a
single new record in a table? Two fields need to change in the new record,
the ID # (primary key) needs to be concatenated and the Length field needs to
be summed. The rest of the fields match.
The long explanation:
This is our second attempt at implementing a material management database.
The first attempt made us read up on database design, more clearly define the
expected tasks, identify the data, and flowchart the process. I think we are
ready for another foray in to database development.
Our small business designs and manufactures timber frames using a 3-d
AutoCAD environment and a large timber CNC machine to mill the appropriate
parts. We’ve been using the vendor supported “export to excel†functionality
and the resultant spreadsheet to manage our timber order lists but it really
isn’t the right tool for what we would like to accomplish.
A brief, simplified explanation of our process would be in order. A design
is created in AutoCAD using timber entities that have properties for ID #,
thickness, width, length, material, grade, and a number of user defined
fields. The nature of the project design often means that there are a number
of short parts in the AutoCAD environment that are too short to order from a
material supplier. A number of these parts need to be “combined†together
into pieces that are at least 10’ long. I’ll call the individual timber
entities in the AutoCAD model “parts†and the individual timbers in the
purchase order “piecesâ€. Most pieces correspond to individual parts, but
some are comprised of multiple parts. This combined list then needs to be
compiled into a subtotaled purchase order that shows a quantity for each
unique piece type instead of a list of individual pieces. When the timbers
arrive at the shop, they are in an unsorted and unlabeled pile so we need to
figure out which pieces are which parts. In order to do this we need to see
a list of timbers sorted similar to the purchase order, but showing the ID’s.
To accomplish this, currently we use the cad software’s “export to excelâ€
functionality, then import this into an access table. Each project/AutoCAD
model has its own material table in the database. The user then manually
combines parts (records) into pieces by concatenating the ID #’s and adding
the lengths, creating a new record for this combined piece and deleting the
original part records. As you can probably imagine, this is a tedious
process that is prone to error. A query then returns a compiled list of
pieces that is then emailed to our various vendors for a quote.
Since the design is often being modified by clients after our initial export
we need to be very diligent about the order status of our individual pieces
and the cad model needs to be routinely compared to the database table.
In order to accommodate all this we intend to “link†an AutoCAD model with a
database table. This needs to be a 1-1 relationship in order to efficiently
handle the constant reconciliation between the model and the database table.
This reconciliation will be handled with VBA called from the AutoCAD
environment and can be ignored in this discussion.
The next step is to create a “combined†table that is used to generate the
purchase order. This is the essence of my short question above. I need to
use access to help us combine short part records into order length piece
records in this table. For a number of reasons I’d like the user to select
the pieces to be combined rather than have the computer figure it out for us.
For this functionality I envision a module that displays all of the part
records under 10’ in length as a list that allows the user to select which
parts they would like to combine. Maybe checkboxes for the selection of
parts? The user would then click a “Combine Pieces†button that would create
a new record that concatenated the ID’s with a coma between each ID and
summed the length. The original part records would then be deleted. We’ll
need to build some functionality in to routinely compare the linked table
with this combined table but I think that can be addressed separately from
this discussion.
Any advise?