Need help with SQL statement for Microsoft Access table

With the following table, I need to find all room numbers that do not have a Type value of FN.  This is much more than just merely creating a unmatched query and I'm unsure of what select statement I can offer to pull the results I need.   If I merely put <> 'FN' as the criteria then 1.PF.001 would show up but FN does show up as its type.  

Please, please - I don't want people asking me what the data is for or is there a better way I should have this data listed.  

Here is what the data is listed as in a table with field names:  Room_Number and Type.  That's the way it is - its not changing.  Based on the following info - what is my syntax for my SQL statement to do what I have explained?   Thank you sincerely in advance.  The Microsoft Access gurus seem to be the best technology experts here - giving fantastic and fastest replies.

Room_Number      Type
1.PF.001      IS
1.PF.001      N
1.PF.001      FN
1.PF.001      FN
1.PF.003      N
1.PF.003      IS
1.PF.003      A
1.PF.003      F
1.PF.003      FN
1.PF.003      FN
1.PF.005      N
1.PF.005      V
1.PF.005      V
1.PF.005      N
1.PF.005      N
1.PF.005      N
1.PF.005      F
1.PF.006      V
1.PF.006      V
1.PF.006      N
1.PF.006      A
1.PF.006      A
1.PF.006      F
1.PF.006      N
1.PF.006      F
LVL 1
stephenlecomptejrAsked:
Who is Participating?
 
richard_cristCommented:
You should be able to do the following and see all the columns for rows with type <> 'FN' at all:

select * from table where room_number not in
( select room_number from table where type = 'FN')

Unless there is something else going on the basic sql syntax above should work.
0
 
richard_cristCommented:
To add to my comment above you can get a shorter list to compare to with:

select * from table where room_number not in
( select distinct room_number from table where type = 'FN')

I am not sure if performance would be better.  It depends on table size, etc.

0
 
stephenlecomptejrAuthor Commented:
richard,

The above query works great.  The problem that I have left to figure out is that the above table was made from another query.  Thus the problem has become two queries where I have to run the first before I run the second query - before running the report.

Here is what the first query does in making the two columns -

SELECT DISTINCTROW PROJ_RM.Room_Number, PROJ_ME.Type INTO _RmsWOFurn
FROM tblequiptype INNER JOIN ((PROJ_INF INNER JOIN (PROJ_DPT INNER JOIN PROJ_RM ON PROJ_DPT.Dept_Code = PROJ_RM.Dept_Code) ON PROJ_INF.[Project Information] = PROJ_DPT.Proj_infid) INNER JOIN ((PROJ_ME LEFT JOIN MEDEQ ON PROJ_ME.Alt_Code = MEDEQ.ProductID) INNER JOIN (ALTSORT INNER JOIN PROJ_EQ ON ALTSORT.Alternate = PROJ_EQ.Alternate) ON PROJ_ME.Code = PROJ_EQ.Equip) ON PROJ_RM.Room_Number = PROJ_EQ.Room_Number) ON tblequiptype.Type = PROJ_ME.Type
WHERE (((PROJ_RM.Room_Number) Like fRoomNumID()) AND ((PROJ_ME.Type) Like fEquipType()) AND ((PROJ_INF.[Project Information]) Like fProjectID()) AND ((PROJ_EQ.Equip) Like fEquipCode()) AND ((PROJ_DPT.Dept_Code) Like fDeptID()) AND ((PROJ_ME.ASE) Like fASEItems()) AND ((PROJ_RM.Rm_Quantity)>0) AND (([Rm_Quantity]*[Quantity])>0) AND ((PROJ_DPT.Show) Like fShowDept()) AND ((tblequiptype.Show) Like fShowType()) AND ((PROJ_RM.Show) Like fShowRoom()) AND ((PROJ_ME.Show) Like fShowEquip()) AND ((ALTSORT.Show) Like fShowAlt()) AND ((PROJ_EQ.Alternate) Like fAltID())) OR (((PROJ_RM.Room_Number) Like fRoomNumID()) AND ((PROJ_ME.Type) Like fEquipType()) AND ((PROJ_INF.[Project Information]) Like fProjectID()) AND ((PROJ_EQ.Equip)="Z12") AND ((PROJ_DPT.Dept_Code) Like fDeptID()) AND ((PROJ_ME.ASE) Like fASEItems()) AND ((PROJ_RM.Rm_Quantity)>0) AND (([Rm_Quantity]*[Quantity])>0) AND ((PROJ_DPT.Show) Like fShowDept()) AND ((tblequiptype.Show) Like fShowType()) AND ((PROJ_RM.Show) Like fShowRoom()) AND ((ALTSORT.Show) Like fShowAlt()) AND ((PROJ_EQ.Alternate) Like fAltID()))
ORDER BY PROJ_INF.[Project Information], (Val(Rm_Num_Sort(PROJ_RM.Room_Number))), PROJ_RM.Room_Number, PROJ_EQ.Equip;
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
stephenlecomptejrAuthor Commented:
Anyway to combine the two queries to be just one result?
0
 
richard_cristCommented:
I have to go home but I will try to come up with something later tonight.   :)
0
 
richard_cristCommented:
Given that the select statement that generates the two columns is somewhat complex, I would suggest using that statement to create a view, especially if that statement will be used quite often.  A view looks like a table but when you select * it actually runs the query behind the scene.  Any time you use a particular query quite often you can use it to define a view.  You can use the view as the "table" I mentioned above.

Let me know if you need more information about views.
0
 
stephenlecomptejrAuthor Commented:
richard,

Could you do me one more final favor?
Could you provide the exact syntax for changing that query to where it creates a view with the two columns?  

Also when you create that view could you also include the final sql syntax?
select * from table where room_number not in
( select room_number from table where type = 'FN')

Thus if so, what would be the complete series of syntax to have this in one stored procedure?
0
 
richard_cristCommented:
CREATE VIEW some_view_name (Room_Number, Type) AS your_big_select_statement_above

The above information is available at http://msdn.microsoft.com/en-us/library/bb177895.aspx

The "some_view_name" would be whatever you wanted to call the view (which again would act like a table).  The "your_big_select_statement_above" would be replaced with the big select statement that yields the two columns.  You would then do the:

select * from table where room_number not in
( select room_number from table where type = 'FN')

where you replace "table" with your view name.

As for stored procedure, you want to store the small select statement in a stored procedure?
0
 
stephenlecomptejrAuthor Commented:
I was hoping you could combine a view with the final small select statement....
but if not no problem.
0
 
stephenlecomptejrAuthor Commented:
combine it to make a stored procedure I mean.
0
 
stephenlecomptejrAuthor Commented:
Thanks for the great comments.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.