How can I select line items from a query and append them into another table?

[DanicaDear]

I work in a steel fabrication shop. I am trying to use an Access Database (2010) to create a program to replace some dilapidated software currently in use. I have some ideas of what I want to do (BIG picture) but I think it’s always good to run it by an expert. I have questions about HOW to get where I want to go.

There are mainly three pieces to the puzzle.

tblShopOrder is where the job number is stored with information on the job (customer and job name, etc). I think it will end up having BillNumbers attached to it as well, as will soon be discussed.

tblBillNumber is where we store the bill of material for the drawings. This is identified by a drawing number such as A123456 or SGP589251. In this table we store very little info, basically the name of the drawing with the drawing number. I will talk more about this in a moment.

tblSheetDetails is where we enter in all of the material required for the BillNumber. (Example, we need qty 3 of 5x5 tubes @ 15’ long each, 30 half-inch bolts, and 30 nuts.)

This is very similar to the structure “Customer, Order Number, Order Details.” The main difference is the BillNumber (compare to OrderNumber) is used again and again and again. We fabricate from the same drawings (about 6000 of them) over and over and over.

So far nothing is too complicated. But....

Dimensions on the SheetDetails can change from job to job. These dimensions are what we call “A” and “B” dimensions. Example: the customer says we want BillNumber A123456 where SheetDetails show us A is 13’ and B is 15’. And then we want A123456 where A is 20’ and B is 44’. And then we want A123456 where A is 34’ and 38’. They don’t issue a new drawing, they just tell us the “A” and “B” dimensions. (There could be 100 other data entries on A123456 that have constant dimensions.) We have always dealt with this and this is how we currently handle it: We make a BillNumber A123456-1, A123456-2, and A123456-3. We append those three BillNumbers to tblShopOrder and then just change the dimensions to what is needed, leaving the master copies unchanged. That means that A123456-1, A123456-2, and A123456-3 would live in tblBillNumbers (with their corresponding SheetDetails) but would be appended to tblShopOrders with details changed as necessary. Since the BillNumbers are used again and again and a couple of entries change each time, this is the best way I know to do it. My first question is: Can someone think of a better way to do this than what I have thought of?

If I’m headed in the right direction, then here’s where the rubber meets the road.
I can make a query where when my shop foreman types in “A1234” into his computer and sees A123456-1, A123456-2, and A123456-3. The search tool in a standard query will do just that. This isn’t an issue. But here is my second question: How do I append JUST THOSE THREE BillNumbers to tblShopOrder? How would I select what I want and append it? Better yet, could I select three at a time? (We can do them one at a time if we need to.) Each job is always going to have multiple BillNumbers to attach, even when we aren’t dealing with “A” and “B” dimensions. I just became familiar with an append query but I don’t know how to select just the one(s) I want out of a list shown. The end users will have NO Access database experience so they aren’t going to know how to go into the table to cut and paste or open a query in design view and press RUN, etc. I need this to work more like software and less like a “database” so the end user accepts my system. Any ideas???

PS. I can read VBA but not really write it. I am decent at searching google and manipulating code to fit my needs. So if possible, I’d like to do as much with macros and form buttons as possible. However I’m not scared to try beyond my level of knowledge. I have done unbelievable things in the past with the help from users on this site!!! Thank you all for your time. I’m always very grateful.

[jforbes]

From what I understand, for each tblShopOrder you will have multiple tblBillNumbers records. For each tbleBillNumber you have one tblSheetDetails record. The tblBillNumber records are mostly static and the tblSheetDetails records vary based on what needs to be built.

The one thing I don't fully understand is how the BOM for tblSheetDetails is handled.

Typically for something like this, there is an Order Record that is the where everything starts. Then there is link table that attaches other records to the Order, typically one record for each line item on the Order. You might be able to use the tblSheetDetails for this as long as you don't plan on reusing the tblSheetDetails records on different orders. Either way the Link table will have a column to link to the Order (foreign key) then in your case another foreign key to the tlbBillNumber. If tblSheetDetails can't be used to as the link table, then there will be a third foreign key to the tblSheetDetails table.

[DanicaDear]

I'll address your reply line by line: I think that will be clearest.

First paragraph--your understanding is correct.

Third paragraph--
Typically for something like this, there is an Order Record that is the where everything starts.
Correct--that's the tblShopOrder.


Then there is link table that attaches other records to the Order, typically one record for each line item on the Order.
Sort of correct. Yes, we will have one tblBillNumber for each line item on the order. However, because of the varying data in tblSheetDetails, I don't think I can link to the records. I will have to append each tblBillNumber (with tblSheetDetail following it) into the tblShopOrder because the details change and we need a history record of those details for each job. We can't just change it in the "master" file. We will lose the history.

You might be able to use the tblSheetDetails for this as long as you don't plan on reusing the tblSheetDetails records on different orders.
You're on to it. But tblSheetDetails will be used on many tblShopOrders. Just never twice on a single ShopOrder.

Either way the Link table will have a column to link to the Order (foreign key) then in your case another foreign key to the tlbBillNumber. If tblSheetDetails can't be used to as the link table, then there will be a third foreign key to the tblSheetDetails table.
I have all the foreign keys and links already set up.

I think we are on the same page except for the fact that I feel the need to append line entries from tblSheetDetails into tblShopOrders because of the varying details on the fields. I just don't know how to easily do it. I want to search a query, find the BillNumber I need, and then append it to tblShopOrders. (Is this possible??)


And before I get to far into this--if tblBillNumber and tblSheetDetails are always one-to-one, should I just put all the data in ONE table and eliminate a table and foreign key? I am sort of thinking this might make things simpler for me later. We'd have tblShopOrders and tblBillNumber, where the sheet details would just be data entries in the tblBillNumber.

[jforbes]

It sounds to me that you can use tblShopOrder as the Header for your Order. Then create a table called tblShopOrderLines as the detail of your Order lines. There would be a one-to-many between tblShopOrder and tblShopOrderLines and a one-to-one between tblShopOrderLines and tblBillNumber. If there is a one-to-one between tblBillNumber and tblSheetDetails, then you wont need to do anything further as this link is already defined.

So then as to How you would append from tblBillNumber to tblShopOrder. This would be done by creating an entry into tblShopOrderLines. A basic scenario for this would be:

• A Form based on tblShopOrder is displayed to the User with a SubForm showing all the tblShopOrderLines (with BillNumbers).
• The User clicks an Add button to add a BillNumber.
• A Modal lookup Form is displayed to the user to allow them to Filter and browse BillNumbers.
• When the User finds the BillNumber they want, they select the row and click OK
• A SQL Statement is built up and executed to Insert a record into tblShopOrderLines with both the tblShopOrder and tblBillNumber. Typically a Line Number is included at this time.
• The Modal Form is closed and the Form based on tblShopOrder is refreshed to show the new line item(s).

As to merging tblBillNumber and tblSheetDetails, if you are sure there is always a one-to-one relationship, you should be able to join them together into one table. But you are going to want to be very sure there is only a one-to-one relationship. Typically, there is more work to split something like this than there is to merge them and going backwards may add a lot of work for you.

[DanicaDear]

Thank you very much. Someone in my organization knows sql writing so I'll get with him. Again, thank you!!

[DanicaDear]

I realized that tblBillNumber is NOT one to one with tblSheetDetails. Thanks for the caution!!!!

[NeoPa]

Let me start by suggesting that it would make sense to include tables for customers and products in your database. [tblCustomer] and [tblProduct] tables mean that you don't need to duplicate that data everywhere and you can rely on every reference to either type of item returning the same information regardless.

I'm a little bit hazy on [tblBillNumber] and its A & B dimensions. You describe it as storing a reference to a drawing yet there is no [tblDrawing] mentioned. Nor does the explanation mention storing values for A & B (Dimensions). Nevertheless, [tblDrawing] should contain all the reference data that's static and [tblBillNumber] (Not [tblBill]? Bearing in mind the nuber is simply a single attribute.) all that's changeable per job.

Can the contents of [tblSheetDetail] be determined directly from [tblBillNumber]? If not, are these added by an operator?