Solved

ms access query calculate price

Posted on 2011-02-28
3
259 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

830 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