Solved

Need help with SQL statement for Microsoft Access table

Posted on 2008-10-27
11
216 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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
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

A new era in Cloud training has arrived.

A day that will go down in Cloud history.. But are you ready for it? Will you accept this Cloud challenge?

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

617 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