<div>
Here is a good start on your education on this subject:<br />
<a href="https://www.experts-exchange.com/A_19.html" rel="ugc">https://www.experts-exchange.com/A_19.html</a><br />
<a href="https://www.experts-exchange.com/A_2153.html" rel="ugc">https://www.experts-exchange.com/A_2153.html</a><br />

</div>


<div>
Thanks Aikimark<br />
<br />
I have tried to simplify the problem for ease of understanding. The data, after insertion by pasting from Excel, is then looked at by a form. The form and the table actually have 71 fields that discribe in detail the product. So I need all this info in a single record so that it can be viewed in total. there are thousands of records in the database. So I don't think that this is a relationship issue. The question is about getting prices from tables (or somewhere) to add to each record.<br />
<br />
I know it's not conventional but it works very well indeed from a point of view of seeing all information at once and being able to make decisions based on the data it's just what the customer wants..
</div>


<div>
Show me an example of the data being read from Excel and what the data needs to look like in Access.<br />
<br />
If I understand your description correctly, this seems similar to a table configuration I'd encountered in a lab mgt. system. &nbsp;One specimen table had multiple key fields to a lookup table. &nbsp;In order to get the correct information, I had to link the lookup table to the specimen table multiple times (one for each key field).
</div>


<div>
Sample attached<br />
<br />
Open &quot;Costing&quot; table. the first 4 columns (Excluding Autonumber) will be entered by pasting from excel into a query that will hold all 8 or 9 columns. The question is can a query collect the prices from the component tables automatically and calculate a total as the data is pasted?<br />
<br />
Thank you <a href="https://filedb.experts-exchange.com/incoming/2011/05_w21/459986/Sample.zip" target="_blank" class="file-inline" title="Costing Database (17 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Sample.zip</a>
</div>


Sample.zip

<div>
Well I have played with that for an hour and yes you are a genius. So simple.
</div>


<div>
However, you shouldn't store such data in the table. &nbsp;It should always be available through the joined tables for your reports. &nbsp;If you are using a form or report, you can do the calculation as part of the user interface or with a field on your report.<br />
<br />
glad I could help.
</div>


<div>
Hi Why do you advise not updating (I guess you mean the Costing table )the table. Isn't that the point of an update query. How will I keep a record of a price quoted if the prices change in the tables in future.<br />
<br />
Thanks
</div>


<div>
This looks like a data you are using for the purposes of learning Access (or databases). &nbsp;My answer might require you to learn about Entity-Relationships (logical database design) in order to understand it. &nbsp;Your posted database has very simple tables with single entries for the component prices<br />
<br />
What is normally done in a system where prices change is that the price of an item is associated with a date. &nbsp;When you want to know the historical price, you would link the tables, based on the greatest date that is less than the quoted price date.
</div>


<div>
So if you were to create an invoice on a form where items in the invoice are chosen from an ID in a combo box, you would have a link to the combo's table and create a date that the item was chosen and store all historic prices in the pricing table permanently rather than have the selection from the combo paste the description and current price into the item on the invoice and keep a permanent record of the invoice? Whether we paste or update the invoice makes no difference presumably?<br />
<br />
Having done this for just about 20 years does not make me right but it does make me curious.<br />

</div>


<div>
It depends on your user interface and needs. &nbsp;A good UI will show the (component and total) costs incrementally, so you will want to pull the related table data as the user selects items.<br />
<br />
Your retention of the costs depends on several factors:<br />
* the financial/securities/audit<wbr />ing requirements that apply to your company and its industry<br />
* the dynamic/static nature of the user-selected data<br />
* the dynamic/static nature of the cost tables<br />
* the need to report/analyze trend data over time<br />
<br />
I don't know you or your company or the application to which this question applies, so my advice is given from a context poor perspective.<br />
<br />
Certainly, storing the prices in the costing table would result in faster processing (reports and queries). &nbsp;However, that speed has flexibility trade-offs.
</div>


<div>
<div class="content wysiwyg-content">
Hi All<br />
I have data in a table that holds a list of components that make up an engine assembly. I have Thirteen columns: Make, Model, Block, Cylindar Head, Camshaft, Gearbox and Driveshaft. I have a reference number for each part &nbsp;and a single date field indicating when the information is entered. I then have &nbsp;&quot;Current Prices Tables&quot; for each of the five items holding description and part number and I need a query to collect the current prices from each table using the reference number from each part. The thing that really throws it all is that the data (without prices) is pasted into the main table from Excell. Will a query be able to automate the process of attaing a total price for the assembly?
</div>
</div>


Hi All
I have data in a table that holds a list of components that make up an engine assembly. I have Thirteen columns: Make, Model, Block, Cylindar Head, Camshaft, Gearbox and Driveshaft. I have a reference number for each part  and a single date field indicating when the information is entered. I then have  "Current Prices Tables" for each of the five items holding description and part number and I need a query to collect the current prices from each table using the reference number from each part. The thing that really throws it all is that the data (without prices) is pasted into the main table from Excell. Will a query be able to automate the process of attaing a total price for the assembly?

How to create queries that lookup information in other queries or tables.

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.