EXCEPTION QUERY - PLEASE HELP

  • Thread starter Thread starter KLZA
  • Start date Start date
K

KLZA

Hi, I'm running a query that includes three fields, A,B, C. I need
all records from the A column, except when B and C are equal to each
other but not equal to A... Can anyone help? This seems fairly
simple but I'm out of answers...
 
1) create a calculated field in the design mode for the query. Have
the calculation be something like:

UseFlag:iif([tblename]! = ([tblename]![C] AND ([tblename]!
<> ([tblename]![A],"Skip","Use")

2) Place criteria for that calculated filed to be "Use"


Ron
 
Based on your description, I think what you want is something like:

Select [A], , [C]
From yourTable
WHERE NOT ( = [C] AND [A] <> )

If any of these values can be NULL, then I'd recommend trying:

Select [A], , [C]
From yourTable
WHERE NOT (NZ() = NZ([C]) AND NZ([A]) <> NZ())

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Perhaps

SELECT A, B, C
FROM SomeTable
WHERE NOT(B=C AND A<>B)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Is there some reason you are copying my recommendations, besides great minds
thinking alike?

;-)

--

Dale

email address is invalid
Please reply to newsgroup only.
 
Based on your description, I think what you want is something like:

Select [A], , [C]
From yourTable
WHERE NOT ( = [C] AND [A] <> )

If any of these values can be NULL, then I'd recommend trying:

Select [A], , [C]
From yourTable
WHERE NOT (NZ() = NZ([C]) AND NZ([A]) <> NZ())

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



KLZA said:
Hi,  I'm running a query that includes three fields, A,B, C.  I need
all records from the A column, except when B and C are equal to each
other but not equal to A...  Can anyone help?  This seems fairly
simple but I'm out of answers...- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...


Thanks.. How do I include this select statement in my existin query?
 
Based on your description, I think what you want is something like:
Click to expand...
Select [A], , [C]
From yourTable
WHERE NOT ( = [C] AND [A] <> )
Click to expand...

If any of these values can be NULL, then I'd recommend trying:
Click to expand...
Select [A], , [C]
From yourTable
WHERE NOT (NZ() = NZ([C]) AND NZ([A]) <> NZ())
email address is invalid
Click to expand...
Click to expand...

Hi. My SQL statement is fairly complex already. I'm interested in
results from columns A B C.. I'm not surehow to massage this into the
existing statement below:

SELECT CommercialFinanceCommissionReport.[COF ID], EMPLOYEES.EIN,
CommercialFinanceCommissionReport.[COLUMN A],
CommercialFinanceCommissionReport.[COLUMN B],
CommercialFinanceCommissionReport.[COLUMN C],
CommercialFinanceCommissionReport.MRR,
CommercialFinanceCommissionReport.[Install Fee],
CommercialFinanceCommissionReport.Order,
CommercialFinanceCommissionReport.[CSD Validated],
CommercialFinanceCommissionReport.Commercial, EMPLOYEES.[Start Date],
DateDiff("m",[Start Date],[CSD VALIDATED]) AS [Accelerator Month], IIf
([Accelerator Month]<6,"3",IIf([Accelerator Month]=7,"2.5",IIf
([Accelerator Month]=8,"2.5",IIf([Accelerator Month]=9,"2.5",IIf
([Accelerator Month]>9,"2","UNDECLARED"))))) AS ACCELERATOR
FROM CommercialFinanceCommissionReport INNER JOIN EMPLOYEES ON
CommercialFinanceCommissionReport.[COLUMN A] = EMPLOYEES.[Full Name]
WHERE (((CommercialFinanceCommissionReport.[COLUMN A])=[Forms]!
[MAINFORM]![BDM]) AND ((CommercialFinanceCommissionReport.Order) Like
"New Order" Or (CommercialFinanceCommissionReport.Order)="Change Order-
Upgrade") AND ((DatePart("m",[CSD VALIDATED]))=[Forms]![MAINFORM]!
[MONTH]))
WITH OWNERACCESS OPTION;
 
Flattery! Are you flattered?

I didn't see your response when I wrote mine, but obviously a solution is a
solution. I was actually trying to wrap my mind around a solution that would
not involve using NOT. But then anything I was coming up with looked more
complex than what you (and I) proposed.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
You should be able to tack on:

AND NOT ([Column B] = [Column C] AND [Column Z] <> [Column B])
to the WHERE clause, right before the "WITH OWNER ACCESS OPTION" clause

A couple of other notes.
1. With table names as long as yours are, I'd start aliasing them so that
CommercialFinanceCommissionReport.[Column A] looks like: CFCR.[Column A]

2. Instead of using Nested IIF statements against your [Accelerator Month]
field, you might consider using the Switch function (it is a little easier to
read).

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



KLZA said:
Based on your description, I think what you want is something like:
Click to expand...
Select [A], , [C]
From yourTable
WHERE NOT ( = [C] AND [A] <> )
Click to expand...

If any of these values can be NULL, then I'd recommend trying:
Click to expand...
Select [A], , [C]
From yourTable
WHERE NOT (NZ() = NZ([C]) AND NZ([A]) <> NZ())
email address is invalid
Click to expand...
Click to expand...

Hi. My SQL statement is fairly complex already. I'm interested in
results from columns A B C.. I'm not surehow to massage this into the
existing statement below:

SELECT CommercialFinanceCommissionReport.[COF ID], EMPLOYEES.EIN,
CommercialFinanceCommissionReport.[COLUMN A],
CommercialFinanceCommissionReport.[COLUMN B],
CommercialFinanceCommissionReport.[COLUMN C],
CommercialFinanceCommissionReport.MRR,
CommercialFinanceCommissionReport.[Install Fee],
CommercialFinanceCommissionReport.Order,
CommercialFinanceCommissionReport.[CSD Validated],
CommercialFinanceCommissionReport.Commercial, EMPLOYEES.[Start Date],
DateDiff("m",[Start Date],[CSD VALIDATED]) AS [Accelerator Month], IIf
([Accelerator Month]<6,"3",IIf([Accelerator Month]=7,"2.5",IIf
([Accelerator Month]=8,"2.5",IIf([Accelerator Month]=9,"2.5",IIf
([Accelerator Month]>9,"2","UNDECLARED"))))) AS ACCELERATOR
FROM CommercialFinanceCommissionReport INNER JOIN EMPLOYEES ON
CommercialFinanceCommissionReport.[COLUMN A] = EMPLOYEES.[Full Name]
WHERE (((CommercialFinanceCommissionReport.[COLUMN A])=[Forms]!
[MAINFORM]![BDM]) AND ((CommercialFinanceCommissionReport.Order) Like
"New Order" Or (CommercialFinanceCommissionReport.Order)="Change Order-
Upgrade") AND ((DatePart("m",[CSD VALIDATED]))=[Forms]![MAINFORM]!
[MONTH]))
WITH OWNERACCESS OPTION;
Thanks.. How do I include this select statement in my existin query?- Hide quoted text -

- Show quoted text -
Click to expand...
Click to expand...
 
Dale, My name is Ms. S and I am looking for help with probably a similar
issue. I haven't used Access in a long time so please bare with me. I have
2 table with similar information. Table 1 has the most recent records
whereas Table 2 has the newly added columns. I have included the newly
columns in table 1 but now want to pull the data from Table 2 into Table 1 by
searching on UnitID. Is this possible? I am getting frustrated-please help.

Dale Fye said:
Based on your description, I think what you want is something like:

Select [A], , [C]
From yourTable
WHERE NOT ( = [C] AND [A] <> )

If any of these values can be NULL, then I'd recommend trying:

Select [A], , [C]
From yourTable
WHERE NOT (NZ() = NZ([C]) AND NZ([A]) <> NZ())

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



KLZA said:
Hi, I'm running a query that includes three fields, A,B, C. I need
all records from the A column, except when B and C are equal to each
other but not equal to A... Can anyone help? This seems fairly
simple but I'm out of answers...
Click to expand...
Click to expand...
 
Ms. S,

This should be possible, assuming that the [UnitID] field is unique in both
tables.

1. Step one - Either backup the database, or copy the two tables you are
working with, so that you can restore to the original if this does not work.
DON'T SKIP THIS STEP!

2. Next, you will need to create an update query. The SQL will look
something like:

UPDATE Table1
INNER JOIN Table2
ON Table1.UnitID = Table2.UnitID
SET Table1.[Field1] = Table2.[Field1],
Table1.[Field2] = Table2.[Field2]

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Ms. S said:
Dale, My name is Ms. S and I am looking for help with probably a similar
issue. I haven't used Access in a long time so please bare with me. I have
2 table with similar information. Table 1 has the most recent records
whereas Table 2 has the newly added columns. I have included the newly
columns in table 1 but now want to pull the data from Table 2 into Table 1 by
searching on UnitID. Is this possible? I am getting frustrated-please help.

Dale Fye said:
Based on your description, I think what you want is something like:

Select [A], , [C]
From yourTable
WHERE NOT ( = [C] AND [A] <> )

If any of these values can be NULL, then I'd recommend trying:

Select [A], , [C]
From yourTable
WHERE NOT (NZ() = NZ([C]) AND NZ([A]) <> NZ())

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



KLZA said:
Hi, I'm running a query that includes three fields, A,B, C. I need
all records from the A column, except when B and C are equal to each
other but not equal to A... Can anyone help? This seems fairly
simple but I'm out of answers...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top