HEEEELP PLEEEEASE (Sorry for Shouting)

[Guest]

I have a problem that is driving me Nuts. I have a "Stock" Table and (because
my Stock Items are serialised) I tore each item as an individual Record in
the Table. In order not to repeat the same Make and Model information in each
record, I store that information in seperate "Make" and "Model" Tables, only
storing the "MakeID" and "ModelID" in each Stock record so a record in my
"Stock" Table would look something like :- Make=1, Model= 4, SerialNo=1234
etc. In order to retreive the infoemation from the Table, I use a Query that
is linked to all the other Tables to resolve the Numbers Back into a
recognisable name.

The problem is that the query only seems to want to show its own selected
records, even when there is no criteria set in the Query (I would expect that
this situation should show all the records in the table). My use of "Find
First" will not find the Fields that the Query says are not in the Table
(even though they quite definately are)

The problem is very dificult to explain and I have put together a small
database (only 800k) that might help if any one thinks they might have a
solution to the problem

Thanks in advance Ray C

 

[Guest]

I'm betting that you have an INNER JOIN between the tables. If one side does
not have a matching record with the other, data from neither side will show.

You need to do a Left or Right Join. In the Query Design View double click
on the black line(s) between the tables/queries and select one of the other
options in the dialog box.

You may run into a couple of problems.

(1) If you have multiple joins betweent the two record sets, you might have
to play with the combinations of joins to get the desired results.

(2) It's possible that you might need both Right and Left joins to get
records from both sides in one query. Access does not support this type of
Outer join. If you run into this problem, it's most likely that your database
is not properly normalized to support your needs.

 

[Guest]

Reading the OP's post, it could also be that the table field is not
specificed in the query. If the field is not returned by the query, then
referencing will cause the problem discribed. BTW, you mean FindFirst, right?

Re: My use of "Find First" will not find the Fields that the Query says are
not in the Table (even though they quite definately are)

 

[Guest]

My, you guys are Grrrreat. I have been tearing my hair out for the last month
or so trying to work out why I seemed to be loosing information when I knew
it was there all the time. I would never have found that out and can not
thank you both enough (I hope that this reply will reach Klatu who also
responded).
Please, Please keep up the good work.

Many thanks RayC

 

[John W. Vinson]

The problem is that the query only seems to want to show its own selected
records, even when there is no criteria set in the Query (I would expect that
this situation should show all the records in the table). My use of "Find
First" will not find the Fields that the Query says are not in the Table
(even though they quite definately are)

Please open the query in SQL view and post the SQL text here. The database
probably isn't necessary. Also indicate how you're doing the FindFirst (e.g.
post the code and the results you expect vs. what you get).

John W. Vinson [MVP]

 

[Guest]

Responses such as yours makes it all worthwhile.

I challenge you to some day help another with their problem. That would be
great!

 

[Guest]

Hi John

Jerry Whittle and Klatuu hit the nail on the head, I had the wrong type of
Joins between the different tables in the query. I thank you for your kind
response but that particulat issue is now resolved (I would never have found
it without the help of you MVC's) and I am on to my next hurdle.

Many Thanks Ray C