MS Access: Report to contain info from third table

Question

I'm currently working on a database for a company, for them to use when making production orders. A report is to be made consisting of several things, mainly product number, order number etc etc. A part of the report is to be made up of a list of spare parts needed for the production of the item in question. I have a table with an order number and product number, which needs to look in another table to find the necessary spare parts. However, the name, location and stock of those spare parts are in a third table, and I can't seem to find a way to include these things automatically when the product number is known. I'm pretty new to MS Access, so any help will be greatly appreciated. Thanks!

I have a table called Table1, which uses a combobox to automatically fill boxes such as production time, test time etc from a given product number. This data is gathered from the second table StandardTimes, which has as a primary key the product number. Other columns in this table includes production area, standard quantity, average production time, and also includes in several columns, the necessary spare parts needed. In a third table called Inventory, we have the product numbers of the spare parts, their location in storage, and number of items currently in store. I created a report using a query which takes an order number, and creates a report on that order number from Table1. What needs to be included in this report is a list of the spareparts necessary, the location in the storage, and the number of items currently in store.

Revised from new user input

Your question still does not provide actual columns or data. As a result, it's hard to model your needs. However, based on what I can read, I think that you have are missing some basic design setup items in a relational model.

Assuming that you have 3 tables: Table1 (Orders), StandardTimes (Products) and Inventory (SpareParts)

In English, every order has one or more products. Every product has one or more spare parts. Really you'd want an orders table, and an order details table which has records for each item as part of that order. But I'm answering it on your setup which I believe is flawed.

Orders <-(1:M)-> Products <- (1:M) -> SpareParts

You have an OrderID, a ProductID, and a SparePartID.

A query such as this would join those 3 tables together with that kind of relationship.

SELECT  o.OrderNum, o.ProductNum, st.ProductionArea, st.StandardQuality, i.SparePartsNum, i.Location, i.Qty
FROM Orders as o
INNER JOIN StanardTimes as st on o.ProductNum = st.ProductNum
INNER JOIN Inventory as i on i.ProductNum = st.ProductNum

D. Bachen D. Bachen · Accepted Answer · 2015-07-01T16:21:17

Some sample data would be helpful to help design the queries. In principal you would need to join the tables together to get the desired result.

You would join the productID on tblOrders to the ProductID on tblProducts. This will net you the name of the product etc. This would be an INNER join, as every order has a product.

You would then join to tblSpareParts, also using the productID so that you could return the status of the spare parts for that product. This might be a LEFT JOIN instead of an INNER, but it depends on if you maintain a value of 0 for spare parts (e.g. Every product has a corresponding spare parts record), or if you only maintain a spare parts record for items which have spare parts.

MS Access: Report to contain info from third table

1 Answers