query not acting right

G

Guest

I have the following code in my form:

DoCmd.RunSQL "SELECT tbl_masterpop_new.[Loan Acct #],
tbl_masterpop_new.[status], tbl_masterpop_new.[PopEnterDt] INTO tbl_history
FROM tbl_masterpop_new INNER JOIN tbl_masterpop ON tbl_masterpop_new.[Loan
Acct #] = tbl_masterpop.[Loan Acct #] WHERE tbl_masterpop.Status <>
tbl_masterpop_new.Status", -1


When I look at the results from the query, I notice that it is not including
all the records I want it to. For example, if the [loan acct #] is in both
tables, but the [status] for that particular [loan acct #] is different in
each table, I still want that record retrieved from both tables in the query
results simply because the [status] has changed.

How should I amend my query?

Thanks in advcance,
geebee
 
T

Tom Ellison

Dear GeeBee:

I recommend we keep this very simple and add complexity later. For that
reason, I'm going to drop the appending of rows to tbl_history for the
moment and just keep this a simple SELECT query. Also, let's get this
working as a query first and then create code for it. Let's try this
simplification.

SELECT tbl_masterpop_new.[Loan Acct #], tbl_masterpop.Status AS OStatus,
tbl_masterpop_new.[status], tbl_masterpop_new.[PopEnterDt]
FROM tbl_masterpop_new
INNER JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.Status <> tbl_masterpop_new.Status

I have added the "old" status so you can confirm that they are different.

Using this query, do you have some any "missing" rows where the status has
changed on a loan? Perhaps the JOIN is not working as you expected. Remove
the last line and run it again. Do all the loans JOIN as you would expect?
Or are the same ones still missing?

Tom Ellison
 
G

Guest

argh. still not working right. i even took out the where clause. and
changed the join properties. basically, it is not including distinct records
from both tables.


Tom Ellison said:
Dear GeeBee:

I recommend we keep this very simple and add complexity later. For that
reason, I'm going to drop the appending of rows to tbl_history for the
moment and just keep this a simple SELECT query. Also, let's get this
working as a query first and then create code for it. Let's try this
simplification.

SELECT tbl_masterpop_new.[Loan Acct #], tbl_masterpop.Status AS OStatus,
tbl_masterpop_new.[status], tbl_masterpop_new.[PopEnterDt]
FROM tbl_masterpop_new
INNER JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.Status <> tbl_masterpop_new.Status

I have added the "old" status so you can confirm that they are different.

Using this query, do you have some any "missing" rows where the status has
changed on a loan? Perhaps the JOIN is not working as you expected. Remove
the last line and run it again. Do all the loans JOIN as you would expect?
Or are the same ones still missing?

Tom Ellison


geebee said:
I have the following code in my form:

DoCmd.RunSQL "SELECT tbl_masterpop_new.[Loan Acct #],
tbl_masterpop_new.[status], tbl_masterpop_new.[PopEnterDt] INTO
tbl_history
FROM tbl_masterpop_new INNER JOIN tbl_masterpop ON
tbl_masterpop_new.[Loan
Acct #] = tbl_masterpop.[Loan Acct #] WHERE tbl_masterpop.Status <>
tbl_masterpop_new.Status", -1


When I look at the results from the query, I notice that it is not
including
all the records I want it to. For example, if the [loan acct #] is in
both
tables, but the [status] for that particular [loan acct #] is different in
each table, I still want that record retrieved from both tables in the
query
results simply because the [status] has changed.

How should I amend my query?

Thanks in advcance,
geebee
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear GeeBee:

You need to clue me in. After you removed the WHERE clause, it still
doesn't show all the "pairs" of records?

What kind of value is [Loan Acct #]? Are the values that do not JOIN
correctly truly identical? If one has, say, trailing spaces and the other
does not match those, they won't join.

Be sure to make the details of your test results clear. That's all I have
to go on!

Tom Ellison


geebee said:
argh. still not working right. i even took out the where clause. and
changed the join properties. basically, it is not including distinct
records
from both tables.


Tom Ellison said:
Dear GeeBee:

I recommend we keep this very simple and add complexity later. For that
reason, I'm going to drop the appending of rows to tbl_history for the
moment and just keep this a simple SELECT query. Also, let's get this
working as a query first and then create code for it. Let's try this
simplification.

SELECT tbl_masterpop_new.[Loan Acct #], tbl_masterpop.Status AS OStatus,
tbl_masterpop_new.[status], tbl_masterpop_new.[PopEnterDt]
FROM tbl_masterpop_new
INNER JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.Status <> tbl_masterpop_new.Status

I have added the "old" status so you can confirm that they are different.

Using this query, do you have some any "missing" rows where the status
has
changed on a loan? Perhaps the JOIN is not working as you expected.
Remove
the last line and run it again. Do all the loans JOIN as you would
expect?
Or are the same ones still missing?

Tom Ellison


geebee said:
I have the following code in my form:

DoCmd.RunSQL "SELECT tbl_masterpop_new.[Loan Acct #],
tbl_masterpop_new.[status], tbl_masterpop_new.[PopEnterDt] INTO
tbl_history
FROM tbl_masterpop_new INNER JOIN tbl_masterpop ON
tbl_masterpop_new.[Loan
Acct #] = tbl_masterpop.[Loan Acct #] WHERE tbl_masterpop.Status <>
tbl_masterpop_new.Status", -1


When I look at the results from the query, I notice that it is not
including
all the records I want it to. For example, if the [loan acct #] is in
both
tables, but the [status] for that particular [loan acct #] is different
in
each table, I still want that record retrieved from both tables in the
query
results simply because the [status] has changed.

How should I amend my query?

Thanks in advcance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

After I removed the WHERE clause, it still did not show the pairs of record.
just one rcord in one table. the values for the [loan acct #] are EXACTLY
the same in each table. this is sooo wierd. i even did a query to have it
return ALL the loan acct #, regardless of whether or not the [status}
matched. still no luck.


Tom Ellison said:
Dear GeeBee:

You need to clue me in. After you removed the WHERE clause, it still
doesn't show all the "pairs" of records?

What kind of value is [Loan Acct #]? Are the values that do not JOIN
correctly truly identical? If one has, say, trailing spaces and the other
does not match those, they won't join.

Be sure to make the details of your test results clear. That's all I have
to go on!

Tom Ellison


geebee said:
argh. still not working right. i even took out the where clause. and
changed the join properties. basically, it is not including distinct
records
from both tables.


Tom Ellison said:
Dear GeeBee:

I recommend we keep this very simple and add complexity later. For that
reason, I'm going to drop the appending of rows to tbl_history for the
moment and just keep this a simple SELECT query. Also, let's get this
working as a query first and then create code for it. Let's try this
simplification.

SELECT tbl_masterpop_new.[Loan Acct #], tbl_masterpop.Status AS OStatus,
tbl_masterpop_new.[status], tbl_masterpop_new.[PopEnterDt]
FROM tbl_masterpop_new
INNER JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.Status <> tbl_masterpop_new.Status

I have added the "old" status so you can confirm that they are different.

Using this query, do you have some any "missing" rows where the status
has
changed on a loan? Perhaps the JOIN is not working as you expected.
Remove
the last line and run it again. Do all the loans JOIN as you would
expect?
Or are the same ones still missing?

Tom Ellison


I have the following code in my form:

DoCmd.RunSQL "SELECT tbl_masterpop_new.[Loan Acct #],
tbl_masterpop_new.[status], tbl_masterpop_new.[PopEnterDt] INTO
tbl_history
FROM tbl_masterpop_new INNER JOIN tbl_masterpop ON
tbl_masterpop_new.[Loan
Acct #] = tbl_masterpop.[Loan Acct #] WHERE tbl_masterpop.Status <>
tbl_masterpop_new.Status", -1


When I look at the results from the query, I notice that it is not
including
all the records I want it to. For example, if the [loan acct #] is in
both
tables, but the [status] for that particular [loan acct #] is different
in
each table, I still want that record retrieved from both tables in the
query
results simply because the [status] has changed.

How should I amend my query?

Thanks in advcance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear GeeBee:

Could be corruption. Compact and repair, then try again. Now what?

Tom Ellison


geebee said:
After I removed the WHERE clause, it still did not show the pairs of
record.
just one rcord in one table. the values for the [loan acct #] are EXACTLY
the same in each table. this is sooo wierd. i even did a query to have
it
return ALL the loan acct #, regardless of whether or not the [status}
matched. still no luck.


Tom Ellison said:
Dear GeeBee:

You need to clue me in. After you removed the WHERE clause, it still
doesn't show all the "pairs" of records?

What kind of value is [Loan Acct #]? Are the values that do not JOIN
correctly truly identical? If one has, say, trailing spaces and the
other
does not match those, they won't join.

Be sure to make the details of your test results clear. That's all I
have
to go on!

Tom Ellison


geebee said:
argh. still not working right. i even took out the where clause. and
changed the join properties. basically, it is not including distinct
records
from both tables.


:

Dear GeeBee:

I recommend we keep this very simple and add complexity later. For
that
reason, I'm going to drop the appending of rows to tbl_history for the
moment and just keep this a simple SELECT query. Also, let's get this
working as a query first and then create code for it. Let's try this
simplification.

SELECT tbl_masterpop_new.[Loan Acct #], tbl_masterpop.Status AS
OStatus,
tbl_masterpop_new.[status], tbl_masterpop_new.[PopEnterDt]
FROM tbl_masterpop_new
INNER JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.Status <> tbl_masterpop_new.Status

I have added the "old" status so you can confirm that they are
different.

Using this query, do you have some any "missing" rows where the status
has
changed on a loan? Perhaps the JOIN is not working as you expected.
Remove
the last line and run it again. Do all the loans JOIN as you would
expect?
Or are the same ones still missing?

Tom Ellison


I have the following code in my form:

DoCmd.RunSQL "SELECT tbl_masterpop_new.[Loan Acct #],
tbl_masterpop_new.[status], tbl_masterpop_new.[PopEnterDt] INTO
tbl_history
FROM tbl_masterpop_new INNER JOIN tbl_masterpop ON
tbl_masterpop_new.[Loan
Acct #] = tbl_masterpop.[Loan Acct #] WHERE tbl_masterpop.Status <>
tbl_masterpop_new.Status", -1


When I look at the results from the query, I notice that it is not
including
all the records I want it to. For example, if the [loan acct #] is
in
both
tables, but the [status] for that particular [loan acct #] is
different
in
each table, I still want that record retrieved from both tables in
the
query
results simply because the [status] has changed.

How should I amend my query?

Thanks in advcance,
geebee
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DUPLICATE QUERY results 1
query differences 1
unwanted duplicate loan account numbers 1
duplicate records 3
make union query into make-table query 2
combine 2 access queries 3
query from form 8
query speed 5

Top