Solved

Need help with SQL statement for Microsoft Access table

Posted on 2008-10-27
11
188 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 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now