Joins acting weird

[NOL]

I'm perplexed!!

My joins are acting really strange.
I have a linked table A .
Using a maketable query and using some criteria , i have created a table B from table A .
Both table A & B are exactly same in structure.

If I use A instead of B in my query I lose data and i just can't understand why ?

Please Help !!!!

Gina .

 

[Vassago]

Can you give us a sample of it? What you said is just not enough to determine why.

Thanks,

Vassago
"Those that make the best use of their time have none to spare" Thomas Fuller.

 

[sambo]

Are you sure you are getting the same structure from a make-table? I believe that primary keys lose their "primaryness" when copied into a make table.

Just a thought..

 

[NOL]

Vassago,
My tables are linked so i can't post a sample,sorry about that.
I'll try to explain.
I deleted all tables in my database and relinked them using the save password option.
A query I had created earlier now does not work.
I ran the same query from the backup and it works fine.
the only difference being when i relinked the tables i did not use any primary keys.

If I create a local copy of the ORACLE table in access and use that in my query it works.

Sambo ,
I have not defined any primary keys when I linked the table .
so in effect both tables do not have rimary keys.
Would a primary key affect a join ?

Thanks for replying..

 

[sambo]

How else do you Join a table if not by Primary/Foreign key relationships?

 

[NOL]

Well, i use the field which i know is unique in the table.
However in Access i have not explicitly defined it as a primary key.
I had assumed that would be required only during updation.

Why should a join fail if i don't define it that way ?
Maybe I've not understood the concept correctly.

 

[sambo]

You said..

Using a maketable query and using some criteria , i have created a table B from table A

This leads me to believe that B is a subset of A. That's all fine and dandy, but then you go on to say..

Both table A & B are exactly same in structure

So my question.. How can A be a subset of B, yet they both have the same structure?

Also, if you're joining tables w/o using the Primary/Foriegn Key (parent/child) relationships, then you aren't using Database Normalization to its potential.

I really am confused as to the scope of your question. What is it that you would like to accomplish with this new table (B)?

 

[NOL]

Sorry about that confusion.

When I say B is a subset of A , I have applied criteria to table A to get selected records.

The columns in both tables will remain the same, but the number of rows in B < number of rows in A

The reason for creating this table is that :
1. The criteria is required in all my queries.
2. If I apply criteria in a query which has outer joins, I've noticed that the outer join no longer functions as such and actually acts as an equi join.
similar to an oracle query where you have to use an inline view to apply criteria to queries where outer joins exist

My question here specifically is ,why data is lost if a linked table is used v/s a local MS access table in a query.
The data just doesn't show up in this query
even though if i query the table separately using a specific Id
it's all there .

Then again, this doesn't happen always, I've noticed it in two of my queries and was hoping sum1 who has encountered a similar problem could throw some light .

Thanks so much ..

 

[sambo]

Ahh..
Now it is beginning to make sense.
Unfortunately I can't think of what would cause this problem to occur.

Troubleshooting question..
Do you get two completely seperate recordsets from your query when you open it two different times (without changing anything)?
If so, than that is very wierd. If not, then what are you changing to get the different recordsets.