Cannot Do Query With Info From Two Tables

[chuck gregory]

Hello again~~

I'm using Microsoft Access 2000 (10.6771.6839) SP3 and can create separate queries with either of two tables which are linked by a Name field in relationships.

However, when I try make a query using fields from each of them, the View displays only column headings and not even blank fields. I've compacted and repaired the tables, or at least am getting a message that an object already exists. Is there something else I should check?

[Stewart Ross]

Hi Chuck. When only column headings are shown the query has not returned any common rows between the tables joined. This suggests a fault in the relationship between the two tables, and by fault I mean no more than that the relationship between the two is incorrectly set up, or is between fields of inappropriate type, or if set up correctly the values stored do not actually relate records from one table to the other.

You have not told us anything about the structure of the two tables, their field names and relationships other than there is an apparent join on a name field. Without clarification from you about the fields involved, posting also the SQL of the query that is returning no rows, and preferably some sample data from both tables it is difficult to form a view on what might be wrong.

As previous posts from the Forum Leader have said, you do need to give us sufficient data so we can assist you without seeking continual clarification of basic issues first.

-Stewart

[chuck gregory]

Thank you, Stewart. As I have said previously, I'm self-taught (even without the Dummies guide), so there's a lot of context I don't know about. For example, I have no idea what you mean by the "structure" of the tables.

Between the two of them, however, there are about 240-250 fields. Do you want all of them? Would a conflict in data types of two unlinked fields be a possible cause, for instance, if a date in "Letter" is a short date in one table, but is text in the other. "Name," the ones that are linked by that line between the two tables, is text in both, however. I have no idea how to send you the data in the fields or the fields themselves without typing it all out, and as I haven't seen other posts including information from their records, I feel uncomfortable doing that.

Sorry to be a caveman wandering in at a brie and Chablis party, but I'm grabbing any help I can get. It's the only way I learn. It's okay if you don't want to deal with it.

[Stewart Ross]

Hi Chuck. If there are 240-250 fields between your tables it is a certainty that they are not in normalised form - in other words that there is redundant information that needs to be broken down into other, simpler, tables. Most database tables are relatively narrow - from under ten to a few tens of fields at most.

Unrelated fields of different or even incorrect types (dates should not be stored as strings!) will not cause problems between related fields (although they are certain to cause problems in other ways if you try to update them using queries).

What you need to do is to look at the types and names of the fields you are joining and post those. You also need to post at least part of the query which is returning no rows - from the query editor select View SQL to see the underlying SQL which Access is generating.

Database design is where it all starts. without this step you will never be able to resolve the issues that arise from lack of structure in your data, and the fact that you acknowledge that the word structure in the context of a database is unknown to you is highly significant.

I would advise you to read all you can on database design. There is an article on database design and normalisation in the HowTo section, linked here, and an article on normalisation in Wikipedia linked here.

-Stewart

[missinglinq]

As Stewart has said, more than 20-25 fields in a form is usually a sign of a non-normaliezd database, and this issue needs to be addressed before you get very far into development. You say the two tables have "about" 240-250 fields, combined, which obviously means that you don't know the exact number. The maximum number of fields in a recordset for a query, form or report is
255, so you're either very close to the max, or may, in fact, already be over the top. This may or may not have a direct bearing on your problem. I'm not sure whether Access pops a warning if you exceed this number or not.

Linq ;0)>

[chuck gregory]

Thank you, gentlemen. I guess there are certain things that cna't be handled with a rosary. Guess I'll have to sell a kidney and get the Dummies manual, but I'll check the links first.

[limperger]

Hey Chuck!

Do not worry! I am a self-taught Access guy (using dummies manuals and stuff like that) and I can tell you that it was indeed some time before I got the idea behind joins. Just check what people here have commented: Same data in fields joined and same type of fields (although this later suggestion I'm not sure if it is really necessary (check what the experts here say ; )!! ). The easiest solution may be to assign each record in both tables referring to the same object, person, customer or whatever, an unique ID number.

Hope this helps!!

Best regards