Specific and general Access questions.


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?
 
Ad

Advertisements

J

Joseph Meehan

Kurt wrote:

IF (notice the big if) I understand correctly, you really want to
combine the records into a single table and use that. See if this looks
right:

Table 1

ProjectField1 ProjectField2
A B
C D

Table 2

ProjectField1 PartField1 PartField2 ...
A AA BB
B CC DD

These combined into

New Table

ProjectField1 ProjectField2 PartField1 PartField2 ...
A B AA BB
C D CC DD

If so, you would create the new table with all the fields and then use
an update query to populate them
 
G

Guest

I don't think so Joseph. Let's just start as one table that needs to be
modified.

TableOriginal
PieceID Length
101 4'
102 6'
103 14'

TableAfterCombiningSmalls
PieceID Length
101,102 10'
103 14'


This is the super simplified list. In reality this list is a couple hundred
records long and the user needs to select the records to be combined each
time.

In the TableOriginal, pieces 101 & 102 are too short to order and need to be
combined into a longer timber. Piece 103 is over 10' and does not need to be
manipulated. TableAfterCombiningSmalls reflects the same table after the
user has selected records 101 & 102 and clicked the "Combine Parts" button.
 
Ad

Advertisements

G

Guest

Hi Kurt,

I don't think deleting original data, as you have envisioned, is necessarily
a good idea. For the combined new part number, {101,102}, you will only know
that the sum of the lengths is 10 feet, but you will have lost their
individual lengths.

It seems to me that you have a one-to-many (1:M) relationship, ie.:
"A combined part can include many small parts"

You may even have a many-to-many (M:N) relationship, if the small parts are
used more than one time, ie.:
"A small part can belong to many larger combined parts"

Assuming a M:N relationship, this would require a third table to resolve two
1:M relationships. I would envision a situation where you would create a new
large part, which would be shown in the top half of a form. You could use a
subform to display the component parts that make up this combined part. A
command button on the form could be used to open a new form, which included
two multi-select list boxes. The left side list box would include the
individual parts required to fill an order. You could select one or more
parts, click a button to move the selected parts from the left side listbox
to the list box on the right hand side. Meanwhile, you could have code that
summed the added lengths and presented this information to the user as they
moved parts from the left side list box to the right side list box.

When you were satisfied with your selections, you would click an OK button,
which would run code to populate the linking table with the selections that
were made in the right-side list box. I would assign a new primary key to the
combined part. I would not attempt to concatentate these values for use as a
primary key. You can always use VBA code to make a comma separated list
whenever you want, but I would not recommend storing this result, as it would
be considered a derived (or calculated) result.

An example of working with two listboxes, as described above, can be found
on Helen Fedemma's web site: http://www.helenfeddema.com/access.htm. Look
for article # 72, "Paired Multi-Select Listboxes".


Tom
_____________________________________

:

I don't think so Joseph. Let's just start as one table that needs to be
modified.

TableOriginal
PieceID Length
101 4'
102 6'
103 14'

TableAfterCombiningSmalls
PieceID Length
101,102 10'
103 14'


This is the super simplified list. In reality this list is a couple hundred
records long and the user needs to select the records to be combined each
time.

In the TableOriginal, pieces 101 & 102 are too short to order and need to be
combined into a longer timber. Piece 103 is over 10' and does not need to be
manipulated. TableAfterCombiningSmalls reflects the same table after the
user has selected records 101 & 102 and clicked the "Combine Parts" button.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top