data pulling multiple times

[MsWatts]

I have a query that is setup to pull data from a form. When I run the query
it is pulling quadruple the data required. I have tried everything to
troubleshoot this with no luck. If I have a relationship setup and I create
the same relationship in my query will that cause the query to pull
duplicated data? Please help...


Thanks

 

[Jeff Boyce]

This may seem picayune, but I think it helps to have a clear picture ...

Unless your query is using selection criteria that is entered on a form,
your query will be pulling data from one/more underlying tables, not from
forms.

And if you have a parent-child relationship between two tables, and have,
say, four rows of "child" records per each "parent", when you query the two
of them together, you'll get, SURPRISE!, 4 rows per parent record!

Since queries are really just a way to get data, it may not matter that some
of it is repeated. You could use the query's results to feed a report, and
use the Sorting & Grouping in the report to hide the duplication.

Another possibility ... if you add two tables to a query but do not join
them on one/more common fields, you get the "product" (number of rows in
table1 times number of rows in table2). Is this what's happening?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jerry Whittle]

There's only one way for us to know: Show us the SQL. Open the query in
design view. Next go to View, SQL View and copy and past it here.

Information on primary keys and relationships would be a nice touch too.

 

[MsWatts]

Yes Jeff that's exactly what's happening! My rows are being multipled and
it's driving me totally crazy because no matter what join changes or table
updates I make nothing is fixing my problem. Can you help?

 

[John W. Vinson]

Yes Jeff that's exactly what's happening! My rows are being multipled and
it's driving me totally crazy because no matter what join changes or table
updates I make nothing is fixing my problem. Can you help?

Not without seeing the query, no. Jeff's a really sharp guy but he hasn't
claimed to be clairvoyant.

Please post the SQL view of the query and some description of the tables.

 

[Jerry Whittle]

Here's the thing to check: Make sure that at least one of the fields in the
join is the primary key for that table. If not there's a good chance that you
are seen a Cartesian product returned. For Example the two tables below are
joined on the Field1 field:

Table1.Field1 Table2.Field1
Jim Jim
Jim Jim

You might thing that joining on Jim would produce only 2 records, but it is
actually 4 records! Each Jim on one side is matched with both Jim's on the
other. Add another Jim in both tables and the records returned go up to 9!

Check out my reply below. If you provide the SQL statement for the query, we
could help you find out the problem.

 

[Jeff Boyce]

I knew you were going to say that, John ... (prescient, maybe, clairvoyant,
nope)...

Jeff B.