Really need help with this criteria please

[mrz via AccessMonster.com]

Hey all!

I have two tables that I need to compare. The data is coming from two diff.
systems but the data has the same meaning.

Table 1: with the following fields:

ID#, Gross Amount, Dept#, Obj Code, Pay Date, Source

Table 2: with the following fields:

ID#, Account#, Dept#, Source

I need to surface the records in both table in which the ID#s are the same
but the Dept number is different.

Kindly post your suggestions.

Greatly appreciated very much

 

[John Vinson]

Hey all!

I have two tables that I need to compare. The data is coming from two diff.
systems but the data has the same meaning.

Table 1: with the following fields:

ID#, Gross Amount, Dept#, Obj Code, Pay Date, Source

Table 2: with the following fields:

ID#, Account#, Dept#, Source

I need to surface the records in both table in which the ID#s are the same
but the Dept number is different.

Create a Query by adding both tables to the query grid; join the two
instances on ID#.

As a criterion on [Table 2].[Dept#] put

<> [Table 1].[Dept#]

The brackets are required, both because you're (unwisely IMHO) using
blanks and special characters in fieldnames, and to tell Access you
are referring to a fieldname rather than a text string.

John W. Vinson[MVP]

 

[mrz via AccessMonster.com]

Dear Mr. Vinson,

I apoligize in advance for my basic questions. I do not know exactly put the
criteria that you are suggesting.
I create the query, joined them by the ID#. I broght down the ID# from Table
one as well as the Dept#...further more I brought down the ID# from table two
plus its Dept#.

My question is where do I put the criteria you are telling me.
Below the dept# in table one or below the dept# in table two?

kindly guide me ... I thank you very much for your patience

Hey all!

[quoted text clipped - 11 lines]

I need to surface the records in both table in which the ID#s are the same
but the Dept number is different.

Create a Query by adding both tables to the query grid; join the two
instances on ID#.

As a criterion on [Table 2].[Dept#] put

<> [Table 1].[Dept#]

The brackets are required, both because you're (unwisely IMHO) using
blanks and special characters in fieldnames, and to tell Access you
are referring to a fieldname rather than a text string.

John W. Vinson[MVP]

 

[John Vinson]

Dear Mr. Vinson,

I apoligize in advance for my basic questions. I do not know exactly put the
criteria that you are suggesting.
I create the query, joined them by the ID#. I broght down the ID# from Table
one as well as the Dept#...further more I brought down the ID# from table two
plus its Dept#.

it is not necessary to display either ID#; if you display one, the
other will by definition be equal to it so there's certainly no point
to displaying both!) but it won't affect the query if you do or if you
don't.

My question is where do I put the criteria you are telling me.
Below the dept# in table one or below the dept# in table two?

What I said was:

As a criterion on [Table 2].[Dept#] put

<> [Table 1].[Dept#]


That is, select the Dept# field from Table 2 into the grid (as you
have done); in the Criteria row underneath this field type the exact
characters

<> [Table 1].[Dept #]

assuming that you've got a table named "Table 1" with a field "Dept#".


John W. Vinson[MVP]

 

[mrz via AccessMonster.com]

Dear Mr. Vinson,

I think I followed exactly your instructions.

Observation:
Now when I run the query I get window that reads Enter Parameter Value.
It seems that it is asking me for an especific dept #.

The only thing that I need is to get a list of all the records in both tables
where the ID#s are the same but the department # are different.

Please tell me what to do next.
Would it help to take a screenshot of the query design view and email to you?


Thanks again!

Dear Mr. Vinson,

[quoted text clipped - 3 lines]

one as well as the Dept#...further more I brought down the ID# from table two
plus its Dept#.

it is not necessary to display either ID#; if you display one, the
other will by definition be equal to it so there's certainly no point
to displaying both!) but it won't affect the query if you do or if you
don't.

My question is where do I put the criteria you are telling me.
Below the dept# in table one or below the dept# in table two?

What I said was:

As a criterion on [Table 2].[Dept#] put

<> [Table 1].[Dept#]

That is, select the Dept# field from Table 2 into the grid (as you
have done); in the Criteria row underneath this field type the exact
characters

<> [Table 1].[Dept #]

assuming that you've got a table named "Table 1" with a field "Dept#".

John W. Vinson[MVP]

 

[John Vinson]

Please tell me what to do next.
Would it help to take a screenshot of the query design view and email to you?

No; what would help is to use the View menu option, select SQL, and
view the actual query SQL text. Copy and paste that to a message here.

Private EMail support is available to paying customers, but as a
self-employed freelancer volunteering my time on the newsgroups, I
must request that free support stay here.

John W. Vinson[MVP]

 

[mrz via AccessMonster.com]

Thanks for that clarification.
I am posting below the SQL text that you instructed me.

Kindly note that for simplicity of the questions I was refering to the tables
as table 1 and table 2
Howerver, the SQL text will show the actual names and fields.

SELECT [LI Trans College Assistants].[SS#], [LI Trans College Assistants].
[Dept#], [FE College Assistants].[FAS Dept #]
FROM [FE College Assistants] INNER JOIN [LI Trans College Assistants] ON [FE
College Assistants].[SS#] = [LI Trans College Assistants].[SS#]
WHERE ((([FE College Assistants].[FAS Dept #])<>[LI Trans College Assistants].
[FAS Dept #]));

Thank you Mr. Vinson.

 

[John Vinson]

Thanks for that clarification.
I am posting below the SQL text that you instructed me.

Kindly note that for simplicity of the questions I was refering to the tables
as table 1 and table 2
Howerver, the SQL text will show the actual names and fields.

SELECT [LI Trans College Assistants].[SS#], [LI Trans College Assistants].
[Dept#], [FE College Assistants].[FAS Dept #]
FROM [FE College Assistants] INNER JOIN [LI Trans College Assistants] ON [FE
College Assistants].[SS#] = [LI Trans College Assistants].[SS#]
WHERE ((([FE College Assistants].[FAS Dept #])<>[LI Trans College Assistants].
[FAS Dept #]));

If you're getting prompted for a parameter, it would seem that you
have misspelled one of the department name fields. Is the name of the
field in fact [FAS Dept #] - with exactly the same spacing and special
characters - in both tables? If not, correct the fieldname in the
criteria.

Also, try removing the criteria altogether. Do you see records with
the same (or different) departments?

John W. Vinson[MVP]

 

[mrz via AccessMonster.com]

Mr. Vinson:

You were indeed correct it worked. Thank you so much. I was double checking
the data the results that surfaced are the ones I was expecting.

I will ask one thing if you do not mind. Can you kindly explain the criteria
that I just inputed.
In other words. I would like to understand what exactly the ( <> ) does in
the following sintax.

<>[LI Trans College Assistants].[Dept]

Also did it matter if I could have type the name of the other table in the
above sintax?
In other words, If i would have input the following criteria:

<>[FE College Assistants].[Dept]

would I have gotten the same results? I will try it but I wanted to get your
input.

Also what does the ( . ) between the [ ] means?

Again thank you for donate your skills to people like me that needs some
guidance.

Thanks for that clarification.
I am posting below the SQL text that you instructed me.

[quoted text clipped - 9 lines]

WHERE ((([FE College Assistants].[FAS Dept #])<>[LI Trans College Assistants].
[FAS Dept #]));

If you're getting prompted for a parameter, it would seem that you
have misspelled one of the department name fields. Is the name of the
field in fact [FAS Dept #] - with exactly the same spacing and special
characters - in both tables? If not, correct the fieldname in the
criteria.

Also, try removing the criteria altogether. Do you see records with
the same (or different) departments?

John W. Vinson[MVP]

 

[John Vinson]

Mr. Vinson:

You were indeed correct it worked. Thank you so much. I was double checking
the data the results that surfaced are the ones I was expecting.

I will ask one thing if you do not mind. Can you kindly explain the criteria
that I just inputed.
In other words. I would like to understand what exactly the ( <> ) does in
the following sintax.

<>[LI Trans College Assistants].[Dept]

It's the "NOT EQUALS" operator. You wanted to find those records for
whicht the DEPT field in [FE College Assistants] was *not equal* to
the DEPT field in [LI Trans College Assistants]. That's precisely what
this operator does - just as the "=" operator finds cases where two
values are equal, and the "<" operator finds cases where the first

Also did it matter if I could have type the name of the other table in the
above sintax?
In other words, If i would have input the following criteria:

<>[FE College Assistants].[Dept]

would I have gotten the same results? I will try it but I wanted to get your
input.

Ummm... No.

That would return all records where the value of the field [Dept] in
the table [FE College Assistants] is *not equal* to itself. Of course
any value is always going to be equal to itself (well, actually not:
if it's NULL then ANY comparison will return NULL, and will fail)

Also what does the ( . ) between the [ ] means?

It simply delimits the name of a table from the name of a field within
that table.

Again thank you for donate your skills to people like me that needs some
guidance.

You're most welcome!

John W. Vinson[MVP]