Solved

Need help with SQL statement for Microsoft Access table

Posted on 2008-10-27
11
211 Views
Last Modified: 2010-04-21
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
0
Comment
Question by:stephenlecomptejr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 3

Accepted Solution

by:
richard_crist earned 500 total points
ID: 22815158
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
 
LVL 3

Expert Comment

by:richard_crist
ID: 22815176
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
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 22815276
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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 22815325
Anyway to combine the two queries to be just one result?
0
 
LVL 3

Expert Comment

by:richard_crist
ID: 22816892
I have to go home but I will try to come up with something later tonight.   :)
0
 
LVL 3

Expert Comment

by:richard_crist
ID: 22821506
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
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 22822979
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
 
LVL 3

Expert Comment

by:richard_crist
ID: 22825252
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
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 22826380
I was hoping you could combine a view with the final small select statement....
but if not no problem.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 22826384
combine it to make a stored procedure I mean.
0
 
LVL 1

Author Closing Comment

by:stephenlecomptejr
ID: 31510434
Thanks for the great comments.
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question