Solved

ms access query calculate price

Posted on 2011-02-28
3
253 Views
Last Modified: 2012-05-11
I have a parts table that has the guage of metal and type of metal to make a part.  I have a cost table that has costs which is dependent on guage and type.

Parts Table:
Parts    GA   Type
A1         12    H
A2          10    G

Cost Table:
Type  GA  Cost
H         07     1.25
H         10     1.00
H         12     0.75
G         07     1.50
G . . . . .

What is the best way to calculate the cost for each part - (I thought about a subselect query but have not done many)

Thanks for any help with this.
0
Comment
Question by:johnmadigan
3 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 167 total points
ID: 34997142
Bring the two table into a query in design view.
Create inner joins between GA and between Type of the two tables.

/gustav
0
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 167 total points
ID: 34997146
select p.Type, p.GA, c.Cost from Parts p inner join Cost c on p.Type = c.Type and p.GA = c.GA
0
 
LVL 12

Assisted Solution

by:Paul_Harris_Fusion
Paul_Harris_Fusion earned 166 total points
ID: 34997152
Create a query as follows:

Select P.Parts,  C.Cost
FROM
[Parts Table] P Inner Join [Cost Table] C
on P.GA = C.GA and P.[Type] = C.[Type]
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now