473,327 Members | 1,919 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Cannot Do Query With Info From Two Tables

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?
Apr 9 '08 #1
6 1429
Stewart Ross
2,545 Expert Mod 2GB
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
Apr 9 '08 #2
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.
Apr 10 '08 #3
Stewart Ross
2,545 Expert Mod 2GB
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
Apr 10 '08 #4
missinglinq
3,532 Expert 2GB
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)>
Apr 10 '08 #5
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.
Apr 14 '08 #6
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
Apr 14 '08 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: jh8735 | last post by:
I use Access to run reports from various pieces of financial data. The one query that I have been unable to produce, but need is the following: A query that yields all accounts from a person...
3
by: dk | last post by:
Hi all, Would appreciate some advice on the following: I am trying to speed up an Access database connected to a SQL Server back-end. I know I can use a pass-through query to pass the sql...
4
by: d.p. | last post by:
Hi all, I'm using MS Access 2003. Bare with me on this description....here's the situation: Imagine insurance, and working out premiums for different insured properties. The rates for calculating...
5
by: Colleyville Alan | last post by:
I have some data in a table structured like this: Date Cust_ID CUSIP Amount 01/31/2005 060208 02507M303 27,061.84...
2
by: dskillingstad | last post by:
I would really appreciate someone's help on this, or at least point me in the right direction.... I'm working on a permit database that contains 12 tables, and rather than list all of the...
0
southoz
by: southoz | last post by:
Good ay all , I'm fairly new to access(a little over 5 weeks now). Since I'v started I have picked up a lot of useful information from forums such as this and in doing so will share that information...
3
by: epearce301 | last post by:
Hi, I have table1 and table2, both are linked, and a query has been run. I created forms that use both just the tables, and then another one using just the query. All of the information is there,...
7
by: jjmontreal | last post by:
Help, Short story is I have 2 tables. 1 for the Companies and 1 for the Employees. These are used in a Main form where the user enters the Company info with a sub form for the employees info. (1 to...
5
by: Ferasse | last post by:
Hi, I'm an occasional Ms-Access developer, so there is still a lot of stuff that I don't get... Right now, I'm working on a database that stores contractual information. One of the form that...
1
by: mandanarchi | last post by:
I've hunted for info about this and come up with loads. Jet 4.0 error - fixed in latest service pack. We all have latest service pack. Copy faulty table, delete old, remove fault field etc This...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.