Select query help

I have tblLog. It's fields are:
Call: text
Freq: single
Mode: text
CID: text
QSL_R: text
Credited: T/F

I need the SQL to return all records where 'Credited' is false, 'QSL_R' <> NULL or 
blank, and whose combination of 'Freq' and 'Mode' for a given 'CID' have 'Credited' = 
false, but not if that record has a 'mate' whose 'CID', 'Freq/Mode', 'QSL_R' all 
match, and 'Credited' is True.

I'm not sure if my description is understandable, so here is an example.

Call    Freq    Mode  CID   QSL_R   Credited
1A0KK   14.210  SSB   1A0    Y       True
1A0XX   14.210  SSB   1A0    Y       False
1A0LY   14.310  CW    1A0    Y       False
1A0KK    7.009  CW    1A0    Y       True
1A0TT    3.506  TTY   1A0            False
NF4L    18.080  TTY   K      Y       False
NF1Y    18.083  TTY   K              False
WA4B    7.080   TTY   K      Y       True
K4UTE   18.125  SSB   K      Y       False
AB4UF   14.230  SSB   K      Y       False

These records should be in the return set.
1A0LY   14.310  CW    1A0    Y       False
NF4L    18.080  TTY   K      Y       False
K4UTE   18.125  SSB   K      Y       False
AB4UF   14.230  SSB   K      Y       False

Thanks,
Mike
0
MikeR
4/26/2010 2:50:01 AM
access 16762 articles. 3 followers. Follow

4 Replies
863 Views

Similar Articles

[PageSpeed] 10

On Sun, 25 Apr 2010 22:50:01 -0400, MikeR <nf4lNoSpam@pobox.com> wrote:

>I have tblLog. It's fields are:
>Call: text
>Freq: single
>Mode: text
>CID: text
>QSL_R: text
>Credited: T/F
>
>I need the SQL to return all records where 'Credited' is false, 'QSL_R' <> NULL or 
>blank, and whose combination of 'Freq' and 'Mode' for a given 'CID' have 'Credited' = 
>false, but not if that record has a 'mate' whose 'CID', 'Freq/Mode', 'QSL_R' all 
>match, and 'Credited' is True.

A Query such as

SELECT * FROM tblLog
WHERE [Credited] = False
AND [QSL_R] IS NOT NULL
AND ([Credited] = FALSE AND NOT EXISTS (SELECT [Call] FROM tblLog AS X
WHERE X.Call = tblLog.Call
AND X.Credited = True));


should work. The NOT EXISTS clause may be slow, and could probably be replaced
by a JOIN but I'd need to mock up a table to test it.

-- 

             John W. Vinson [MVP]
0
John
4/26/2010 4:13:50 PM
John W. Vinson wrote:
> On Sun, 25 Apr 2010 22:50:01 -0400, MikeR <nf4lNoSpam@pobox.com> wrote:
> 
>> I have tblLog. It's fields are:
>> Call: text
>> Freq: single
>> Mode: text
>> CID: text
>> QSL_R: text
>> Credited: T/F
>>
>> I need the SQL to return all records where 'Credited' is false, 'QSL_R' <> NULL or 
>> blank, and whose combination of 'Freq' and 'Mode' for a given 'CID' have 'Credited' = 
>> false, but not if that record has a 'mate' whose 'CID', 'Freq/Mode', 'QSL_R' all 
>> match, and 'Credited' is True.
> 
> A Query such as
> 
> SELECT * FROM tblLog
> WHERE [Credited] = False
> AND [QSL_R] IS NOT NULL
> AND ([Credited] = FALSE AND NOT EXISTS (SELECT [Call] FROM tblLog AS X
> WHERE X.Call = tblLog.Call
> AND X.Credited = True));
> 
> 
> should work. The NOT EXISTS clause may be slow, and could probably be replaced
> by a JOIN but I'd need to mock up a table to test it.
> 

Thanks, John.

After playing with your SQL, it's not quite what I want. I think I probably explained 
poorly, plus I realized doing anything with 'Freq' was going to be overly complex 
because what I'm actually interested in involves a range of 'Freq'. I added a field 
called 'Band' (number) to describe a range of 'Freq' (eg a 'Freq' between 13.9 and 15 
falls in the 20 'Band').

Speed isn't an issue, it executes fast enough.

I think the field 'Call' is irrelevant. What I really wanted was the country the call 
is in, identified by 'CID' ( Country ID). I changed your query to this:

SELECT *
FROM Log
WHERE [Credited] = False
AND [QSL_R] IS NOT NULL and trim(len([qsl_r] + "")) > 0
AND ([Credited] = FALSE AND NOT EXISTS (SELECT [CID] FROM Log AS X
WHERE X.CID = Log.CID
AND X.Credited = True))
ORDER BY cid;

which still isn't right. Maybe another example with reasons would help. If the table 
is ordered by CID, the records are

Call    Freq    Mode  CID   QSL_R Band   Credited
1A0KK   14.210  SSB   1A0    Y     20      True
1A0KK   1O.121  CW    1A0    Y     30      True
1A0KK   18.165  SSB   1A0    Y     17      False
1A0KK   21.295  SSB   1A0    Y     15      False
1A0KK   14.160  SSB   1A0    Y     20      False
1A0KK   14.188  SSB   1A0          20      False

The first 2 should not be chosen because 'Credited' is true.
The third and fourth should be chosen because 'Credited' is false and their band and 
mode do not match another record with the same 'CID' whose 'Credited' is false.
The fifth should not be chosen because it's 'Band' and 'Mode' and 'CID' do match a 
record whose 'Credited' is true.
The sixth would be rejected because 'QSL_R' is not filled.

None of these were selected by our query.

There is also a problem with 'Mode' as it includes ranges also as for example USB, 
LSB, SSB, PHO, AM, FM should all be considered the same mode.

It seems to me that I have a table design problem that may make all this very 
difficult. I could add another field to do with 'Mode' what I did with 'Freq'.

What's your opinion?

I can send you a .zip of the .mdb that contains this table if it would help. The .zip 
is 270K.
0
MikeR
4/26/2010 8:32:30 PM
On Mon, 26 Apr 2010 16:32:30 -0400, MikeR <nf4lNoSpam@pobox.com> wrote:


>> 
>
>Thanks, John.
>
>After playing with your SQL, it's not quite what I want. I think I probably explained 
>poorly, plus I realized doing anything with 'Freq' was going to be overly complex 
>because what I'm actually interested in involves a range of 'Freq'. I added a field 
>called 'Band' (number) to describe a range of 'Freq' (eg a 'Freq' between 13.9 and 15 
>falls in the 20 'Band').

It sounds like you may need a non-equi join: a table with fields Band, LowFreq
and HighFreq. You can join it to look up the Band value applicable to this
Freq.

>Speed isn't an issue, it executes fast enough.
>
>I think the field 'Call' is irrelevant. What I really wanted was the country the call 
>is in, identified by 'CID' ( Country ID). I changed your query to this:

I have no idea what to do with that information.

>SELECT *
>FROM Log
>WHERE [Credited] = False
>AND [QSL_R] IS NOT NULL and trim(len([qsl_r] + "")) > 0
>AND ([Credited] = FALSE AND NOT EXISTS (SELECT [CID] FROM Log AS X
>WHERE X.CID = Log.CID
>AND X.Credited = True))
>ORDER BY cid;
>
>which still isn't right. Maybe another example with reasons would help. If the table 
>is ordered by CID, the records are
>
>Call    Freq    Mode  CID   QSL_R Band   Credited
>1A0KK   14.210  SSB   1A0    Y     20      True
>1A0KK   1O.121  CW    1A0    Y     30      True
>1A0KK   18.165  SSB   1A0    Y     17      False
>1A0KK   21.295  SSB   1A0    Y     15      False
>1A0KK   14.160  SSB   1A0    Y     20      False
>1A0KK   14.188  SSB   1A0          20      False
>
>The first 2 should not be chosen because 'Credited' is true.
>The third and fourth should be chosen because 'Credited' is false and their band and 
>mode do not match another record with the same 'CID' whose 'Credited' is false.
>The fifth should not be chosen because it's 'Band' and 'Mode' and 'CID' do match a 
>record whose 'Credited' is true.
>The sixth would be rejected because 'QSL_R' is not filled.
>
>None of these were selected by our query.

I'd need to spend a lot more time than I have available to dope through all
this!

>There is also a problem with 'Mode' as it includes ranges also as for example USB, 
>LSB, SSB, PHO, AM, FM should all be considered the same mode.
>
>It seems to me that I have a table design problem that may make all this very 
>difficult. I could add another field to do with 'Mode' what I did with 'Freq'.
>
>What's your opinion?
>
>I can send you a .zip of the .mdb that contains this table if it would help. The .zip 
>is 270K.

Well, that would go fairly far beyond what I'd consider reasonable for unpaid
volunteer work, and I'm not taking consulting clients at present. Sorry! Maybe
you should repost in a new thread and see if there are other volunteers who
have more time.
-- 

             John W. Vinson [MVP]
0
John
4/27/2010 1:06:08 AM
John W. Vinson wrote:
> On Mon, 26 Apr 2010 16:32:30 -0400, MikeR <nf4lNoSpam@pobox.com> wrote:
> 
> 
>> Thanks, John.
>>
>> After playing with your SQL, it's not quite what I want. I think I probably explained 
>> poorly, plus I realized doing anything with 'Freq' was going to be overly complex 
>> because what I'm actually interested in involves a range of 'Freq'. I added a field 
>> called 'Band' (number) to describe a range of 'Freq' (eg a 'Freq' between 13.9 and 15 
>> falls in the 20 'Band').
> 
> It sounds like you may need a non-equi join: a table with fields Band, LowFreq
> and HighFreq. You can join it to look up the Band value applicable to this
> Freq.

That'll work!
> 
>> Speed isn't an issue, it executes fast enough.
>>
>> I think the field 'Call' is irrelevant. What I really wanted was the country the call 
>> is in, identified by 'CID' ( Country ID). I changed your query to this:
> 
> I have no idea what to do with that information.

Sorry, what I did was use 'CID' where you were using 'Call'
> 
>> SELECT *
>>FROM Log
>> WHERE [Credited] = False
>> AND [QSL_R] IS NOT NULL and trim(len([qsl_r] + "")) > 0
>> AND ([Credited] = FALSE AND NOT EXISTS (SELECT [CID] FROM Log AS X
>> WHERE X.CID = Log.CID
>> AND X.Credited = True))
>> ORDER BY cid;
>>
>> which still isn't right. Maybe another example with reasons would help. If the table 
>> is ordered by CID, the records are
>>
>> Call    Freq    Mode  CID   QSL_R Band   Credited
>> 1A0KK   14.210  SSB   1A0    Y     20      True
>> 1A0KK   1O.121  CW    1A0    Y     30      True
>> 1A0KK   18.165  SSB   1A0    Y     17      False
>> 1A0KK   21.295  SSB   1A0    Y     15      False
>> 1A0KK   14.160  SSB   1A0    Y     20      False
>> 1A0KK   14.188  SSB   1A0          20      False
>>
>> The first 2 should not be chosen because 'Credited' is true.
>> The third and fourth should be chosen because 'Credited' is false and their band and 
>> mode do not match another record with the same 'CID' whose 'Credited' is false.
>> The fifth should not be chosen because it's 'Band' and 'Mode' and 'CID' do match a 
>> record whose 'Credited' is true.
>> The sixth would be rejected because 'QSL_R' is not filled.
>>
>> None of these were selected by our query.
> 
> I'd need to spend a lot more time than I have available to dope through all
> this!
> 

Thanks for your time and knowledge. I think you've given me enough of a boot to be 
able to complete it.
>> There is also a problem with 'Mode' as it includes ranges also as for example USB, 
>> LSB, SSB, PHO, AM, FM should all be considered the same mode.
>>
>> It seems to me that I have a table design problem that may make all this very 
>> difficult. I could add another field to do with 'Mode' what I did with 'Freq'.
>>
>> What's your opinion?
>>
>> I can send you a .zip of the .mdb that contains this table if it would help. The .zip 
>> is 270K.
> 
> Well, that would go fairly far beyond what I'd consider reasonable for unpaid
> volunteer work, and I'm not taking consulting clients at present. Sorry! Maybe
> you should repost in a new thread and see if there are other volunteers who
> have more time.

I understand. Again thank you!
0
MikeR
4/27/2010 11:36:51 AM
Reply:

Similar Artilces:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

Build Dynamic Query from Form
I am trying to build a dynamic Query from a Form. I keep getting an error that reads ‘Object qryFilter already exists’ I suspect it has something to do with the string of dates being passed to the Query; strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom] And [Forms]![SearchForm]![cboTo])" I am trying to add a means for a user to Query by Customer and Trader AND all records between two dates. This was working fine for Customer and Trader; when I added in the code to filter by dates I started having problems. I know the SQL will be li...

Please help..with a formula. I don't know code.
I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

excel, worksheet, set print area, position selection on printed pa
Office 07, Excel. Print area is set, print preview displays selection, always on far left of page. I want to choose the position on the page, eg centered or right aligned etc. Help please. Hi, To get your printout to center on the page chose Page Layout, click the Page Setup quick launch button (bottom right corner of the Page Setup group). Choose the Margins tab, click Horizontal. There is no command to right align a printout, but you can change the left margin to force the printout to the left. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Eve Al...

Need a default email account for all users, need help.
I have a tablet PC running WinXP Tablet with Outlook 2003. This tablet will connect to our exchange server via VPN. How can I set it up so that everyone that logs onto their account can access one (the same) email account. The problem is that I dont know at this point all of the users however anyone using the tablet will use one generic email account. So how can I set Outlook to default to this account so that no matter who logs on they will use this account? Thanks! Shane ...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

DEADLINE... PLEASE HELP! Stacked Bar chart?
I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Update Query
Access 2003 XP SP2 I am having a problem with an update query. Table is in a one-to-one relationship, referentail integrity and cascading data are checked. (The fileds I want to update are not in both tables) Table name= payForward Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc Oct-Sep fields are yes/no type I want to "select" a field (Oct-Sep) via a query parameter and repalce "yes" with "no". Here is my query: UPDATE payForward SET [Enter month]=No The messages I get is 'operation must use an updateable query' Wh...

Hyperlink File Help
I am needing some major help. I have a file with hyperlinks in column F that link to a file on our server. I am needing to test to see if the file exists and if it does, copy the file to a folder in my documents called (CapturedFiles) and if it doesn't format the cell color to red. Can VBA do this and if so how? Any help would be greatly appreciated. Thanks in advance. Fileserver or webserver ? Tim On Nov 23, 7:20=A0am, Aaron <Aa...@discussions.microsoft.com> wrote: > I am needing some major help. =A0I have a file with hyperlinks in column = F that > l...

Help please user not showing in 5.5 GAL but is in exchange 2003 GA
Up until today I have been bable to add users fine and their address would appear in both the 5.5 GAL and the exchange 2003 GAL. Is a single site with 2 5.5 servers and 1 exchange 2003 server. When I add a new user now through users and computers and put the mailbox on the new exchange 2003 server the user gets his email addresses and appears in the GAL on the 2003 server but people connected the the old 5.5 servers cannot see it. When I open the 5.5 exchange admin tool again if connected to one of the old 5.5 server I cannot see the person I just created but when connected the the 20...

VLookup #VALUE! error help needed to resolve
The following is the funcation I have: =VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0) I have all the columns formatted the same; as in the column that the function is using to lookup is text and so is the column for this figure in order to pull back the appropriate answer. I have keyed the data instead of having links. I have replaced the final '0' with TRUE & FALSE then put it back. I have formatted the columns for text and for numbers. But I am getting the #VALUE! error in SOME of the cells NOT all of the cells. I don't know what else to d...

help with a sub
Hi, can anybody tell me why the following code fails at FormatConditions.Add Private Sub CommandButton1_Click() Dim Sh As Worksheet Dim lngLastRow As Long Set Sh = ActiveWorkbook.ActiveSheet lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row Range("A4:E" & lngLastRow).Activate Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(MOD(ROW(),2)=0" Selection.FormatConditions(1).Interior.ColorIndex = 24 End Sub Thanks -- Traa Dy Liooar Jock You have an extra open paren just before MOD: &qu...

cdrom.sys corrupt in Win7
Yesterday Win7 decided to no longer show my 2 LiteOn DVDRW drives. I've tried to re-install/repair the driver (6.1.7600.16385) and everytime I get the same response = my current driver is good. BUT, then when I check with Device Manager, it shows that the drives are not working. Can anyone help me get a new cdrom.sys installed into the system32/drivers folder? Booting up with the Win7 DVD will work. But I can't find the cdrom.sys on the disk. No other repair options are there to get this fixed. Help would certainly be appreciated. I don't want to have to start all o...

Exchange 2003 SMTP QUIT
= = = = = = = = = = = = = = = = = = = = = = = = = = = PROBLEM: Problem is that OUR SERVER is sending QUIT-, instead of sending MAIL FROM: MY Server open a SMTP connection REMOTE Server says 220 .. MY Server says EHLO to REMOTE Server REMOTE Server says 250 ... MY Server then say QUIT ! (instead of MAIL FROM ....) We have::Exchange 2003 , cu SP1, pe Windows 2003.. Exchange has also IMF (spam filter from Microsoft) and Symantec Mail Security for Exchange 4.5. all PTR is installed and working OK. The SMTP Server is workin OK a while, then it start opening a lot of connections (7-10 /sec) t...

how to set up a query
I am using sql server 2005 express and have 3 tables Table1 Dept_Id (primary key) Dept_Name Table2 Employee_Id (primary key) Dept_Id (foreign key with table1) Employee_Name Table3 WorkSchedule_Id (primary key) Employee_Id (foreing key with table 2) Date_To_Work (date type) I want to list all the Departments (Dept_Name) that do not have anyone scheduled to work on a particular date (ie '1/20/2010' ) Any help would be appriciated. Thanks in advance, RABMissouri2010 Try this: SELECT dept_name FROM Table1 AS D WHERE NOT EXISTS(SELECT * ...

Excel comparative query
I'm respectfully requesting assistance with the correct formula to use for the following query: I'd searching all of column A on spreadsheet 1 to see if a value in column A of spreadsheet 2 is there, and placing a result X (or another) next to (adjacent cell) the confirmed value on spreadsheet 2. Please help if you can. Chris Hi Chris see your answer in puclic.excel -- Regards Frank Kabel Frankfurt, Germany Chris wrote: > I'm respectfully requesting assistance with the correct formula > to use for the following query: > > I'd searching all of column A on ...

Currency Format Lost in Union Query
Thanks to John Spencer, I resolved one problem in my union query (Thank you, John). But I have another problem. Some of my fields in my union query are currencies and others percents. I see both formats are lost. I am sure it is a union query problem, because when I ran an individual query, none of the formats were lost. Is there any way I can keep currency and percent formats without writing, FORMATCURRENCY, etc for each field? Thank you. What data types are these fields? The field in a UNION query will normally take on the data type of the field in the first SELECT. So, if ...

I need help
Here is my problem. A couple of years ago I began playing around with money. But I have come to the conclusion that I really do like the services, but I now have a bunch of acct. info that should have been input in a better manner. I want to know how do I delete all of the old info so that I can start fresh. I deleted that money program once and reloaded it but it retrieved all of the old garbage that I did not want. Your help is greatly appreciated. Sincerly, Matt On Tue, 19 Aug 2003 20:58:14 -0700, "Matt" <mrisher4@hotmail.com> wrote: >Here is my problem. A c...

Help styles
With great difficulty I am now creating documents with some styles.and outline numbering Now when I copy a table from another document into this document I get 1.1.1.1 and similar numbering all over the table. How do I accomplish the copy without the numbering inside the table? afd Try this: Select the table cells and press Ctrl+Shift+N. This (re-)applies the Normal style to text. -- Stefan Blom Microsoft Word MVP "afdmello" <afdmello@hotmail.com> wrote in message news:%23R7DwkCdKHA.4952@TK2MSFTNGP06.phx.gbl... > With great difficulty I a...

Getting all Sproc through one query
Hi all, can anyone help in getting below information from all stored procedure from one DB we have 100 procedure in one DB of sql server 2005.instead if of manully getting info for each procedure i want all below details in one shot or one query Procedure Name : Input Parameter : Output Parameter : Called By : Calls : Dependent tables : Thanks in advance ...

Need help with cursor
Hello, I have a small GUI monitor application that doesn't have any controls on it. Basically its a frame with a title, menu bar, and client area. I dynamically size the frame for the contents of the client. The client window contains some status information about a service. I've noticed that the cursor gets changed to inappropriate values when the cursor is moved into the client area. For instance, moving the cursor in from the side may leave the cursor looking like the sideways-resize cursor. My application is written in MS VC++ v6.0. What events do I need to hook in...

!!!!!!!!!!!!! HELP THE CHILDRENS !!!!!!!!!!!!!!!!!!!!!!!!!!4
http://free.x3.hu/charity ...

Listbox Help (remove selected item)
This should be easy but I can not get it to work. I have a simple listbox with Row Source Type property of value/list. It is not mult select. I am using .additem to load the listbox and am not using the itemdata property. I want to be able to select an item in the listbox, click a button on the form to get the value in column 1 of the selected item, then remove the selected item from the list box. Thanks in advance for any help "sk" <sk@discussions.microsoft.com> wrote in message news:1BB03B01-F56E-4CDE-83DF-BF6550AD8593@microsoft.com... > This should b...

Installation CRM 1.2 on SBS2003 SP1 fails, please help
At the end of my installation I get an error windows with; Setup was unable to install Microsoft CRM Server Setup was unable to provision your organisation Setup was unable to create user settings. Access is denied. (80070005) Active Directory is in native mode I am installing as the administrator (domain admin) security account for services in Local System account Local System account and computer are added to the Pre Windows 2000 comp group Thanks in advance for any support Marc Did you enter your organization name precisely the same way it appears on your CRM 1.2 licenses? -- Matt ...