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

Derek Brown
Derek Brown used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
Here is a good start on your education on this subject:
http://www.experts-exchange.com/A_19.html
http://www.experts-exchange.com/A_2153.html

Author

Commented:
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..
Top Expert 2014

Commented:
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).
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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
Top Expert 2014
Commented:
Not as they enter/paste data.  However, you can run an update query.
UPDATE (((Costing LEFT JOIN Engine ON Costing.EngineRef = Engine.EngineRef) 
LEFT JOIN Gearbox ON Costing.GearboxRef = Gearbox.GearboxRef) 
LEFT JOIN DriveShaft ON Costing.DriveShaftRef = DriveShaft.DriveShaftRef) 
LEFT JOIN Tyres ON Costing.TyresRef = Tyres.TyresRef 
SET Costing.EnginePrice = [engine].[engineprice], 
Costing.GearboxPrice = [gearbox].[gearboxprice], 
Costing.DriveShaftPrice = [driveshaft].[driveshaftprice], 
Costing.TyresPrice = [tyres].[tyresprice], 
Costing.Total = [engine].[engineprice]+[gearbox].[gearboxprice]+[driveshaft].[driveshaftprice]+[tyres].[tyresprice] 
WHERE (((Costing.Total)=0));

Open in new window

Author

Commented:
Well I have played with that for an hour and yes you are a genius. So simple.
Top Expert 2014

Commented:
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.

Author

Commented:
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
Top Expert 2014

Commented:
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.

Author

Commented:
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.
Top Expert 2014

Commented:
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/auditing requirements that apply to your company and its industry
* 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.

Author

Commented:
That's great thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial