Relationship between tables and generating a query

[CaliberGX]

Hey guys,



I am new to the forum and I will try and make this as easy to understand as possible Up to now I have been learning on my own on how to work on access.

I have two tables in my access database. One table shows parts of what I want to make and the materials and resources they need. The other table shows the materials I need and what resource each material requires.

Example Table One

Material	Part 1	Part 2
Material a	1	1
Material b	2	0
Resource a	0	1
Resource b	1	0

Example Table Two

Resource	Material a	Material b
Resource b	1	0
Resource c	1	2
Resource d	0	1
Resource e	1	1

And I would like to create a relationship between the two tables but I can't think of how to. I want to get a query/report that would show me how many resources I will need for each part and for all parts excluding the materials.

Example of end result

Resource	Quantity Part1	Quantity part2	Total
Resource a	0	2	1
Resource b	2	1	3
Resource c	5	1	6
Resource d	2	0	2
Resource e	3	1	4

I hope my question makes sense. Thank you for your time and consideration.

[ranman256]

because your table is designed wrong, it is not atomic.
you want the data in table1 to look like:

Material B , PART1, 2
Material B , PART2, 0


you cannot relation the table design you have.
(same with tbl2)

[CaliberGX]

Thank you for your reply.

And if I change the tables like that how do I proceed from there?

[Homegrownandy]

If your relationships are correct then you can show whatever you like in a query. in design view drag on screen the tables that have fields you wish to view then double click the field. this will then go in the bottom where you can further manipulate the data to refine your results.

Database normalisation and relationships might be a search term you want to put into youtube (or google). Demonstrations will help you a lot more than the forums at this stage.