By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

Blank Query Results

P: 14
I need help in finding out why I am not getting any data in my query.

When I select all the fields in one table in my query grid and run it, the data is there. However, when I try to do it using to related tables and pull fields from both table in my query rid and run the query, data from both related tables are not showing in my query datasheet. What is the problem? The relationship line in my query is showing that the table are linked.

Thanks for your response in advance.
Feb 24 '09 #1
Share this Question
Share on Google+
16 Replies


Expert 100+
P: 1,287
The relationship line means you are doing an inner join on the linked field, so only records that are equal in those fields from both tables will be shown.
Feb 24 '09 #2

P: 14
BNW
@ChipR

Thanks for responding.

P.S. I have enough data in the fields and some are null fields. When I run the query and adding my criterias, the data should still show in my query datasheet. It is not doing it.
Feb 24 '09 #3

Expert 100+
P: 1,287
Did you run the query first without the criteria to verify that you have some records to apply the criteria to?
Feb 24 '09 #4

P: 14
BNW
@ChipR
Yes, I did one table at a time. The results showed.

But when I combined both tables in my query grid and selected the fields I wanted from both tables to be in my datasheet, none of the data shows in my datasheet from both tables after I ran the query. However, the relationship is established.
Feb 24 '09 #5

Expert 100+
P: 1,287
Did you run the query with both tables and no criteria? I've had this happen when I expected NULL to match up with "" (empty string), and it didn't. I changed all the NULLs to blanks and then the join produced the results I expected. Double check to make sure the line between your tables in the query is between the two fields that have equal values in them.
Feb 24 '09 #6

P: 14
BNW
@ChipR

Yes, I ran the table with a criteria in it. It still didn't give me not results (empty data). I will probably need to check to see if the relationship line between both of my fields have equal values. I'll let you know. Thanks
Feb 24 '09 #7

P: 14
BNW
@ChipR

I checked my related fields in my table. The primary table have both Letter and numbers in the field and the Foreign table have regular letters (it is a combox box field in which I can select the values from the list). However, both of them have the same data types.
Feb 24 '09 #8

Expert 100+
P: 1,287
Your query is only going to have results when the values in the two related fields are exactly equal. It sounds like your two fields are not actually related at all.
Feb 24 '09 #9

P: 14
BNW
@ChipR
Here is an example.

Primary Key Field (CustomerNumber) value is "AC001"

Foreign Key Field (CustomerNumber) value is "All Creatures". This is a lookup field that have the value list box.

I'm trying to rebuild this database from my Access 2000 Bible reference/text book called "Mountain Hospital Animal Database" and that's what the relationship is showing for these two tables. Both of these fields have the Text datatype.
Feb 24 '09 #10

Expert 100+
P: 1,287
As you can see, the value "All Creatures" is not a CustomerNumber. The CustomerNumber in the foreign key should also be "AC001" and the "All Creatures" should be in another field, like Description (or whatever you want to call it).
Feb 24 '09 #11

P: 14
BNW
@ChipR
Ok. Thanks

I just don't understand why for this database table they have them fields the way they are. I wish I can send you a copy of this relationship printout sheet through MS Word so you can see what I am trying to convey. It is not a 1 to many relationship. It is a 1 to 1 relationship.
Feb 24 '09 #12

Expert 100+
P: 1,287
I think I do understand your situation, and as best I can tell, the fields are just labeled improperly. Unless your value list is supposed to have 2 columns, and the 1st is the actual value, but is hidden. The FK values have to be PKs in the other table, regardless of the relationship.
Feb 24 '09 #13

P: 14
BNW
@ChipR
I understand what you are saying about the 2 column values. I'll recheck the table documentary summary I printed out as well as the main Mountain Animal Hospital Database to see if it does have the 2 columns values with the Primary Key column as well as the other value column.

I'll let you know. Thanks for all of your help.
Feb 24 '09 #14

P: 14
BNW
@ChipR


Hi ChipR

I check the other database in which I am using the recreate it. It does have 2 columns but the other value column have the PK values.
Thanks again.
Feb 24 '09 #15

Expert 100+
P: 1,287
No problem. Glad you got it straightened out.
Feb 24 '09 #16

P: 14
BNW
@ChipR
OK Thanks a million
Feb 24 '09 #17

Post your reply

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