Spurious Results in Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a linked table "Journal" with the following example data:

TransId Order Date Amount and etc.
12345 55512 8/4/04 136.15
12345 56241 8/4/04 256.14
12359 55512 8/9/04 223.98

I have a table "Journal1" with the following example data:

TransId Order DepNum
12345 55512 0
12345 56241 6
12359 55512 11

These tables are joined at both the TransId and Order fields.

When I query based on either of the joined fields, I get data such as the following:

TransId Order Date Amount DepNum
12345 55512 8/4/04 136.15 0
12345 55512 8/4/04 136.15 0

or
TransId Order Date Amount DepNum
12345 55512 8/4/04 136.15 0
12345 56241 8/4/04 136.15 0

or
TransId Order Date Amount DepNum
12345 55512 8/4/04 136.15 0
12359 55512 8/4/04 136.15 0

Any suggestions on how I can correct this?
 
Dear David:

Have you tried this:

SELECT J.TransId, J.Order, J.[Date], J.Amount, J1.DepNum
FROM Journal J
INNER JOIN Journal1 J1
ON J1.TransId = J.TransId AND J1.[Order] = J.[Order]
ORDER BY J.TransId, J.[Order]

It appears that you have a compound unique key (or Primary Key if you
wish) on both tables, being TransId and Order.

The results should then be:

12345 55512 8/4/04 136.15 0
12345 56241 8/9/04 223.98 11
12359 55512 8/9/04 223.98 6

But is that what you wanted?

By the way, since Date and Order are key words or funciton names, it
is a good thing avoid using them for column names. I'd recommend
something like DateOrdered and OrderNo.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi David,

PMFBI

I created your 2 tables in Access
with the example data you gave,
and using Tom's query, I got:

TransId [Order] [Date] Amount DepNum
12345 55512 8/4/2004 $136.15 0
12345 56241 8/4/2004 $256.14 6
12359 55512 8/9/2004 $223.98 11

To rule out something quirky with your Access
install, can you create a new db with the 2 tables
and data you provided and determine that Tom's
query works correctly?

Do you have all the latest SP's, Jet 8.0, latest MDAC?

Do you have AutoCorrect turned off?

Else...I would guess you...

1) have some corruption
- does it change if you compact and repair the
backend the linked tables are in?
- what happens if you import everything
into a new db?

2) have more than just Access involved
- are these linked FoxPro (or some other db) tables?
(if FoxPro, make sure "BackgroundFetch" is set to "NO")
- are the tables in a backend across a "dirty" network?

3) have different data than the example data
you have given us

For example, your "first case" might happen if you have
repeats in Journal1.

quick test:
SELECT TransID, [Order], DepNum
FROM Journal1
GROUP BY TransID, [Order], DepNum
HAVING Count(*)>1;

Of course this couldn't happen if you have set up
compound unique keys as Tom speculated.

Plus...I just don't see any reason for the other 2 cases
except for corruption/bad ODBC driver (or setting)/dirty network...
unless you have erroneous entries in both tables.

Apologies again for butting in.

Good luck,

Gary Walter

David said:
I tried your suggestion but got the exact same results...
Any other ideas?

David

Tom Ellison said:
Dear David:

Have you tried this:

SELECT J.TransId, J.Order, J.[Date], J.Amount, J1.DepNum
FROM Journal J
INNER JOIN Journal1 J1
ON J1.TransId = J.TransId AND J1.[Order] = J.[Order]
ORDER BY J.TransId, J.[Order]

It appears that you have a compound unique key (or Primary Key if you
wish) on both tables, being TransId and Order.

The results should then be:

12345 55512 8/4/04 136.15 0
12345 56241 8/9/04 223.98 11
12359 55512 8/9/04 223.98 6

But is that what you wanted?

By the way, since Date and Order are key words or funciton names, it
is a good thing avoid using them for column names. I'd recommend
something like DateOrdered and OrderNo.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
Click to expand...
 
Gary,
I think you have narrowed my search for an answer.
My Journal table is a Foxpro table that I must not edit or change in any
way. Journal1 is an Access table that I append to Journal to add the data I
need connnected to each record.

I am using Access2004, recently installed and all updates applied. However,
I haven't done any serious database work since QuatroPro 3.0... I'm a little
out of date...

I don't understand the problem with autocorrect or how to change it.
I can't find any reference to a 'backgroundfetch."

and yes, I have set up compound keys.
 
Hi David,

To turn off autocorrect, click on Tools/Options/General tab, then
uncheck the Name Auto-Correct boxes.

The "backgroundfetch" is a property of the FoxPro ODBC driver.

ACC97: Inconsistent Results Are Returned from a Query That Is Based on Linked Visual
FoxPro Tables
http://support.microsoft.com/default.aspx?scid=kb;en-us;252666&Product=mdac

*** quote ***
RESOLUTION
You can resolve this issue by either clicking to clear the Fetch data in background
check box or by selecting a unique record identifier when you link the ODBC table.

To clear the Fetch data in background check box, follow these steps.

NOTE: Because there are several versions of Microsoft Windows, the following steps
may be different on your computer. If they are, see your product documentation to
complete these steps.

Click Start, point to Settings, click Control Panel, and then double-click the ODBC
Data Sources (32bit) icon.
Select your DSN that uses the Microsoft Visual FoxPro driver, and then click
Configure.
In the ODBC Visual FoxPro Setup window, click Options.
Click to clear the Fetch data in background check box.
Click OK twice to close the ODBC Administrator.
Open your Access database.
Delete and re-create the links to your Visual FoxPro tables.
IMPORTANT: Do not use the Link Table Manager because the Link Table Manager does not
refresh the links to the tables.
*** unquote ***

David said:
Gary,
I think you have narrowed my search for an answer.
My Journal table is a Foxpro table that I must not edit or change in any
way. Journal1 is an Access table that I append to Journal to add the data I
need connnected to each record.

I am using Access2004, recently installed and all updates applied. However,
I haven't done any serious database work since QuatroPro 3.0... I'm a little
out of date...

I don't understand the problem with autocorrect or how to change it.
I can't find any reference to a 'backgroundfetch."

and yes, I have set up compound keys.



Gary Walter said:
Hi David,

PMFBI

I created your 2 tables in Access
with the example data you gave,
and using Tom's query, I got:

TransId [Order] [Date] Amount DepNum
12345 55512 8/4/2004 $136.15 0
12345 56241 8/4/2004 $256.14 6
12359 55512 8/9/2004 $223.98 11

To rule out something quirky with your Access
install, can you create a new db with the 2 tables
and data you provided and determine that Tom's
query works correctly?

Do you have all the latest SP's, Jet 8.0, latest MDAC?

Do you have AutoCorrect turned off?

Else...I would guess you...

1) have some corruption
- does it change if you compact and repair the
backend the linked tables are in?
- what happens if you import everything
into a new db?

2) have more than just Access involved
- are these linked FoxPro (or some other db) tables?
(if FoxPro, make sure "BackgroundFetch" is set to "NO")
- are the tables in a backend across a "dirty" network?

3) have different data than the example data
you have given us

For example, your "first case" might happen if you have
repeats in Journal1.

quick test:
SELECT TransID, [Order], DepNum
FROM Journal1
GROUP BY TransID, [Order], DepNum
HAVING Count(*)>1;

Of course this couldn't happen if you have set up
compound unique keys as Tom speculated.

Plus...I just don't see any reason for the other 2 cases
except for corruption/bad ODBC driver (or setting)/dirty network...
unless you have erroneous entries in both tables.

Apologies again for butting in.

Good luck,

Gary Walter
Click to expand...
Click to expand...
 
Gary,
Thanks for the help... as I was researching the ODBC driver, I realized I
had not linked my tables correctly. I redid the link and PRESTO! no spurious
data!

Now if I can just figure out how to speed up my queries... but that will be
another topic.

Thanks for your help!!!
David

Gary Walter said:
Hi David,

To turn off autocorrect, click on Tools/Options/General tab, then
uncheck the Name Auto-Correct boxes.

The "backgroundfetch" is a property of the FoxPro ODBC driver.

ACC97: Inconsistent Results Are Returned from a Query That Is Based on Linked Visual
FoxPro Tables
http://support.microsoft.com/default.aspx?scid=kb;en-us;252666&Product=mdac

*** quote ***
RESOLUTION
You can resolve this issue by either clicking to clear the Fetch data in background
check box or by selecting a unique record identifier when you link the ODBC table.

To clear the Fetch data in background check box, follow these steps.

NOTE: Because there are several versions of Microsoft Windows, the following steps
may be different on your computer. If they are, see your product documentation to
complete these steps.

Click Start, point to Settings, click Control Panel, and then double-click the ODBC
Data Sources (32bit) icon.
Select your DSN that uses the Microsoft Visual FoxPro driver, and then click
Configure.
In the ODBC Visual FoxPro Setup window, click Options.
Click to clear the Fetch data in background check box.
Click OK twice to close the ODBC Administrator.
Open your Access database.
Delete and re-create the links to your Visual FoxPro tables.
IMPORTANT: Do not use the Link Table Manager because the Link Table Manager does not
refresh the links to the tables.
*** unquote ***

David said:
Gary,
I think you have narrowed my search for an answer.
My Journal table is a Foxpro table that I must not edit or change in any
way. Journal1 is an Access table that I append to Journal to add the data I
need connnected to each record.

I am using Access2004, recently installed and all updates applied. However,
I haven't done any serious database work since QuatroPro 3.0... I'm a little
out of date...

I don't understand the problem with autocorrect or how to change it.
I can't find any reference to a 'backgroundfetch."

and yes, I have set up compound keys.



Gary Walter said:
Hi David,

PMFBI

I created your 2 tables in Access
with the example data you gave,
and using Tom's query, I got:

TransId [Order] [Date] Amount DepNum
12345 55512 8/4/2004 $136.15 0
12345 56241 8/4/2004 $256.14 6
12359 55512 8/9/2004 $223.98 11

To rule out something quirky with your Access
install, can you create a new db with the 2 tables
and data you provided and determine that Tom's
query works correctly?

Do you have all the latest SP's, Jet 8.0, latest MDAC?

Do you have AutoCorrect turned off?

Else...I would guess you...

1) have some corruption
- does it change if you compact and repair the
backend the linked tables are in?
- what happens if you import everything
into a new db?

2) have more than just Access involved
- are these linked FoxPro (or some other db) tables?
(if FoxPro, make sure "BackgroundFetch" is set to "NO")
- are the tables in a backend across a "dirty" network?

3) have different data than the example data
you have given us

For example, your "first case" might happen if you have
repeats in Journal1.

quick test:
SELECT TransID, [Order], DepNum
FROM Journal1
GROUP BY TransID, [Order], DepNum
HAVING Count(*)>1;

Of course this couldn't happen if you have set up
compound unique keys as Tom speculated.

Plus...I just don't see any reason for the other 2 cases
except for corruption/bad ODBC driver (or setting)/dirty network...
unless you have erroneous entries in both tables.

Apologies again for butting in.

Good luck,

Gary Walter
Click to expand...
Click to expand...
Click to expand...
 
Back
Top