Distinct category query

  • Thread starter Thread starter selgin
  • Start date Start date
S

selgin

I have a query where I am trying to get a listing of distinct cagegories. I
found some SQL in another post and attempted to modify it for my own use,
but my code keeps asking me to input "INVENTORY.INV_ID". Here is my current
SQL.

SELECT INVENTORY.INV_ID, INVENTORY.TYPE, INVENTORY.[ITEM#],
INVENTORY.DESCRIPTION, INVENTORY.CTX, INVENTORY.CATEGORY
FROM INVENTORY AS T
WHERE CATEGORY = (SELECT DISTINCT(CATEGORY) FROM INVENTORY
WHERE INV_ID = T.INV_ID);

TIA for any help on this.
 
Many thanks for the quick help!!

Lynn Trapp said:
Change your query this way:

SELECT T.INV_ID, T.TYPE, T.[ITEM#],
T.DESCRIPTION, T.CTX, T.CATEGORY
FROM INVENTORY AS T
WHERE CATEGORY = (SELECT DISTINCT(CATEGORY) FROM INVENTORY
WHERE INV_ID = T.INV_ID);

You have to reverence the fields by the alias ("T") that you gave to the
table.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


selgin said:
I have a query where I am trying to get a listing of distinct cagegories.
I found some SQL in another post and attempted to modify it for my own
use, but my code keeps asking me to input "INVENTORY.INV_ID". Here is my
current SQL.

SELECT INVENTORY.INV_ID, INVENTORY.TYPE, INVENTORY.[ITEM#],
INVENTORY.DESCRIPTION, INVENTORY.CTX, INVENTORY.CATEGORY
FROM INVENTORY AS T
WHERE CATEGORY = (SELECT DISTINCT(CATEGORY) FROM INVENTORY
WHERE INV_ID = T.INV_ID);

TIA for any help on this.
Click to expand...
Click to expand...
 
Back
Top