Referencing Column Number in Query

[usachrisk]

Hi -

I have a query this is using two tables. Each table has a field called "CostCenter" - so I have table1.costcenter and table2.costcenter. Table1.CostCenter has 3 columns in it. I'm trying to build a query that will display on those CostCenter fields that do not match. I have it now to a point where it will output the following:


AEmployee1 ACostCenterTable1 ACostCenterTable2
BEmployee1 BCostCenterTable1 BCostCenterTable2
CEmployee1 CCostCenterTable1 CCostCenterTable2

However in the criteria for Table1.CostCenter I've placed:

"Not [Table2.CostCenter]"

and that doesn't work (it displays all records) so I put the following in Table2.CostCenter:

"Not [Table1.CostCenter]"

... and it still shows all the records. Investigation showed that when it does the compare, it's actually comparing Column(0) to the CostCenter, and those will never match, so I'm trying to find out how to get it to reference Column(1) in the criteria.

I've tried [Table1.CostCenter].Column(1), [Table1.CostCenter.Column(1)], [[Table1.CostCenter].Column(1)], but I just can't seem to get the syntax.

Any suggestions?

 

[RV]

Can you be more specific by,for example, posting your SQL statement?


>nvestigation showed that when it does
the compare, it's actually comparing Column(0) to the CostCenter<

Which Column(0), are you referring to a ComboBox?

RV

 

[usachrisk]

Thanks for your reply. I think it'd be easiest if I put in eactly what I have (of relevence).

Tables: AccountNumbers, PagerList and tblDirectory.

AccountNumbers:
Has Costcenter, AccountNumber, SecondAccountNumber fields.

Pager List:
Has a list of pagers, phone numbers, employees, etc.
One of the fields is CostCenter.
CostCenter has a lookup for AccountNumber

tblDirectory
Has a list of names from our employee directory
Has a list of cost centers that is not tied to the first table.

What I'm trying to do is compare our directory cost center to the costcenter in PagerList.

I've created a new query called "qryMatchingCCs" that has the PagerList.EmployeeName, PagerList.PagerNumber, PagerList.Costcenter and tblDirectory.CostCenter.

In the tblDirectory.CostCenter criteria field, I've entered the following:

not [PagerList.CostCenter]

However it shows all four columns and many of the two cost center fields match (and obviously some don't) -- so I know it doesn't seem to be working. I've also tried the reverse and put:

not [tblDirectory.CostCenter]

in the PagerList criteria field and come up with exactly the same results.

 

[Pat Hartman]

You need to join the PagerList to tblDirectory on some unique value such as EmployeeID. Then in the criteria cell of the CostCenter column of PagerList put -

<> [tbldirectory].[CostCenter]

"NOT" is NOT the same as <> (not equal). NOT as you were using is was evaluating the field [PagerList.CostCenter] for true or false rather than comparing its value to that of tblDirectory.CostCenter.

 

[usachrisk]

Thanks Pat -- I have a relationship between the Employee Names (which match exactly) in the two tables I'm putting in the query.

 

[Jon K]

Pat's criteria should work.

Switch to your query's SQL View and copy and paste the SELECT statement here. Maybe we can spot something.


"Table1.CostCenter has 3 columns in it."
It's not usual for a field to have columns. Posting some contents of CostCenter in your tables can also help.

 

[usachrisk]

My SQL is as follows:

SELECT PagerList.Employee, PagerList.PrimaryPagerNumber, PagerList.CostCenter, tblDirectory.CostCenter
FROM PagerList INNER JOIN tblDirectory ON PagerList.Employee = tblDirectory.CustomerID
WHERE ((Not (PagerList.CostCenter)=[tblDirectory.CostCenter]));


The reason the one field has three columns is because, like I noted above, the field in the table has a lookup the PagerList.CostCenter field has a lookup value as follows:

SELECT [AccountNumbers].[CostCenter], [AccountNumbers].[Vendor2], [AccountNumbers].[Vendor2], [AccountNumbers].[Flag] FROM AccountNumbers;

Thanks.