How to create queries that lookup information in other queries or tables.
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?
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?
ASKER
Thanks Aikimark
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.
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..
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.
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..
Show me an example of the data being read from Excel and what the data needs to look like in Access.
If I understand your description correctly, this seems similar to a table configuration I'd encountered in a lab mgt. system. One specimen table had multiple key fields to a lookup table. 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).
If I understand your description correctly, this seems similar to a table configuration I'd encountered in a lab mgt. system. One specimen table had multiple key fields to a lookup table. 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).
ASKER
Sample attached
Open "Costing" 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?
Thank you Sample.zip
Open "Costing" 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?
Thank you Sample.zip
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well I have played with that for an hour and yes you are a genius. So simple.
However, you shouldn't store such data in the table. It should always be available through the joined tables for your reports. 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.
glad I could help.
glad I could help.
ASKER
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.
Thanks
Thanks
This looks like a data you are using for the purposes of learning Access (or databases). My answer might require you to learn about Entity-Relationships (logical database design) in order to understand it. Your posted database has very simple tables with single entries for the component prices
What is normally done in a system where prices change is that the price of an item is associated with a date. 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.
What is normally done in a system where prices change is that the price of an item is associated with a date. 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.
ASKER
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?
Having done this for just about 20 years does not make me right but it does make me curious.
Having done this for just about 20 years does not make me right but it does make me curious.
It depends on your user interface and needs. 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.
Your retention of the costs depends on several factors:
* the financial/securities/audit
* the dynamic/static nature of the user-selected data
* the dynamic/static nature of the cost tables
* the need to report/analyze trend data over time
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.
Certainly, storing the prices in the costing table would result in faster processing (reports and queries). However, that speed has flexibility trade-offs.
Your retention of the costs depends on several factors:
* the financial/securities/audit
* the dynamic/static nature of the user-selected data
* the dynamic/static nature of the cost tables
* the need to report/analyze trend data over time
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.
Certainly, storing the prices in the costing table would result in faster processing (reports and queries). However, that speed has flexibility trade-offs.
ASKER
That's great thank you
https://www.experts-exchange.com/A_19.html
https://www.experts-exchange.com/A_2153.html