Show parent records where childs exist

[hbrems]

Dear all,

I have a table categories and a table items which are connected by a 1 to many relationship.

Categories are:

- cars
- electronics
- foodstuff

Items fall under these categories and could be:

- radio
- computer
- apple

I would like to retrieve the categories for which an item exists.

In the above example this would return electronics and foodstuff because no car is listed in the items table.

Access certainly must know that a child record exists. Referential integrity is turned on and when I try to remove a category for which an item exists I get a message that this is not be possible.

Kind regards,
Hans B.

 

[tehNellie]

A simple JOIN would do it:

SELECT tblcategory.name as Category, tblItem.name as Item
FROM tblCategory
INNER JOIN tblItem ON
  tblCategory.id = tblItem.categoryid

The INNER JOIN only returns records where there is a match in both tables so your result set would be:

Category     Item
-----------------
electronics  Radio
electronics  Computer
foodstuff    Apple

to only show the categories themselves just modify the query slightly:

SELECT DISTINCT tblcategory.name as Category
FROM tblCategory
INNER JOIN tblItem ON
  tblCategory.id = tblItem.categoryid

Which then returns:

category
--------
Electronics
Foodstuff

because you've dropped the item names off, the DISTINCT only shows unique categories in your recordset.

 

[hbrems]

Neat, I knew there would be a simple solution.

You have helped me to create a descent treeview control where only the relevant categories are showing.

 

[Xx_TownDawg_xX]

Right now my SQL for a similar issue is this:

SELECT qryDataEntry.Series_ID, qryDataEntry.SeriesDesc, qryDataEntry.QtyRejected, qryDataEntry.SO_Nbr, qryDataEntry.AuditDate, qryDataEntry.Comments, qryDataEntry.Dispo_ID
  FROM qryDataEntry
  WHERE (((qryDataEntry.AuditDate)>=Year(([tblDataEntry].[AuditDate])=[Forms]![frmMainMenu]![qFiscalStart] And ([tblDataEntry].[AuditDate])<=[Forms]![frmMainMenu]![qFiscalYear])) AND ((qryDataEntry.Dispo_ID)<>"NDF") AND ((qryDataEntry.Department_ID)=[Forms]![frmMainMenu]![qDepartment]))
  ORDER BY qryDataEntry.AuditDate, qryDataEntry.AuditTime;

Which part of that is my inner join?

INNER JOIN tblDataEntry ON
tblCategory.id = tblItem.categoryid

NOTE: For the purpose of the subform, the parent is “tblDefects” and the child is “tblDataEntry” and the common link between both is Defect_ID.

The SQL works fine except that I only want to show parent records where childs exist.

NOTE: I just noticed this original thread is in the wrong forum though.

 

[Xx_TownDawg_xX]

Oops.. revised the SQL.

SELECT qryDataEntry.Defect_ID, qryDataEntry.Series_ID, qryDataEntry.SeriesDesc, qryDataEntry.QtyRejected, qryDataEntry.SO_Nbr, qryDataEntry.AuditDate, qryDataEntry.Comments, qryDataEntry.Dispo_ID
  FROM qryDataEntry INNER JOIN tblDefects ON qryDataEntry.Defect_ID = tblDefects.Defect_ID
  WHERE (((qryDataEntry.Defect_ID)=[tblDefects]![Defect_ID]) AND ((qryDataEntry.AuditDate)>=Year(([tblDataEntry].[AuditDate])=[Forms]![frmMainMenu]![qFiscalStart] And ([tblDataEntry].[AuditDate])<=[Forms]![frmMainMenu]![qFiscalYear])) AND ((qryDataEntry.Dispo_ID)<>"NDF") AND ((qryDataEntry.Department_ID)=[Forms]![frmMainMenu]![qDepartment]))
  ORDER BY qryDataEntry.AuditDate, qryDataEntry.AuditTime;

so right now it states: INNER JOIN tblDefects ON qryDataEntry.Defect_ID = tblDefects.Defect_ID

So what else do I need to add so it only will show parent records where childs exist?

I did find this, but it doesn't seem to be helping..

http://www.eggheadcafe.com/software/aspnet/33356980/relationships-for-queries.aspx


I'm still learning here..