Conditional Query?? (1 Viewer)

SteveTT · Aug 25, 2007

My Access is very rusty and I really can't get my head round how to do this!

I have a simple caller i.d. system which detects incoming telephone calls. I have been running an append query where the incoming call number is a query parameter against a table of customer numbers and names. The append query basically adds call numbers, names and times to a call log table.

This all works fine! ...however I realise that if the incoming call number is not in the table of customers numbers and names, no log entry is created. What I wish to do is, if the number is not found to append it anyway together with the time of call. Basically if the query fails to find a result, append details anyway albeit without a customer name.

I'm sure this must be simple but I'm going round in circles. Any thoughts anyone?

Thanks

ajetrumpet · Aug 25, 2007

SteveTT said:
This all works fine! ...however I realise that if the incoming call number is not in the table of customers numbers and names, no log entry is created. What I wish to do is, if the number is not found to append it anyway together with the time of call.

Steve, this could be happening if the query looks something like...

Code:

INSERT INTO [source log table].[source log fields]
SELECT [fields]
FROM [table that passing records]
WHERE [criteria field] IN (SELECT [source log fields] FROM [source log table]);

This code only has one side to the criteria, kind of like an incomplete "IFF" statement. The problem you're describing should work with something like...

Code:

INSERT INTO [source table].[source fields]
SELECT [appended fields]
FROM [table that is passing records]
WHERE [appended fields] IN (SELECT [source log fields]
FROM [source log table]) OR [appended fields] NOT IN (SELECT [source log fields]
FROM [source log table]);

I guess you could also use the <> in the criteria too as a "not equal" operator...

Code:

WHERE [appended fields]<>[source log table].[source log fields]

SteveTT · Aug 27, 2007

Got it working and Thanks

-J

Many thanks for your response, it certainly provoked me to think it all through more clearly and sorry for the delay...I had to go away for 2 days.

I'm not sure if I made it clear that if the CallerId number was not found in the TelephoneNumbers table I wanted to add it to the CallLog table anway but with no customer details (as they are unknown)

What I have ended up in doing is defining a Mr Unknown with a number 99999999 in the TelephoneNumbers table.

The SQL is now:

INSERT INTO CallLog ( [TEL NO], [CUST CODE] )
SELECT [Forms]![Caller ID Demo]![viewcall] AS Ctel, TelephoneNumbers.[CUST CODE]
FROM TelephoneNumbers
WHERE (((TelephoneNumbers.[TEL NO])=[Forms]![Caller ID Demo]![viewcall])) OR (((TelephoneNumbers.[TEL NO])="99999999") AND ((Exists (SELECT TelephoneNumbers.[TEL NO]
FROM TelephoneNumbers
WHERE (((TelephoneNumbers.[TEL NO])=[Forms]![Caller ID Demo]![viewcall]))))=False));

[viewcall] is obviously the text box on the form that receives the CallerID number. This is appended to the CallLog table. If [viewcall] is not found in the TelephoneNumber table then the 99999999 value is used and Mr Unknown is appended to the CAllLog

Again Many thanks

SteveT

ajetrumpet · Aug 27, 2007

As long as you got it working Steve, you know more about your setup than I do....a lot of times, problems just need time to be fixed by logical thinking. I don't know about you, but in the work environment, that's kinda hard to do nowadays!!

Good Luck.

Conditional Query?? (1 Viewer)

SteveTT

New member

ajetrumpet

Banned

SteveTT

New member

ajetrumpet

Banned

Similar threads

Users who are viewing this thread