Records without related records not showing up in report

[BDibz]

I haven't dabbled in Access for a little while now, so I'm having trouble going back and fixing an erroneous database. It uses a check-in, check-out inventory system that then takes the total of transactions for each object type to determine current inventory. This query pulls data together for the report, which should display the Electrode ID, the Parts it corresponds with, the Customer name, Qty On-Hand, Qty out for replate, and total qty:


The problem is, if someone adds an Electrode to the database that does not exist in the existing Electrodes table (which defines the electrode's corresponding Parts) then it will not appear in the report. This is the query for the report:

SELECT tblTransactions.Electrode, Sum(([Qty In]-[Qty Out])) AS OnHand, Sum(([Replate In]-[Replate Out])) AS OutForReplate, tblInventory.partID, tblParts.[Customer Name]FROM tblParts INNER JOIN (tblInventory INNER JOIN tblTransactions ON tblInventory.electrodeID = tblTransactions.Electrode) ON tblParts.[Part #] = tblInventory.partID
GROUP BY tblTransactions.Electrode, tblInventory.partID, tblParts.[Customer Name]
HAVING (((Sum(([Qty In]-[Qty Out])))<>0));

It should be noted that Parts is a linked table, so it can't be directly modified through this database.
Could I either:


A. Modify this SQL statement so that it includes Electrode entries without matching Parts
B. Make the database automatically open another pop-up form that allows the creation of a new Electrode entry that can be tied to a Part when the electrode does not exist yet

and which would be the better option?

Please ask for any additional information you need if this post isn't clear!

Thanks

[ranman256]

you may need an OUTER join, to pick up orphan subrecords.

[BDibz]

Also, before I continue with this problem; does this relationship for my electrodes make sense or is it redundant?

[ranman256]

to me, everything is a part, including electrodes.
there would be 1 table for parts (electrodes would be in it too) the part would have a PartType (electrode, litebulb, etc)

[BDibz]

to me, everything is a part, including electrodes.
there would be 1 table for parts (electrodes would be in it too) the part would have a PartType (electrode, litebulb, etc)

Electrodes are effectively a different object class from parts. Electrodes are an expendable resource used to create parts (they burn metal away in a specific form). Electrodes have a specific number of uses per electrode (partsPerElectrode). Electrodes also have a one-to-many relationship with parts, since some parts need multiple electrodes to be created. Parts are pre-defined in an external table, and are tied to customers and other properties.

This DB tracks only the inventory of electrodes for the purpose of knowing when we need to order more. When we get a forecast, we know how many parts will be run, and that information in turn determines the gap between electrode inventory on-hand and the amount needed.

[Micron]

I think you ought to be considering a BOM type of database, unless the only thing you're concerned with here is what electrodes are used in the manufacturing of a part. In that case, I see tblElectrodes with unique ID (suggest auto number), the electrode code and perhaps a description field. If you don't have a table for parts, then creating one would be the best option if not too onerous. Then tblPartsRods which you don't have (rods being short for welding rods, which I think this is) would have an auto number ID field, plus the part ID field (PartIDfk) as a foreign key whose value is the PK from tblParts) and the rod ID (RodIDfk) as a foreign key whose value is the PK from tblElectrodes. Thus if part ID (99 from tblParts) requires 3 rods, using your data from post 3 and rows 6 to 8 being identified as such (which you have not done so as ID numbers) then this would look like

tblPartsRods

ID	PartIDfk	RodIDfk
1	99	6
2	99	7
3	99	8

To get the rod descriptions into a form or report, your query would include the description type fields to show that the rods in question are E6011, E6012 and E6013 if I counted my rows correctly. Sure, you can forego the auto number type of unique field, but the main disadvantage would be that if you created related records after entering a new rod type and found that rod type entry to be incorrect, you now have to Cascade update all related records. Easier to use the numeric ID that uniquely identifies the field. My parts and rods tables would also have a way to identify those that become obsolete so that they cannot be assigned anymore while maintaining historical records. This set up would allow the addition of a Qty field in tblPartsRods to show quantity quite easily, since each rod/part pair is a record.

Hope that helps a bit.