need to combine 2 table rows into 1

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi I'm pretty new to Access but have done a bit of macro programming in Excel, autocad lisp and a specialized toolscripting language for CNC machines

I've got a pretty simple database with an individual table (for now) that I need to manage a list of timbers. The database gets populated by importing a excel spreadsheet which itself is created by exporting from an autocad model. The Table has the following fields
ID (this number identifies which timber it is in the autocad model
fT (Finished Thickness- for a 2x8 this would be 1.5
fw (Finished Width- for a 2x8 this would be 7.5
L (Length
Specie
Grad
Cut

One of my first hurdles is to create a list of timbers to order from the mill. Since the mill does not cut any timbers less than 10' long, I need to combine any 2 or 3 individual shorter pieces into a single longer piece while still keeping track of my ID's of the smaller pieces

My idea of the most convienent method from a user standpoint is to have a query or filter return a table of the pieces that are less than 10' in length. A macro would then prompt the user to select 2 records (pieces) they would like to combine. After the user selects the two pieces, the macro would create a new record that added the L values, concatenate the ID values and delete the original 2 records

Any ideas on how this would be best accomplished?
 
Lumpy,

I would do it fairly similar, but not quite. Here's my suggestion...
Add 2 new fields to your table:
a Yes/No data type, let's call it Combine
a Number data type, let's call it CombineID

OK, you can easily make a query based on this table to find those
timbers less than 10', as you suggested, just via a simple criteria in
the L field in the query. Make a continuous view form, based on this query.

At this point, you can then select those timbers you will combine to
make a 10+' piece for ordering, by ticking the Combine checkbox.

You can make a simple Update Query to update the value of the CombineID
field for all records where Combine = -1 to...
Nz(DMax("[CombineID]","YourTableName"),0)+1

You can make another simple Update Query to update the value of Combine
to 0.

I would put a command button on the form header section. Clicking this
button will run these two Update Queries. If you want to do this via a
macro, you will use the OpenQuery macro action.

Then, when you are preparing your order, base it on a Totals Query,
where you make a calculated field like this...
OrderLine: Nz([CombineID],[ID])
Group By this field, and also the other fields except L, which will be Sum.

Hope this sort of makes sense.
 
Back
Top