Simple Query only returning 5 records

[chard]

I am very new to Access, therefore I'm pretty sure I know where the
problem is. That said...I have a query built of multiple imported
tables. I'm mostly using it to combine values for data analysis.
I have been changing the query up and rerunning it over and over.
Yesterday I got some error that said I didn't have enough memory or
disk space to finish the query. Now I can only get 5 records out when
I run the query. I don't know if the two are related but something is
up. In trying to discover the issue I have deleted all table
connections and simply tried to return all the values of one field of
one table...still ony get 5 results.

Can someone help?

Chard

 

[Guest]

First make a backup of the database then try a compact and repair to free up
space.

Next show us the SQL. Open the query in design view. Next go to View, SQL
View and copy and past it here.

 

[chard]

First off...I love the Kieth Bontrager Quote. Unfortunate but true.

I did the compact and repair and nothing. I created a new query with
similar tables and it has worked so far. We'll see how long that
lasts.
Here is the code:
SELECT TOP 5 UGS_AMID_MAP.AMIDL2
FROM DB_LINK_BL860C_DATA_W_AMIDS, COMBINED_AMID_CROSS_REFERENCE,
DB_LINK_BCS_TOP_ACCTS, UGS_AMID_MAP;

Now that I look at it...is it saying that since I don't have any
relationships it is trying to pull the common data from all the
tables?

Thanks for responding

Chard

 

[John W. Vinson]

First off...I love the Kieth Bontrager Quote. Unfortunate but true.

I did the compact and repair and nothing. I created a new query with
similar tables and it has worked so far. We'll see how long that
lasts.
Here is the code:
SELECT TOP 5 UGS_AMID_MAP.AMIDL2
FROM DB_LINK_BL860C_DATA_W_AMIDS, COMBINED_AMID_CROSS_REFERENCE,
DB_LINK_BCS_TOP_ACCTS, UGS_AMID_MAP;

Now that I look at it...is it saying that since I don't have any
relationships it is trying to pull the common data from all the
tables?

Well, the reason you're only getting five records is the TOP 5 clause in the
query: you're ASKING for only five records.

And yes, the fact that you have no joins means that every record from each
table will be presented along with every record from each of the OTHER tables.
If each of these four tables contains 100 records, you'll get all
100x100x100x100 = 100,000,000 possible combinations. I rather doubt that is
what you want!!!

Do any of these tables have a Primary Key? Are there any relationships
established between the tables (apparently not)? Are there any *logical*
relationships between the data in the tables upon which relationships could be
based?

John W. Vinson [MVP]

 

[Guest]

As you noted there aren't any joins between the tables/queries in the SQL
statement. This will cause a Cartesian product which usually is a mistake.
You need a join between them or else you could be creating thousands if not
millions of bogus records. Is there a line between the tables in the query
design QBE grid? There needs to be. In an SQL statement there would either be
the word "JOIN" between the tables or a WHERE clause that joins at least one
field in each table.

While the lack of relationships is a big problem, the reason for only
returning 5 records is pretty obvious.

SELECT TOP 5. That's telling Access to only return the top 5 records. If
there were less than 5 records, that's all it would return. If there was a
tie for the 5th record, it would return all the ties. You're probably seeing
a lot of bogus records due to the Cartesian product caused by a lack of
joins. But for the most part, you're only going to get 5 records until you
remove "TOP 5" from the SQL statement.

BTW: If you do need to use something like TOP 5, you need an ORDER BY clause
or Sort on the SQB grid so that Access can rack and stack the records in a
meaningful order.