my query is blank (no records)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new to Access so forgive me if this is a basic question.

I have a bunch of clients from who I collect a lot of information. The
information is too much to fit into one table so I created 5 tables. The main
table is Table1 that has the basic information for each client (first name,
last name, address, etc). This table is linked to the other 4 tables, using
the client ID. (I created relationships among the tables)

Using the query wizard, I created a query that pulls out information from
each of the 5 tables. The wizard is pretty straightforward and I get through
it without much effort. Problem is when I finish my query and I open it up it
is BLANK! I have all the columns and column headings but no records. For the
life of me, I can't figure out what I am doing wrong. Help!
 
My suggestion would be to always BUILD your queries, slowly. One table at a
time. Start with your
client table. Use the wizard if you like and just add a few pertinent fields
from the one table and let the query open. Take note of the number of
records it returns. Now, in design view, add another table, just one. Create
your join. Check your records. Check the count of records. If you proceed in
this manner, the source of your trouble will reveal itself as a matter of
cause and effect.

Ed
 
Thanks. Great advice, I'll try that. I have one question though, what do you
mean by "create your join"? Again, sorry if that is a basic question.
 
I am new to Access so forgive me if this is a basic question.

I have a bunch of clients from who I collect a lot of information. The
information is too much to fit into one table so I created 5 tables.
Click to expand...

That's not a good reason to split data into five tables. It's a strong
suggestion that you need to turn your thinking "sideways". If you have
255 fields (the limit) in each of these tables, you should almost
certainly instead have a one-to-many relationship; rather than having
many FIELDS you would have many RECORDS. What's the structure of these
tables?
The main
table is Table1 that has the basic information for each client (first name,
last name, address, etc). This table is linked to the other 4 tables, using
the client ID. (I created relationships among the tables)
Click to expand...

One to one? One to many?
Using the query wizard, I created a query that pulls out information from
each of the 5 tables. The wizard is pretty straightforward and I get through
it without much effort. Problem is when I finish my query and I open it up it
is BLANK! I have all the columns and column headings but no records. For the
life of me, I can't figure out what I am doing wrong. Help!
Click to expand...

The query will be blank if there is any one of the four tables for
which there is no matching ClientID record.

Please post a description of these tables. I strongly suspect that a
better table design (and use of a Form with one or more Subforms,
instead of trying to use query datasheets) will ease your troubles!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thanks for your response John. I totally agree with you that my table design
is probably off. But as I said before I am new to Access and I am really not
sure how to better describe my problem so you can help. Specifically, I am
really not sure how to "describe" my tables. I thought I already did that in
my first post.

Anyway, that is all beside the point now. I followed Edward's advice and
started from scratch in design view and figured out the problem. It was
pretty simple actually, one of the tables did not have any data in it. I am
guessing that an Access query doesn't bring up records that are linked to
other records with zero/null values??

Now I have to go figure out how to get the query to bring up all the
information whether or not some records are blank...It's a never ending saga
I suppose.

Oh and by the way, I do have a one-to-many relationship and all the tables
have a matching ClientID record. A form sounds like a great idea, I'll try
that later.
 
Now I have to go figure out how to get the query to bring up all the
information whether or not some records are blank...
Click to expand...

Click on the Join line in the query design window and choose the
option "Show all records from <main table> and matching records from
<related table>" - it's called an Outer Join.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Well, I'm glad to see I'm not the only person here who has no interest in
eating turkey today!
To answer your question about what I meant by "creating a join", the truth
is if your tables
are correctly designed, Access will create the joins for you. In query
design view you would
see a thin black line between your Client table and (guessing here) your
Transaction table. Right-clicking
on this join line will give you a brief drop down list. Two choices. "Join
Properties" or "Delete". If you select "Join
Properties", you will get a choice of 3 possible joins which is what John
Vinson was pointing you to. Since you are
new to Access, I would suggest trying out all 3 possible joins just to see
what happens, but in the end you want
the one John recommended. If there is no line between the tables in your
query you can create a join by clicking on a
field in one of the tables in query design view and while holding the mouse
button down dragging it to the field in the
second table. The join will work if the data types match up (you can't join
a text field to a number field, etc) and if the fields
contain similar data (you aren't likely to find too many matches if you join
a CustName field to a PartDesc field even though they
are both text fields).
But, since you are just starting out, it would be a good idea to get your
tables right to begin with so it would be best if your
ClientId field is an autonumber field in the Client table and is set as the
primary key for the table. In your Transaction? table, you would want
ClientId to be a number field. Done this way, Access will automatically
create joins when you add these two tables
to a query.
John was right to question you on having 5 tables. When he asks for a
description, I think he means something like this.

Client table
ClientId
ClientName
Street
City
State
Zip
HomePhone
Email

Products table
ProductId
ProductName
Desc
CommodityCode
InventorySourceCode
PrimaryLocation
SecondaryLocation

Transactions table
TransactionId
CustId
ProductId
Date
Qty

Querying the Transaction table alone in a database consisting of the above
tables would give you a bunch of
meaning less numbers. Item 1 purchased by Customer 3 on such and such date,
for example. You would add the Client
and Products tables to the query to provide the readable data such as the
first and last name of the client and the desc
and part number of the purchased item.

Hope this helps.

Happy Turkey Day
 
Back
Top