A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Unmatched Query Mess



 
 
Thread Tools Display Modes
  #1  
Old October 27th, 2004, 01:07 PM
Natalia
external usenet poster
 
Posts: n/a
Default Unmatched Query Mess



I created a "Find Unmatched Query"
I am comparing a table to a query - Access is not finding
ANY unmatched records. I know that there are NO
duplicates in the table and there are NO
empty fields. What else should I look for? I can't
figure out why the query is not find these unmatched
records.

Below is the SQL view of the query.

SELECT [Training Grid].SOPNumber, [Training
Grid].TrainingType, [Training Grid].EmployeeName,
[Training Grid].DocumentationDate FROM [Training Grid]
LEFT JOIN [SOP Training Information] ON [Training
Grid].DocumentationDate=[SOP Training
Information].DocumentationDate WHERE ((([SOP Training
Information].DocumentationDate) Is Null));

I'm comparing a table and a query. They both have the
same fields - The table has 1788 records and the
query has 1731 records. If I'm comparing the same data,
why would I have more records in the table and less in the
query? Obviously the query is not recognizing some
records in the table. But how do I find out which records
if the "Unmatched Query" is not finding them? Sorry, I'm
confused.

Thank you for your time, Natalia

  #2  
Old October 27th, 2004, 03:07 PM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

I am confused by your concern regarding the query returning fewer records
than are in the table. You don't say which table has 1788 records, so I
cannot speak with specificity about the tables.

However, in general, your query has a WHERE clause in it:

WHERE ((([SOP Training Information].DocumentationDate) Is Null))

By definition, using a WHERE clause means that the query most likely will
return fewer records than are in the source table. You're excluding records
by using a WHERE clause.

So, can you provide more explanations about why your results puzzle you?
--

Ken Snell
MS ACCESS MVP



"Natalia" wrote in message
...


I created a "Find Unmatched Query"
I am comparing a table to a query - Access is not finding
ANY unmatched records. I know that there are NO
duplicates in the table and there are NO
empty fields. What else should I look for? I can't
figure out why the query is not find these unmatched
records.

Below is the SQL view of the query.

SELECT [Training Grid].SOPNumber, [Training
Grid].TrainingType, [Training Grid].EmployeeName,
[Training Grid].DocumentationDate FROM [Training Grid]
LEFT JOIN [SOP Training Information] ON [Training
Grid].DocumentationDate=[SOP Training
Information].DocumentationDate WHERE ((([SOP Training
Information].DocumentationDate) Is Null));

I'm comparing a table and a query. They both have the
same fields - The table has 1788 records and the
query has 1731 records. If I'm comparing the same data,
why would I have more records in the table and less in the
query? Obviously the query is not recognizing some
records in the table. But how do I find out which records
if the "Unmatched Query" is not finding them? Sorry, I'm
confused.

Thank you for your time, Natalia



  #3  
Old October 27th, 2004, 05:19 PM
Natalia
external usenet poster
 
Posts: n/a
Default

Thank you for your help - What I don't understand is that
the table has 4 fields - ("SOPNUmber" - "TrainingType" -
"Employee" and "DocumentationDate") and the query I'm
comparing the table to is based on the same 4 fields in
the table - This is something that was set up before I
starting working on it and my boss just wants (for audit
purposes) to compare the table in which we use for data
entry and then take the same table and all the same
fields and create a query. Then check the total number
of records in the table and in the query and they should
match (example: Table: 2000 records & query 2000 records)

My question is: If I'm creating a carbon copy (the query)
of the table, why do I have less records in the query?

Sorry about this mess.

-----Original Message-----

I am confused by your concern regarding the query
returning fewer records than are in the table. You don't
say which table has 1788 records, so I cannot speak with
specificity about the tables.

However, in general, your query has a WHERE clause in it:

WHERE ((([SOP Training Information].DocumentationDate) Is
Null))

By definition, using a WHERE clause means that the query
most likely will
return fewer records than are in the source table. You're
excluding records
by using a WHERE clause.

So, can you provide more explanations about why your
results puzzle you?

Ken Snell
MS ACCESS MVP



"Natalia" wrote in
message ...

I created a "Find Unmatched Query"
I am comparing a table to a query - Access is not finding
ANY unmatched records. I know that there are NO
duplicates in the table and there are NO
empty fields. What else should I look for? I can't
figure out why the query is not find these unmatched
records.

Below is the SQL view of the query.

SELECT [Training Grid].SOPNumber, [Training
Grid].TrainingType, [Training Grid].EmployeeName,
[Training Grid].DocumentationDate FROM [Training Grid]
LEFT JOIN [SOP Training Information] ON [Training
Grid].DocumentationDate=[SOP Training
Information].DocumentationDate WHERE ((([SOP Training
Information].DocumentationDate) Is Null));

I'm comparing a table and a query. They both have the
same fields - The table has 1788 records and the
query has 1731 records. If I'm comparing the same data,
why would I have more records in the table and less in the
query? Obviously the query is not recognizing some
records in the table. But how do I find out which records
if the "Unmatched Query" is not finding them? Sorry, I'm
confused.

Thank you for your time, Natalia


  #4  
Old October 27th, 2004, 05:51 PM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

You refer to "a table", but your query is using two tables as the source for
the data. So, I don't know "which" table you mean.

I think, after reading your reply, that you are using the wrong query for
what you want? It sounds as if you're comparing two tables and want to know
if they "match"?

(Note: one should not be using two separate tables to maintain the same set
of data -- it leads to all types of problems -- such as what you're now
seeing -- if they are not meticulously kept exactly the same.)

Let's back up and start from the beginning so that I can understand exactly
which tables you have, which data are in each table, what the fields are in
each table, and what you want your query to tell you. That should enable us
to point you to a solution.

--

Ken Snell
MS ACCESS MVP

"Natalia" wrote in message
...
Thank you for your help - What I don't understand is that
the table has 4 fields - ("SOPNUmber" - "TrainingType" -
"Employee" and "DocumentationDate") and the query I'm
comparing the table to is based on the same 4 fields in
the table - This is something that was set up before I
starting working on it and my boss just wants (for audit
purposes) to compare the table in which we use for data
entry and then take the same table and all the same
fields and create a query. Then check the total number
of records in the table and in the query and they should
match (example: Table: 2000 records & query 2000 records)

My question is: If I'm creating a carbon copy (the query)
of the table, why do I have less records in the query?

Sorry about this mess.

-----Original Message-----

I am confused by your concern regarding the query
returning fewer records than are in the table. You don't
say which table has 1788 records, so I cannot speak with
specificity about the tables.

However, in general, your query has a WHERE clause in it:

WHERE ((([SOP Training Information].DocumentationDate) Is
Null))

By definition, using a WHERE clause means that the query
most likely will
return fewer records than are in the source table. You're
excluding records
by using a WHERE clause.

So, can you provide more explanations about why your
results puzzle you?

Ken Snell
MS ACCESS MVP



"Natalia" wrote in
message ...

I created a "Find Unmatched Query"
I am comparing a table to a query - Access is not finding
ANY unmatched records. I know that there are NO
duplicates in the table and there are NO
empty fields. What else should I look for? I can't
figure out why the query is not find these unmatched
records.

Below is the SQL view of the query.

SELECT [Training Grid].SOPNumber, [Training
Grid].TrainingType, [Training Grid].EmployeeName,
[Training Grid].DocumentationDate FROM [Training Grid]
LEFT JOIN [SOP Training Information] ON [Training
Grid].DocumentationDate=[SOP Training
Information].DocumentationDate WHERE ((([SOP Training
Information].DocumentationDate) Is Null));

I'm comparing a table and a query. They both have the
same fields - The table has 1788 records and the
query has 1731 records. If I'm comparing the same data,
why would I have more records in the table and less in the
query? Obviously the query is not recognizing some
records in the table. But how do I find out which records
if the "Unmatched Query" is not finding them? Sorry, I'm
confused.

Thank you for your time, Natalia




  #5  
Old October 27th, 2004, 08:50 PM
Natalia
external usenet poster
 
Posts: n/a
Default

Thank you again for your help -
Just to let you know - this database is not set up
properly. The employee name field is not separated into 2
fields. The person who set it up, combined last & first
name in the same field (example: Smith, John) - When I
get the chance, I am going to separate the one field into
two. That's another problem.

Anyway, below are the tables:

tblTrainingGrid
SOPNumber
TrainingType
EmployeeName
DocumentationDate

tblSOP
SOPNumber
SOPName
SOPType

tblTrainingType
TrainingType

tblEmployee
EmployeeName
EmployeeNumber
DateOfHire
TerminationDate

This may be redundant, but this is how my boss wants to
validate the records in the table. Take the
tblTrainingGrid and create a query based on the that
table using all 4 fields. Compare total amount of
records in the table vs. the query. Then I recently
created an "Unmatched Query" comparing the table and the
query. If there were any unmatched records before, the
girl who created this DB used to print out the table and
the query data and manually check the records. I created
a report based on the unmatched query and the user would
be able to click a command button to see WHICH records
were unmatched.
I want to revamp this DB went I get the chance. In the
meantime, I have to work with this. Another problem with
this DB: Instead of creating one DB with all the
different departments who use this DB. She created 4
databases for each department. I will attempt to combine
this into one and create a department table without
(hopefully) jeopardizing the integrity of the data - Any
suggestions?

The reason I mention that is because I created the same
unmatched query for the other databases and every thing
worked out fine. This DB has 57 unmatched records and my
query is not finding them.

Thank you so much for your help.
-----Original Message-----

You refer to "a table", but your query is using two
tables as the source for
the data. So, I don't know "which" table you mean.

I think, after reading your reply, that you are using the
wrong query for what you want? It sounds as if you're
comparing two tables and want to know
if they "match"?

(Note: one should not be using two separate tables to
maintain the same set of data -- it leads to all types of
problems -- such as what you're now seeing -- if they are
not meticulously kept exactly the same.)

Let's back up and start from the beginning so that I can
understand exactly which tables you have, which data are
in each table, what the fields are in each table, and
what you want your query to tell you. That should enable
us to point you to a solution.


Ken Snell
MS ACCESS MVP

"Natalia" wrote in
message ...
Thank you for your help - What I don't understand is that
the table has 4 fields - ("SOPNUmber" - "TrainingType" -
"Employee" and "DocumentationDate") and the query I'm
comparing the table to is based on the same 4 fields in
the table - This is something that was set up before I
starting working on it and my boss just wants (for audit
purposes) to compare the table in which we use for data
entry and then take the same table and all the same
fields and create a query. Then check the total number
of records in the table and in the query and they should
match (example: Table: 2000 records & query 2000 records)

My question is: If I'm creating a carbon copy (the query)
of the table, why do I have less records in the query?

Sorry about this mess.
-----Original Message-----
I am confused by your concern regarding the query
returning fewer records than are in the table. You don't
say which table has 1788 records, so I cannot speak with
specificity about the tables.

However, in general, your query has a WHERE clause in it:

WHERE ((([SOP Training Information].DocumentationDate) Is
Null))

By definition, using a WHERE clause means that the query
most likely will return fewer records than are in the
source table. You're excluding records by using a WHERE
clause.

So, can you provide more explanations about why your
results puzzle you?

Ken Snell
MS ACCESS MVP



"Natalia" wrote in
message ...

I created a "Find Unmatched Query"
I am comparing a table to a query - Access is not finding
ANY unmatched records. I know that there are NO
duplicates in the table and there are NO
empty fields. What else should I look for? I can't
figure out why the query is not find these unmatched
records.

Below is the SQL view of the query.

SELECT [Training Grid].SOPNumber, [Training
Grid].TrainingType, [Training Grid].EmployeeName,
[Training Grid].DocumentationDate FROM [Training Grid]
LEFT JOIN [SOP Training Information] ON [Training
Grid].DocumentationDate=[SOP Training
Information].DocumentationDate WHERE ((([SOP Training
Information].DocumentationDate) Is Null));

I'm comparing a table and a query. They both have the
same fields - The table has 1788 records and the
query has 1731 records. If I'm comparing the same data,
why would I have more records in the table and less in the
query? Obviously the query is not recognizing some
records in the table. But how do I find out which records
if the "Unmatched Query" is not finding them? Sorry, I'm
confused.

Thank you for your time, Natalia

  #6  
Old October 27th, 2004, 10:25 PM
Natalia
external usenet poster
 
Posts: n/a
Default

I'm so sorry about all this mess - I found the problem.
The employee name field is not separated into 2
fields. The person who set it up, combined last & first
name in the same field (example: Smith, John) - In the
employee table, one employee didn't have a middle
intial. In the Training Grid table the employee had a
middle intial. Any suggestions on how to separate the
employee name field into 2 fields?

Sorry for the mess.
-----Original Message-----
You refer to "a table", but your query is using two

tables as the source for
the data. So, I don't know "which" table you mean.

I think, after reading your reply, that you are using

the wrong query for
what you want? It sounds as if you're comparing two

tables and want to know
if they "match"?

(Note: one should not be using two separate tables to

maintain the same set
of data -- it leads to all types of problems -- such as

what you're now
seeing -- if they are not meticulously kept exactly the

same.)

Let's back up and start from the beginning so that I can

understand exactly
which tables you have, which data are in each table,

what the fields are in
each table, and what you want your query to tell you.

That should enable us
to point you to a solution.

--

Ken Snell
MS ACCESS MVP

"Natalia" wrote in

message
...
Thank you for your help - What I don't understand is

that
the table has 4 fields - ("SOPNUmber" -

"TrainingType" -
"Employee" and "DocumentationDate") and the query I'm
comparing the table to is based on the same 4 fields in
the table - This is something that was set up before I
starting working on it and my boss just wants (for

audit
purposes) to compare the table in which we use for data
entry and then take the same table and all the same
fields and create a query. Then check the total number
of records in the table and in the query and they

should
match (example: Table: 2000 records & query 2000

records)

My question is: If I'm creating a carbon copy (the

query)
of the table, why do I have less records in the query?

Sorry about this mess.

-----Original Message-----

I am confused by your concern regarding the query
returning fewer records than are in the table. You

don't
say which table has 1788 records, so I cannot speak

with
specificity about the tables.

However, in general, your query has a WHERE clause in

it:

WHERE ((([SOP Training Information].DocumentationDate)

Is
Null))

By definition, using a WHERE clause means that the

query
most likely will
return fewer records than are in the source table.

You're
excluding records
by using a WHERE clause.

So, can you provide more explanations about why your
results puzzle you?

Ken Snell
MS ACCESS MVP



"Natalia" wrote

in
message ...

I created a "Find Unmatched Query"
I am comparing a table to a query - Access is not

finding
ANY unmatched records. I know that there are NO
duplicates in the table and there are NO
empty fields. What else should I look for? I can't
figure out why the query is not find these unmatched
records.

Below is the SQL view of the query.

SELECT [Training Grid].SOPNumber, [Training
Grid].TrainingType, [Training Grid].EmployeeName,
[Training Grid].DocumentationDate FROM [Training Grid]
LEFT JOIN [SOP Training Information] ON [Training
Grid].DocumentationDate=[SOP Training
Information].DocumentationDate WHERE ((([SOP Training
Information].DocumentationDate) Is Null));

I'm comparing a table and a query. They both have the
same fields - The table has 1788 records and the
query has 1731 records. If I'm comparing the same

data,
why would I have more records in the table and less in

the
query? Obviously the query is not recognizing some
records in the table. But how do I find out which

records
if the "Unmatched Query" is not finding them? Sorry,

I'm
confused.

Thank you for your time, Natalia




.

  #7  
Old October 27th, 2004, 11:16 PM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

How and whether you can easily separate the first and last names into
separate fields depends upon the consistency of the data that currently are
in the single field. As you have noted, the current data don't have just a
first and last name, but some also have a middle initial or name as well.

Assuming that the data are in the format of LastName, FirstName, you can use
the InStr, Left, and Mid functions to parse the text into two separate
strings:

FirstName: Mid([FullNameField], InStr([FullNameField], ",") + 1)
LastName: Left([FullNameField], InStr([FullNameField], ",") - 1)

--

Ken Snell
MS ACCESS MVP

"Natalia" wrote in message
...
I'm so sorry about all this mess - I found the problem.
The employee name field is not separated into 2
fields. The person who set it up, combined last & first
name in the same field (example: Smith, John) - In the
employee table, one employee didn't have a middle
intial. In the Training Grid table the employee had a
middle intial. Any suggestions on how to separate the
employee name field into 2 fields?

Sorry for the mess.
-----Original Message-----
You refer to "a table", but your query is using two

tables as the source for
the data. So, I don't know "which" table you mean.

I think, after reading your reply, that you are using

the wrong query for
what you want? It sounds as if you're comparing two

tables and want to know
if they "match"?

(Note: one should not be using two separate tables to

maintain the same set
of data -- it leads to all types of problems -- such as

what you're now
seeing -- if they are not meticulously kept exactly the

same.)

Let's back up and start from the beginning so that I can

understand exactly
which tables you have, which data are in each table,

what the fields are in
each table, and what you want your query to tell you.

That should enable us
to point you to a solution.

--

Ken Snell
MS ACCESS MVP

"Natalia" wrote in

message
...
Thank you for your help - What I don't understand is

that
the table has 4 fields - ("SOPNUmber" -

"TrainingType" -
"Employee" and "DocumentationDate") and the query I'm
comparing the table to is based on the same 4 fields in
the table - This is something that was set up before I
starting working on it and my boss just wants (for

audit
purposes) to compare the table in which we use for data
entry and then take the same table and all the same
fields and create a query. Then check the total number
of records in the table and in the query and they

should
match (example: Table: 2000 records & query 2000

records)

My question is: If I'm creating a carbon copy (the

query)
of the table, why do I have less records in the query?

Sorry about this mess.

-----Original Message-----
I am confused by your concern regarding the query
returning fewer records than are in the table. You

don't
say which table has 1788 records, so I cannot speak

with
specificity about the tables.

However, in general, your query has a WHERE clause in

it:

WHERE ((([SOP Training Information].DocumentationDate)

Is
Null))

By definition, using a WHERE clause means that the

query
most likely will
return fewer records than are in the source table.

You're
excluding records
by using a WHERE clause.

So, can you provide more explanations about why your
results puzzle you?

Ken Snell
MS ACCESS MVP



"Natalia" wrote

in
message ...

I created a "Find Unmatched Query"
I am comparing a table to a query - Access is not

finding
ANY unmatched records. I know that there are NO
duplicates in the table and there are NO
empty fields. What else should I look for? I can't
figure out why the query is not find these unmatched
records.

Below is the SQL view of the query.

SELECT [Training Grid].SOPNumber, [Training
Grid].TrainingType, [Training Grid].EmployeeName,
[Training Grid].DocumentationDate FROM [Training Grid]
LEFT JOIN [SOP Training Information] ON [Training
Grid].DocumentationDate=[SOP Training
Information].DocumentationDate WHERE ((([SOP Training
Information].DocumentationDate) Is Null));

I'm comparing a table and a query. They both have the
same fields - The table has 1788 records and the
query has 1731 records. If I'm comparing the same

data,
why would I have more records in the table and less in

the
query? Obviously the query is not recognizing some
records in the table. But how do I find out which

records
if the "Unmatched Query" is not finding them? Sorry,

I'm
confused.

Thank you for your time, Natalia




.



  #8  
Old October 27th, 2004, 11:25 PM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

I neglected to add how to use these expressions that I posted.

You can add two new fields to your table. Name them as I note (FirstName and
LastName).

Then you can run an update query to populate these two new fields:

UPDATE TableName
SET FirstName = Mid([FullNameField], InStr([FullNameField], ",") + 1),
LastName = Left([FullNameField], InStr([FullNameField], ",") - 1);

--

Ken Snell
MS ACCESS MVP


"Ken Snell [MVP]" wrote in message
...
How and whether you can easily separate the first and last names into
separate fields depends upon the consistency of the data that currently

are
in the single field. As you have noted, the current data don't have just a
first and last name, but some also have a middle initial or name as well.

Assuming that the data are in the format of LastName, FirstName, you can

use
the InStr, Left, and Mid functions to parse the text into two separate
strings:

FirstName: Mid([FullNameField], InStr([FullNameField], ",") + 1)
LastName: Left([FullNameField], InStr([FullNameField], ",") - 1)

--

Ken Snell
MS ACCESS MVP

"Natalia" wrote in message
...
I'm so sorry about all this mess - I found the problem.
The employee name field is not separated into 2
fields. The person who set it up, combined last & first
name in the same field (example: Smith, John) - In the
employee table, one employee didn't have a middle
intial. In the Training Grid table the employee had a
middle intial. Any suggestions on how to separate the
employee name field into 2 fields?

Sorry for the mess.
-----Original Message-----
You refer to "a table", but your query is using two

tables as the source for
the data. So, I don't know "which" table you mean.

I think, after reading your reply, that you are using

the wrong query for
what you want? It sounds as if you're comparing two

tables and want to know
if they "match"?

(Note: one should not be using two separate tables to

maintain the same set
of data -- it leads to all types of problems -- such as

what you're now
seeing -- if they are not meticulously kept exactly the

same.)

Let's back up and start from the beginning so that I can

understand exactly
which tables you have, which data are in each table,

what the fields are in
each table, and what you want your query to tell you.

That should enable us
to point you to a solution.

--

Ken Snell
MS ACCESS MVP

"Natalia" wrote in

message
...
Thank you for your help - What I don't understand is

that
the table has 4 fields - ("SOPNUmber" -

"TrainingType" -
"Employee" and "DocumentationDate") and the query I'm
comparing the table to is based on the same 4 fields in
the table - This is something that was set up before I
starting working on it and my boss just wants (for

audit
purposes) to compare the table in which we use for data
entry and then take the same table and all the same
fields and create a query. Then check the total number
of records in the table and in the query and they

should
match (example: Table: 2000 records & query 2000

records)

My question is: If I'm creating a carbon copy (the

query)
of the table, why do I have less records in the query?

Sorry about this mess.

-----Original Message-----
I am confused by your concern regarding the query
returning fewer records than are in the table. You

don't
say which table has 1788 records, so I cannot speak

with
specificity about the tables.

However, in general, your query has a WHERE clause in

it:

WHERE ((([SOP Training Information].DocumentationDate)

Is
Null))

By definition, using a WHERE clause means that the

query
most likely will
return fewer records than are in the source table.

You're
excluding records
by using a WHERE clause.

So, can you provide more explanations about why your
results puzzle you?

Ken Snell
MS ACCESS MVP



"Natalia" wrote

in
message ...

I created a "Find Unmatched Query"
I am comparing a table to a query - Access is not

finding
ANY unmatched records. I know that there are NO
duplicates in the table and there are NO
empty fields. What else should I look for? I can't
figure out why the query is not find these unmatched
records.

Below is the SQL view of the query.

SELECT [Training Grid].SOPNumber, [Training
Grid].TrainingType, [Training Grid].EmployeeName,
[Training Grid].DocumentationDate FROM [Training Grid]
LEFT JOIN [SOP Training Information] ON [Training
Grid].DocumentationDate=[SOP Training
Information].DocumentationDate WHERE ((([SOP Training
Information].DocumentationDate) Is Null));

I'm comparing a table and a query. They both have the
same fields - The table has 1788 records and the
query has 1731 records. If I'm comparing the same

data,
why would I have more records in the table and less in

the
query? Obviously the query is not recognizing some
records in the table. But how do I find out which

records
if the "Unmatched Query" is not finding them? Sorry,

I'm
confused.

Thank you for your time, Natalia




.





  #9  
Old October 28th, 2004, 02:36 PM
Natalia
external usenet poster
 
Posts: n/a
Default

Thank you again - I deleted all the middle intials. All
the names are stored: Smith, John

-----Original Message-----
How and whether you can easily separate the first and

last names into
separate fields depends upon the consistency of the data

that currently are
in the single field. As you have noted, the current data

don't have just a
first and last name, but some also have a middle initial

or name as well.

Assuming that the data are in the format of LastName,

FirstName, you can use
the InStr, Left, and Mid functions to parse the text

into two separate
strings:

FirstName: Mid([FullNameField], InStr

([FullNameField], ",") + 1)
LastName: Left([FullNameField], InStr

([FullNameField], ",") - 1)

--

Ken Snell
MS ACCESS MVP

"Natalia" wrote in

message
...
I'm so sorry about all this mess - I found the problem.
The employee name field is not separated into 2
fields. The person who set it up, combined last &

first
name in the same field (example: Smith, John) - In the
employee table, one employee didn't have a middle
intial. In the Training Grid table the employee had a
middle intial. Any suggestions on how to separate the
employee name field into 2 fields?

Sorry for the mess.
-----Original Message-----
You refer to "a table", but your query is using two

tables as the source for
the data. So, I don't know "which" table you mean.

I think, after reading your reply, that you are using

the wrong query for
what you want? It sounds as if you're comparing two

tables and want to know
if they "match"?

(Note: one should not be using two separate tables to

maintain the same set
of data -- it leads to all types of problems -- such

as
what you're now
seeing -- if they are not meticulously kept exactly

the
same.)

Let's back up and start from the beginning so that I

can
understand exactly
which tables you have, which data are in each table,

what the fields are in
each table, and what you want your query to tell you.

That should enable us
to point you to a solution.

--

Ken Snell
MS ACCESS MVP

"Natalia" wrote

in
message
...
Thank you for your help - What I don't understand is

that
the table has 4 fields - ("SOPNUmber" -

"TrainingType" -
"Employee" and "DocumentationDate") and the query

I'm
comparing the table to is based on the same 4

fields in
the table - This is something that was set up

before I
starting working on it and my boss just wants (for

audit
purposes) to compare the table in which we use for

data
entry and then take the same table and all the same
fields and create a query. Then check the total

number
of records in the table and in the query and they

should
match (example: Table: 2000 records & query 2000

records)

My question is: If I'm creating a carbon copy (the

query)
of the table, why do I have less records in the

query?

Sorry about this mess.

-----Original Message-----
I am confused by your concern regarding the query
returning fewer records than are in the table. You

don't
say which table has 1788 records, so I cannot speak

with
specificity about the tables.

However, in general, your query has a WHERE clause

in
it:

WHERE ((([SOP Training

Information].DocumentationDate)
Is
Null))

By definition, using a WHERE clause means that the

query
most likely will
return fewer records than are in the source table.

You're
excluding records
by using a WHERE clause.

So, can you provide more explanations about why your
results puzzle you?

Ken Snell
MS ACCESS MVP



"Natalia"

wrote
in
message news:15a201c4bc1d$7c422680

...

I created a "Find Unmatched Query"
I am comparing a table to a query - Access is not

finding
ANY unmatched records. I know that there are NO
duplicates in the table and there are NO
empty fields. What else should I look for? I can't
figure out why the query is not find these unmatched
records.

Below is the SQL view of the query.

SELECT [Training Grid].SOPNumber, [Training
Grid].TrainingType, [Training Grid].EmployeeName,
[Training Grid].DocumentationDate FROM [Training

Grid]
LEFT JOIN [SOP Training Information] ON [Training
Grid].DocumentationDate=[SOP Training
Information].DocumentationDate WHERE ((([SOP

Training
Information].DocumentationDate) Is Null));

I'm comparing a table and a query. They both have

the
same fields - The table has 1788 records and the
query has 1731 records. If I'm comparing the same

data,
why would I have more records in the table and less

in
the
query? Obviously the query is not recognizing some
records in the table. But how do I find out which

records
if the "Unmatched Query" is not finding them?

Sorry,
I'm
confused.

Thank you for your time, Natalia




.



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Unmatched Query not working right? Matt Running & Setting Up Queries 3 September 15th, 2004 12:30 AM
Too Few Parameters error Mail Merge Access Parameter Query Tony_VBACoder Mailmerge 3 September 14th, 2004 12:15 PM
Unmatched Query: Memo Fields Bernie Running & Setting Up Queries 1 August 22nd, 2004 03:19 PM
Unmatched Query Bob Mullen Running & Setting Up Queries 1 July 24th, 2004 02:34 PM
Struggling with MS Query... Alex General Discussion 5 July 6th, 2004 11:46 AM


All times are GMT +1. The time now is 07:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.