[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Need help with SQL statement for Microsoft Access table

Posted on 2008-10-27
11
Medium Priority
?
232 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
  • 6
  • 5
11 Comments
 
LVL 3

Accepted Solution

by:
richard_crist earned 2000 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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

612 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