I am working on a Query to populate a report to aid in goal tracking for our production Equipment. We Currently have 39 machines in my facility, the Database contains around 2000 part numbers that we currently produce. The machines can run multiple parts depending on the setup.
I have a SCADA Software that daily saves records from my production Equipment into the Production_partial Table in my Database (Information for this table is below) I have another table that the Production Lead will enter the Goal (Takt time Field in the tblGoals) for the part number for a particular machine. This SCADA Software also calculates the current production rate(pieces per hour) to compare to the goal that has been set for each part number when it runs in each machine.
Daily the production lead will run a report that uses the Production_partial Query as its record source. The Production_Partial Query looks for records that have been saved for that day in the Production_partial table and then if there is a corresponding record for the machine and Part number combination in the tblGoals It will populate the Goals field of the Report(this is the TaktTime Field in tblGoals).
The Issue that I am having currently is if there is more than one record in the tblGoals for a machine then it will return the same number of records in the Production_Partial Query but it will repeat the part number that is currently running for that day in the Production_partial table.
One record in the query for the machine for the selected day will have the correct part number and will return the correct Goal(takt_time Field in the tblGoals) and the additional will be blank.
How can i have the query join the two tables on both the part number field and the Machine ID field?
I am in the process of trying to obsure the information in the database to be able to post it.
Thanks in advance for any assistance that can be given.
Jason
Tables:
Production_partial
Columns
Name Type Size
PressID Text 4
CustProdID Text 20
Production Double 8
TaktCount Double 8
Uptime Double 8
Date Date/Time 8
Time Date/Time 8
tblGoals
Columns
Name Type Size
PK_TaktTimeID Long Integer 4
FK_PressID Text 255
FK-PartNumber Text 255
TaktTime Long Integer 4
Query: Production_Partial Query
SQL
SELECT Production_Partial.PressID, Production_Partial.CustProdID, Production_Partial.Date, tblGoals.TaktTime,
CustomerName.CustomerID
FROM (Production_Partial INNER JOIN (CustomerName INNER JOIN PartNumber ON
CustomerName.CustomerID=PartNumber.CustomerID) ON
Production_Partial.CustProdID=PartNumber.ProductID ) LEFT JOIN tblGoals ON
Production_Partial.CustProdID=tblGoals.[FK-PartNumber]
WHERE (((Production_Partial.Date)=Date()))
ORDER BY Production_Partial.PressID;