Select Records that fall in an external set of ranges (subquery)

HiI have not done subqueries before so I am a bit confused.  I have twotables1 - Phone Numbers - (converted to be numeric) (with other fields forlater) and2 - Exchange codes - multiple entries for code with 3 fields -Exchange name (duplication) - Start Range and End Range.I want to return a list of phone numbers that fall into the range fora certain exchange.For example - MRSPOO has 4 entries in the Exchange Table with 4distinct ranges.  200-299, 307-788, 997-1102 and 2036-6698. I would like to be able to return all the phone numbers for a singleexchange code that I would select (using a query or a form)Please can you help.
0
Orange
3/9/2007 11:25:09 AM
access 16762 articles. 3 followers. Follow

2 Replies
610 Views

Similar Articles

[PageSpeed] 7

I'm struggling to understand the data in the examples.The ranges such as:    200 - 299are prefixes? So a phone number such as 202 5000 fits in that range? But you also have 4-digit prefixes? So 203 6777 fits in a *different* range? That seems inconsistent.Do some phone numbers contain more digits than others?-- Allen Browne - Microsoft MVP.  Perth, Western AustraliaTips for Access users - http://allenbrowne.com/tips.htmlReply to group, rather than allenbrowne at mvps dot org."Orange Clare" <slappawappa@yahoo.com> wrote in messagenews:1173439509.401648.140800@64g2000cwx.googlegroups.com...> Hi>> I have not done subqueries before so I am a bit confused.  I have two> tables>> 1 - Phone Numbers - (converted to be numeric) (with other fields for> later) and> 2 - Exchange codes - multiple entries for code with 3 fields -> Exchange name (duplication) - Start Range and End Range.>> I want to return a list of phone numbers that fall into the range for> a certain exchange.>> For example - MRSPOO has 4 entries in the Exchange Table with 4> distinct ranges.  200-299, 307-788, 997-1102 and 2036-6698.>> I would like to be able to return all the phone numbers for a single> exchange code that I would select (using a query or a form)>> Please can you help. 
0
Allen
3/9/2007 2:52:34 PM
On 9 Mar, 14:52, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:> I'm struggling to understand the data in the examples.>> The ranges such as:>     200 - 299> are prefixes? So a phone number such as 202 5000 fits in that range? But you> also have 4-digit prefixes? So 203 6777 fits in a *different* range? That> seems inconsistent.>> Do some phone numbers contain more digits than others?>> --> Allen Browne - Microsoft MVP.  Perth, Western Australia> Tips for Access users -http://allenbrowne.com/tips.html> Reply to group, rather than allenbrowne at mvps dot org.>> "Orange Clare" <slappawa...@yahoo.com> wrote in message>> news:1173439509.401648.140800@64g2000cwx.googlegroups.com...>>>> > Hi>> > I have not done subqueries before so I am a bit confused.  I have two> > tables>> > 1 - Phone Numbers - (converted to be numeric) (with other fields for> > later) and> > 2 - Exchange codes - multiple entries for code with 3 fields -> > Exchange name (duplication) - Start Range and End Range.>> > I want to return a list of phone numbers that fall into the range for> > a certain exchange.>> > For example - MRSPOO has 4 entries in the Exchange Table with 4> > distinct ranges.  200-299, 307-788, 997-1102 and 2036-6698.>> > I would like to be able to return all the phone numbers for a single> > exchange code that I would select (using a query or a form)>> > Please can you help.- Hide quoted text ->> - Show quoted text -Hi AllenThe 3-4 digit numbers are a simplification.  We have 10 or 11 digitnumbers to deal with and they are not all prefixes.  They are separateranges.HTHClare
0
Orange
3/9/2007 3:28:27 PM
Reply:

Similar Artilces:

Encrypting external config section
With .net 4.0 can you now encrypt external sections using the Aspnet_regiis.exe tool? I know in 2.0 you couldn't <connectionStrings file="ExternalWeb.config"> Or is there is a good way to do this without having to write your own exe to munge the xml? thanks, Hi Chuck, Currently, we have no documentation that describes the Aspnet_regiis.exe tool can be used to encrypt external sections in .NET 4.0. Another way to achieve this is to read the extarnal files and inject them inside the web.config. Please refer to the detailed steps in these two a...

Column Reference to External Source As a Variable
Can anyone help me convert the column referenced in the formula below into a variable that the user can define? More specifically, I have several columns that I need to read from an external workbook (Short_Billy.xls). Each column to the right of column C represents an additional day out in a 14 day projection from today (whose data is held in column C). In cell I5 of my active workbook (Inventory.xls), I would like the user to be able to enter a value representing the number of days out they would like to see the projection for (0=today=Column C, 1=Tomorrow=Column D, etc.). In cell I6, I...

Forwarding a specific e-mail to an external e-mail!
The owner of the company would like to forward an e-mail received from a specific customer's e-mail address to his external e-mail - This is the process - Our customer (cust@123.com) sends and e-mail to the owner of the company (owner@company.com). Whenever the owner of the company receives e-mail from our customer cust@123.com, the e-mail should automatically be forward to the owner's external e-mail (owner@yahoo.com) How do I do this? I tried a forwarding rule and it didn't work. Do you have any other suggestions on how to accomplish this? Thank you in advance for you...

Sql to find record in a hierarchical chain
Hi, lets say I have a table with 2 fields: myTable Field 1 - ref Field 2 - parentRef Now in the structure there could be numerous chains until you get to the top of the hieratchy (where the ref field = parentRef field). ie ref parentRef 111 222 222 333 333 444 444 444 so in this 444 is top of the chain (there will be many other records as well that are nothing to with 444 and are part of their own hierarchy). So how do I easily relate 111 to 444. Ie how do i find the ultimate top parent for a given 'ref' field. Hope anyone can give me some pointers ...

Auto Forward mail to a group of External addresses
Can someone please tell me how I create an Auto Forward to a group of external email addresses. I want to setup an auto forward on my exchange so that all mail sent to forward@mydomain.com is then forwarded to a group of email accounts such as me@hotmail.com, me@yahoo.com, user@mail.com. Can someone please tell me how I set this up on an exchange server. I know how to do it for an individual but I can't work out how I create an auto forward for a group of people. Thanks for any advice! what version Exchange? only the terminology is different...create custom recipients/contacts ...

CRM 4.0 External Connector
Can anyone point me to CRM 4.0 External Connector installation guide? Cheers, Mandy. "Mandy" <Mandy@discussions.microsoft.com> wrote in message news:BF297AE0-9446-46CB-91FA-CFF19A65F1DF@microsoft.com... > Can anyone point me to CRM 4.0 External Connector installation guide? The external connector is a license so nothing you need to install. -- Robert MacLean http://www.sadev.co.za thanks for update. would you know if there's any specs on how it integrates with crm? "Robert MacLean" wrote: > "Mandy" <Mandy@discussions.microsoft.com&g...

selecting a cell
I seem unable to select a single cell, or a single row--click on one in the normal manner, and the two below also highlight, then delete or whatever command is given. If I input a number/text, that just goes into the one cell. tapping F8 increases this to two wide and three high automatically selected. Also, very slow to do almost anything. Thanks Pat, Are you by any chance using Excel 2007? If so there is a known bug that causes multiple cell selection and I understand this has been reported to Microsoft. If you take the zoom level up and down this is reported to cl...

how can i set up my pop and smtp account using proxy server in ou.
"Abhishek" <Abhishek@discussions.microsoft.com> wrote in message news:90EE645B-97D9-4145-A368-E26743C547F9@microsoft.com... You'll need instructions from your ISP. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Stockholm Consulting Group/KSG http://www.scgab.com Microsoft OneNote FAQ: http://home.hawaii.rr.com/schorr/computers/onenotefaq.htm **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! ...

Query to hide duplicate records
I recall that this used to easy in previous versions (Unique values only ??), but in 2007, I can't get this to work at all. I have a table with our companie's job numbers in it. The job numbers show up multiple times because of different phases of the project: ProjNum ProjDescription 3077 Univ. of Vermont/UC/LEED 3077 Univ. of Vermont/University Commons: Building Fee 3077 Univ. of Vermont/University Commons: Excess Professiona... I need to jut have a single listing of each project number, otherwise, I get repeated records in the query that looks at this information (which ...

Setting Outlook settings to new server
We are in the process of moving mailboxes to a new server. Is there a utility or ad policy to automatically configure outlook to point to the new exchange server? Instead of manually configuring the users outlook profile. On Tue, 21 Nov 2006 11:15:01 -0800, Rich <Rich@discussions.microsoft.com> wrote: >We are in the process of moving mailboxes to a new server. Is there a utility >or ad policy to automatically configure outlook to point to the new exchange >server? Instead of manually configuring the users outlook profile. If you are moving the mailboxes in the same org vi...

Paper settings change for specific printer
I just installed access 2007 and my application uses reports to print labels. Specifically a Dymo turbo 400 and 30252 address labels. when i was using acess 2003 i specified to use a specific printer and everything was great. and when i copied updated FrontEnds to my networked computers every thing transfered and the labels printed fine however with 07 the "Specifc printer" has a different page size.. i fixed all the reports on my master FE but the changes didnt stay when i copied FE to other computers and im not sure where to go. i installed all the printers from the s...

outlook in sub-domain to set use root-domain question!!!
Dear Sir Please see below more details,(We are using special railway line between Head office in Taipei and branch office in Tao-Yuan) Head office in Taipei: aaa.com(Root domain) Dc server * 2(One of it is GC Server), Front-End Exchange 2003 *1, Back-End Exchange 2003 * 2(One is named mail1, another is named mail2 ) Branch office in Tao-Yuan: bbb.aaa.com(sub-domain) Dc Server *1(No GC Server,No Exchange Server) After using ADMT v3 Tool, when I transfer an account from root named aaa.com(ou) to bbb.aaa.com. After I ins...

Setting the default font and font size for Excel comments
Hi, Do you know how to set the default font and font size in Excel for the cell 'Comments'? I have looked in Options as well as in the registry to see if I can change the default from Tahoma (size 8) to another (i.e. Arial size 12). Your help would really be appreciated. Thanks, Clint - UK ...

Can I set SQL mirroring across WAN?
We have a 100 Mb dedicated DR link to another data center. Want to set up a SQL 2008 database to use mirroring from primary to a standby server at the remote data center. I did a ping test and here is the result. ping -n 100 -l 5120 <target IP> Ping statistics for <target IP> Packets: Sent = 100, Received = 99, Lost = 1 (1% loss), Approximate round trip times in milli-seconds: Minimum = 8ms, Maximum = 19ms, Average = 11ms is the network latency good enough for database mirroring? Does anyone know the min. requirement? Field experience will be fine too....

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

Excel for Customer History Records
Hello, can anyone direct me to an existing template that can be used to track customer enquiries and feedback. It needs an efficient way of recording follow up & response conversations. In a standard workbook layout if you type loads of text into a column (say 'follow up' for instance), it pushes all the rows so far down the spreadsheet it quickly becomes unwieldy. If anyone can help I could e mail an example of what we're using now (awful) and after having a chuckle at our expense maybe you can suggest something more appropriate. We're running 2003. Many thanks --...

Highlight color for selected items
How can I find out what color is used for highlighting selected items (for example in Windows Explorer)on a users computer? Normally it is a blue color, but a user can change that. "Urban Olars" <anonymous@discussions.microsoft.com> wrote in message news:071c01c3c941$3db2c890$a401280a@phx.gbl... > How can I find out what color is used for highlighting > selected items (for example in Windows Explorer)on a users > computer? > Normally it is a blue color, but a user can change that. See if it's GetSysColor/COLOR_HIGHLIGHT. -- Jeff Partch [VC++ MVP] Take a ...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

Routing Restrictions button unavailable (gray) no matter what options selected in IMS-->Routing tab
I am running Exchange 5.5 SP4. In Connections-->IMS-->Routing Tab, my "Routing Restrictions" button is unavailable (greyed-out) no matter what options I select. Any suggestions? Thank you. Try installing Exch Admin prog on other machine, apply SP4 and see if you can change it from there. Post back with details. ryanadmin wrote: > I am running Exchange 5.5 SP4. In Connections-->IMS-->Routing Tab, my > "Routing Restrictions" button is unavailable (greyed-out) no matter > what options I select. Any suggestions? Thank you. ...

Code about setting Y Axes scale.
I couldn't understand the lines between 35-42, why he thought the dbMinScale after the change is the one he want. What the logic of this code section. Any one who could understand please help me. thanks in advance!:) Private Sub USP_SetYAxesScal(ByVal i_strWSName As String, ByVal i_strChartName As String, Optional ByVal i_intDecimal As Integer = 2) Dim dbMaxScale As Double, dbMinScale As Double, i As Integer, j As Integer Dim xTargetChart As Chart, vTemp() As Variant, blnInit As Boolean, dbMax As Double, dbMin As Double 1 On Error Resume Next 'get max/m...

EXTRACTING UNIQUE RECORD BASED ON CONDITION
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C781BF.08FB92F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello everyone! I would like to extract unique records based on a condition. For = example, how to extract unique record from column 'B' when column 'A' = has "AP" or any other desired condition. The data is as follows: A B =20 MI 70056542=20 MI 70056543=20 AP PATRICK CUDAHY INCORPORATED=20 AP PATRICK CUDAHY INCORPORATED=20 AP SUGAR CREEK ...

MS Exchange and external mail relay
In our organization we are planning if moving from Linux (postfix) to MS Exchange server. We plan to implement Postfix (in DMZ zone) as a mail relay for Exchange server (in LAN).We have about 400 users on the same external domain like @company.com. The trick is that about 100 of users only need mail address (not also a domain account), so we're thinking if it is possible to create 300 of our users of @comany.com domain on Exchange server, all other users that need only company mail (also @comapany.com) will have mailboxes on mail relay (postfix), not on Exchange server. Since it...

Setting Text in a ComboBox
Hi, Does anybody know why I cannot set the text in the edit control of a CBS_DROPDOWN style CombBox control using SetWindowText()? I was able to change the text in the edit control part of the ComboBox manually. Thanks, Vincent. I don't know why can't you? Post some code. You should be able to m_MyCombo.SetWindowText("The Text"); Ali R. "Vincent Yu" <anonymous@discussions.microsoft.com> wrote in message news:5e2b01c3ad48$5309d630$a601280a@phx.gbl... > Hi, Does anybody know why I cannot set the text in the > edit control of a CBS_DROPDOWN style ...

Include external DLL's path
Hello, I made an application which uses some 3rd party dll's. I want to put them in a separate folder next to my .exe (dlls are 16 in no. hence don't want to put them at the same level as the .exe to avoid cluttering of files). How is it possible for me to make my .exe utilize these dll's? I understand that a way out is to put the dll path in the CLASSPATH (or PATH?) of the system. I don't know how to do that. Is there a way out so that i don't have to set the CLASSPATH (or PATH, if so)? thanks in advance, Divya Rathore (for email ID, remove all the underscores) >I...

excel 2007
I have data that looks something like this account#, invoice amount 1, 50 1, 70 1, 80 2, 10 2, 50 2, 52 2, 50 3, 10 4, 30 I need a way to combine all the invoice totals for each account so I have results like this 1, 200 2, 162 3, 10 4, 30 Any suggestions on how to go about doing this? I thought I had done something similar to this with filters before in Excel 2003, but I can't figure out how I did it. Thanks! You can use subtotal or a pivot table in the pivot put the account numbers in the row field and the invoice amount in the data, in subtotal use at each change in account numb...