Trying to access data from three tables selectively
Date: 19 Feb 2002 07:58:32 -0800
Message-ID: <73229072.0202190758.6648affd_at_posting.google.com>
Please help!!!
[Quoted] [Quoted] I have an access database and am trying to execute an SQL query that goes...
SELECT InventoryID, ProductName, (select Category from tblDevicePC
where tblDevicePC.InventoryID=tblInventory.InventoryID) AS CategoryPC,
(select Category from tblDevicePeriph where
tblDevicePeriph.InventoryID=tblInventory.InventoryID) AS
CategoryPeriph
FROM tblInventory
WHERE (((select Category from tblDevicePC where
tblDevicePC.InventoryID=tblInventory.InventoryID) Or (select Category
from tblDevicePeriph where
tblDevicePeriph.InventoryID=tblInventory.InventoryID))<>False);
With the present query I have this output:
InventoryID ProductName CategoryPC CategoryPeriph
23 Dell Inspiron Desktop 24 Vectra Desktop 25 Pavilion Desktop 26 Dimension Desktop 27 Micron Laptop 28 Laser Printer Printer 29 Deskjet Printer Printer 30 Scanner Scanner 31 Web Cam Camera 32 External Hard Drive Storage 33 External CD ROM Storage 34 Logitech Camera Camera 35 Optical Mouse Other 36 10-baseT Hub Network 37 100-baseT Hub Network 38 100-baseT Hub Network 39 Wireless Keyboard Other 40 Backpack Storage 41 DVD Drive Storage 42 Think Pad Laptop 43 Vaio Laptop *********************************************************************I would like to have my output in this format where category is selected from the relevant table PC or Peripheral based on DevicePC or DevicePeriph field in the inventory table
InventoryID ProductName Category
23 Dell Inspiron Desktop 24 Vectra Desktop 25 Pavilion Desktop 26 Dimension Desktop 27 Micron Laptop 28 Laser Printer Printer 29 Deskjet Printer Printer 30 Scanner Scanner 31 Web Cam Camera 32 External Hard Drive Storage 33 External CD ROM Storage 34 Logitech Camera Camera 35 Optical Mouse Other 36 10-baseT Hub Network 37 100-baseT Hub Network 38 100-baseT Hub Network 39 Wireless Keyboard Other 40 Backpack Storage 41 DVD Drive Storage 42 Think Pad Laptop 43 Vaio Laptop ***************************************************************************************
The tables are:
tblInventory - primary key is InventoryID
InventoryID DevicePC DevicePeriph ProductName EquipType
23 Yes No Dell Inspiron PC 24 Yes No Vectra PC 25 Yes No Pavilion PC 26 Yes No Dimension PC 27 Yes No Micron PC 28 No Yes Laser Printer Peripheral 29 No Yes Deskjet Printer Peripheral 30 No Yes Scanner Peripheral 31 No Yes Web Cam Peripheral 32 No Yes External Hard Drive Peripheral 33 No Yes External CD ROM Peripheral 34 No Yes Logitech Camera Peripheral 35 No Yes Optical Mouse Other 36 No Yes 10-baseT Hub Other 37 No Yes 100-baseT Hub Other 38 No Yes 100-baseT Hub Other 39 No Yes Wireless Keyboard Other 40 No Yes Backpack Other 41 No Yes DVD Drive Peripheral 42 Yes No Think Pad PC 43 Yes No Vaio PC
########################################################################################
tblDevicePC - Primary key is ID
ID InventoryID Category
5 23 Desktop 6 24 Desktop 7 25 Desktop 8 26 Desktop 9 27 Laptop 10 42 Laptop 11 43 Laptop
#########################################################################################
tblDevicePeriph - Primary key is ID
ID InventoryID Category
5 28 Printer 6 29 Printer 7 30 Scanner 8 31 Camera 9 32 Storage 10 33 Storage 11 34 Camera 12 35 Other 13 36 Network 14 37 Network 15 38 Network 16 39 Other 17 40 Storage 18 41 Storage
Please help me get to the bottom of it, I can feel that there is something small that I am missing but I am not sure what. Thanks in advance.
Batool Fatima Received on Tue Feb 19 2002 - 16:58:32 CET