Appending From One Table to Another Table to Specific Data

[skilche1]

OK. First off, this is the first time I’ve Appended and I've searched the forum and other sites, but canot seem to find any help:confused.

I am developing a database for incoming inspection. I have set up two tables, one with spec callouts per product and the other table contains fields where I want the specs from the tblSpec table to be Append into the tblMeasurement table. Now, within the tblMeasurement table (where I am trying to append specs from the tblSpecs table into), I also have additional fields for actual measurements from received goods.

My plan is as I go through and select the Vendor, it populates the associated products to that vendor (no problem there). When I want to select the product, I would like the specs from the tblSpecs to dumped into the tblmeasurement table that correlates to that product on the form.

How can I have this automatically append after selecting the product? I am not sure if I have my relationships set up correctly or not, but I cannot see to append when I am trying to execute this function while in Query view.
I have the db attached in a zipped format. If you can assist, I would greatly appreciate it.

Thank you for your help in advance.

 

[plog]

No, your relationships are not correct. You shouldn't have loops or multiple paths between two tables. That means you need to refine the relationship between Vendors, Products and Inspection Results.

My guess is that Inspection Results shouldn't go directly to Products but indirectly via Products. You inspect products correct? Not vendors.

Lastly, I don't really understand your issue about appending data. Usually APPEND queries are for people who have improper structures or who don't know how relational databases are to work. You don't move data among and between tables, you set up queries and use the query combine all relevant data.

 

[skilche1]

Thanks for your reply Plog. I caught the looping path and corrected that.

WHat I am trying to accomplish is that yes, I am inspecting products from vendors. What I was hoping to achieve was once the specs are added to each product, I would be able to select the product from the vendor where the specs would automatically be loaded for the inspector to add the readings he/she got from inspecting the part. This way, the inspector would have to look at a data sheet for specs. That person would be able to lok at the database, get the measurements and input the data associated with that part...

 

[skilche1]

This is what I am trying to accomplish after Appending as I understand it should do. Notice the blank fields for data input.

"Drawing Location" and "Spec" fields are what I am trying to append.

Note, this is a photoshopped version.

 

[plog]

Now we run into a different structure issue--numerated fields. You should not have fields that increment (Measurement1, Measurement2, etc.) . Tables shouldn't accomodate data horizontally (by adding fields), but vertically (by adding rows). That means you most likely need a new table. This new table would have a foreign key to tblMeasurements.Measurements_ID and tblMeasurements would have a 1 to many relationship with the new table.

The structure of that table would be like so:

tblSubMeasurements
SubMeasurement_ID, Measurements_ID, Measurement, MeasurementNumb

MeasurementNumb would replace the number that suffix each Measurement field currently in tblMeasurements (1, 2, 3, 4, 5).

 

[skilche1]

Now we run into a different structure issue--numerated fields. You should not have fields that increment (Measurement1, Measurement2, etc.) . Tables shouldn't accomodate data horizontally (by adding fields), but vertically (by adding rows). That means you most likely need a new table. This new table would have a foreign key to tblMeasurements.Measurements_ID and tblMeasurements would have a 1 to many relationship with the new table.

The reason why I have it set up like that is that the inspector can take multiple sample readings from the same lot of received goods. Each row that I illustrated indicates a unique measurement of the part. With this being set up like the way I have it will allow me to average out the measurements of each spec call outs. This is why I am trying to pull the specs from the tblSpec table into the tblMeasurement table so all the information will be in the same row...

 

[plog]

I don't fully understand what your data represents, so I don't really get your explanations of how your tables relate in a real-world sense. However, the numerated fields thing I pointed out is a classic error.

When you mention Average, there's a built in aggregate query function for that (AVG()). Of course to use it, you would need to put your data into the structure I suggested in my previous post.

Can you export some sample data to a spreadsheet? Put data from each table on a different tab, then make an additional tab illustrating this Average you want to calculate along with all the other data that would go with it.

 

[skilche1]

I don't fully understand what your data represents, so I don't really get your explanations of how your tables relate in a real-world sense.

I apologize for not being as detailed as I should have been. I forget that most people aren’t in the QC field and do not understand what takes place during the incoming inspection process. I’ll outline the process in which how it works and what I am trying to accomplish. Hope this helps.

OK, to better illustrate the "real world" situation, I took some time to locate a generic CAD drawing on the net. This particular drawing illustrates the dimensions of a Top Plate that needs to be manufactured.

Modified in Photoshop by highlighting critical measurements.

You will notice in this example I have selected, the engineer has highlighted the critical measurements of the part to be manufactured. I would then add the critical measurements (along with deviations associated with that one dimension, in which our Incoming Inspector would have to measure upon receiving the part from the vendor (the manufacturer) in the Vendor Input form. This is needed with any company that manufacture goods from other manufacturers that supply them their parts. Note below

Not Photoshopped.

Once the Top Plate is received from the vendor, the Incoming Inspector then takes samples based on the amount that was delivered to the company. In this example, 5,600 Top Plates were received, which he would have a measuring sample rate of 5 Top Plates from this shipment

Modified in Photoshop by adding Measurement readings.

So what I trying to accomplish is to have the “DrawingLocation and Spec” fields that is in the tblSpec table automatically dump the information into the identical fields in the tlbMeasurement table each time a product has been received, which has other information I want to collect, which in this case are the measurements taken from the samples (up to 5). I want the drawing location and spec indicated so it will force the inspector to indicate the measurements he got from the part in which the engineer is looking for. It will also allow the inspector to know where to measure without looking at another data sheet. This step would be repeated each time a particular or identical part has been received.

So at this point, the inspector will randomly pull 5 samples from various packages to sample (in most cased, these parts will be in boxes. In this case, they may be grouped 10 Top Plates per box) and measure each data point on each sample. Hence the Measure 1 – Measure 5 fields.

Once data has been collected, I can then create a query (of any type, depending on the information I am seeking) to create reports to control the vendor’s performance.

Now, from what I see on the net through forums and YouTube, Appending is also used from transferring data not only from Excel to Access, but also used to transfer data from one table to another table. I wasn’t sure if I this was the correct forum or not, but since I believe Appending is a Query function, I posted this thread here. If this not the correct function on how to perform this action, can you please let know what it is and how to go about doing it?

I appreciate your help with this a lot and value your time.

Thanks again,
Steve

 

[plog]

That helps, not entirely clear still, but I'm starting to understand. From what you've said, I am correct about the Measurement1 - 5 fields--it requries another table with 1 record per measurement, not as you have it.

Also, it sounds like a query is the way to go, not an APPEND. You would simply link that new table to the specs they need to measure, not move the data into there.

 

[skilche1]

That helps, not entirely clear still, but I'm starting to understand. From what you've said, I am correct about the Measurement1 - 5 fields--it requries another table with 1 record per measurement, not as you have it.

I would actually prefer one record with all measurements (1 up to 5) from that one lot to be associated with the drawing location and spec. Set it up so that I have one record per shipment received containing all the measurements of that one measurement indicated from the drawing. To me, that makes more since...

I don't know how else to explain it. Basically I just want the Drawing Location and Specs from the tblSpecs to be dumped into the tblMeasurements table when the product is selected. So the end results within the tlbMeasurements table is there would be multiple entries of Drawing Location and Specs with measurement 1 - 5 readings (to be added in by the Incoming Inspector) from either the identical product and others...

NOTE: the Drawing Location and Specs with measurement 1 - 5 readings were manually added through the table structure.

As you can see, the tblSpecs table has one entry while the tblMeasurements table consists of multiple entries of the dimensions that needed to be evaluated from two separate shipments. I thought Appending would be able to dump the data from one table to another, no? If not, then I have to come up with alternative solution to my problem....

I am not sure I can explain this any better, I don't mean to be bother...