result from query gives thousands of records

[Guest]

Hello, and thanks for reading my question.

I have a pretty complex database with about 30 tables. My record count in my
main table is 173 records. The problem is, when I do a query asking for
information, I am getting a result of about 2000 records. Does anyone know
what would cause this?

Thanks so much in advance.

-Blenvid

 

[Guest]

If you link two tables by Id for example, and this Id will apear 20 times in
1 table, and 3 times in the second tables, you will be displayed with 60
records.

If you want to remove duplicates then use distinct in the query
Select Distinct Field1, Field2 Form .....

 

[Guest]

Thanks for the quick response Ofer.

Many of my tables are linked via a 1-to-1 relationship with my main table.
But then, some of the entries in other tables are linked via 1-to-many to
other tables.

Forgive me, I don't quite understand your directions:

If you want to remove duplicates then use distinct in the query
Select Distinct Field1, Field2 Form .....

Please explain further.

-Blenvid

 

[Rick Brandt]

Thanks for the quick response Ofer.

Many of my tables are linked via a 1-to-1 relationship with my main
table. But then, some of the entries in other tables are linked via
1-to-many to other tables.

Forgive me, I don't quite understand your directions:


Please explain further.

Simpler example. If I have a table of 100 Customers and write a single table
query...

SELECT CustomerID FROM Customers

I will get 100 records.

If I add the Orders table to that query and have a query like...

SELECT CustomerID FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

Then evey customer that has zero orders will not be in the output, customers
with exaclty one order will appear once in the output, but a customer that has
50 orders wil appear in the output 50 times (once for each order). If I change
the Query to...

SELECT DISTINCT CustomerID FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

Then duplicates in the output will be supressed.

In your case you have 30 tables in the query with various 1 to 1 and 1 to many
relationships so the number of rows in the output is going to be affected by
every one of those joins.

 

[Guest]

Hi Rick,

Thanks for your input.

Your example is clean and concise. I understand now.

Will this "Select Distinct" solve the issue?

-Blenvid

 

[Guest]

Yes, It should.
Unless you have another field returned in your query that can make each
record unique, such as date field.
Try it, and see the result.
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[Guest]

Thank you guys. I will try that tomorrow at work Ofer. I'll report back in
then and let you know what happens.

-Blenvid

 

[Guest]

Good morning fellas,

I payed more attention to the tables when I was building the queries, if
they were connected or not in the query design view. When the query worked,
they were connected. When the query didn't work, they weren't connected.

Anyway, here's the code from the query which returns about 6,000 records,
the one which doesn't work, and which in design view, the tables are not
connected:


SELECT DISTINCT tbl_ServerIPs.serverName, tbl_ServerIPs.ip,
tbl_CommandList.commandName
FROM tbl_ServerIPs, tbl_CommandList;


You can see I put "SELECT DISTINCT" in there. This still didn't solve the
problem.

Here's the code from a near identical query, with the addition of another
table in the query. This additional table is the bridge between the tables
used in the same query above , but in "Design View," the tables are now
connected together to this center table, [tbl_ServerPhysicalLocation].


SELECT tbl_ServerIPs.serverName, tbl_ServerIPs.ip,
tbl_ServerPhysicalLocation.row, tbl_ServerPhysicalLocation.rack,
tbl_CommandList.commandName
FROM tbl_CommandList INNER JOIN (tbl_ServerIPs INNER JOIN
tbl_ServerPhysicalLocation ON tbl_ServerIPs.serverName =
tbl_ServerPhysicalLocation.serverName) ON tbl_CommandList.commandName =
tbl_ServerPhysicalLocation.commandName;


I don't know if this means anything or not, but I see that there are some
INNER JOINs here not present in the first query.

I'm looking forward to hearing you guys tear this code apart.

Thanks so much.

-Blenvid

 

[Rick Brandt]

Good morning fellas,

I payed more attention to the tables when I was building the queries,
if they were connected or not in the query design view. When the
query worked, they were connected. When the query didn't work, they
weren't connected.

"Not Connected"? Do you mean no join lines between them? If you have a table
in a query that is not joined to any other then you get a "cartesian result".
That is every row will be repeated for every row in that table.

EX: Two tables each with 10 records in a query with no join will produce 100
rows (10 * 10).

If a table has no relationship to other tables in the query then it doesn't
belong in there. The exception being the rare occassion where a cartesian
result is actually what you want.

 

[Guest]

Hi Rick,

The 2 tables are connected in the Relationship view of Access through the
3rd table I mentioned in my last post, but they are not connected in the
Query Design View.

To further explain, the table [tblServerIPs] is connected to table
[tbl_ServerPhysicalLocation] via a 1-to-many connection. Table
[tbl_ServerPhysicalLocation] is connected to [tbl_ServerNameMASTER] via a
1-to-1 relationship. Finally, [tbl_ServerNameMASTER] is connected to
[tbl_ServerIPs] via a 1-to-1 relationship. Graphically, it looks like this.

[tbl_ServerCommandList]1---00[tblServerPhyiscalLocation]1---1[tbl_ServerNameMASTER]1--1[tbl_ServerIPs]

I hope that helps.

-Blenvid

 

[Rick Brandt]

Hi Rick,

The 2 tables are connected in the Relationship view of Access through
the 3rd table I mentioned in my last post, but they are not connected
in the Query Design View.

In the query is the only place that matters. If there is no join then you
get a cartesian result.

 

[Guest]

Alright. Thank you very much Rick. I learned that one the hard way.

-Blenvid