query triplicating data

  • Thread starter Thread starter Annemarie
  • Start date Start date
A

Annemarie

I have a look up form that searches the database for names. When I select an
option to search by, the subform displays the results 3 times. I only have
each person listed once in the database, but for some reason the look up is
showing 3. Any ideas?
 
You have the subform mapped to the main form incorrectly.
Look at the Link Child Fields and Link Master Fields properties.
How are the 2 tables related?

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Lets say your main form is bound to a table containg a column called
PaymentId and your sub-form is bound to a table with a column called RecordId
and the PaymentId is related to the RecordId. In other words you have
multiple records in your second table where RecordId = Payment Id. Then your
links would be Master=PaymentId, Child=RecordId.
You need to figure out how the data in your main form and subform is related.
-- David
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
When I try to link them together, I get this error message: "Can't build a
link between unbound forms."
 
That is your problem then. Where do your forms get their data? Normally a
form is bound (recordsource property) to either a table or a query and the
data in the main form is related to the data in the subform and that is how
the subform gets its data.
How is your subform populated, are you clicking a button to do it? If so,
you need to post the code of what the button is doing.
 
Here is the SQL for the main form:
--------------------
Option Compare Database

Private Sub Combo0_Click()

End Sub

Private Sub Combo0_Undo(Cancel As Integer)

End Sub

Private Sub cboAlphaCateg_AfterUpdate()
Me!lookup_query_subform.Requery
End Sub

Private Sub cboAlphaFirst_AfterUpdate()
Me!lookup_query_subform.Requery
End Sub

Private Sub cboAlphaLast_AfterUpdate()
Me!lookup_query_subform.Requery
End Sub

Private Sub cboAlphaDept_AfterUpdate()
Me!lookup_query_subform.Requery
End Sub
Private Sub AddContact_Click()
On Error GoTo Err_AddContact_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "add_contact"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_AddContact_Click:
Exit Sub

Err_AddContact_Click:
MsgBox Err.Description
Resume Exit_AddContact_Click

End Sub
 
Hi Dorian,
That is your problem then.
Click to expand...

Don't assume that a search form must be a bound form. I routinely create
unbound QBF (Query by Form) search forms, which include a bound subform. The
Link Master Field / Link Child Field properties are left blank. Here are some
examples, if you'd like to take a look:

Simplist "bare-bones" QBF (includes one multi-select listbox):
http://www.accessmvp.com/TWickerath/downloads/elements.zip

Modified version of QBF from "Access 2000 Power Programming", by F. Scott
Barker:
http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

Sample QBF that uses Northwind tables
See the Feb. 12, 2008 download
http://www.seattleaccess.org/downloads.htm

The last sample, currently posted on the Seattle Access downloads page,
includes a sample database and a Word document that makes an attempt at
explaining how this technique works.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Hi Annemarie,

You possibly have a cartesian product query in the recordsource for the
subform. A cartesian product results when a query contains more than one
table, but there is no join established between one or more of the tables.
When this happens, the recordset will contain the number of records selected
from each table multiplied together. As an example, open the Northwind sample
database (Northwind.mdb). Add the Customers table and the Order Details table
to the query. Select the Company Name and Quantity fields. When you run the
query, you should see 196,105 records (example given for a "clean" copy [ie.
no deleted or added records] in the copy of Northwind that shipped with
Access 2003). There are 91 customer records multiplied by 2155 Order Detail
records (91 * 2155 = 196,105).

Open the query in design view. Add the Orders table. When you re-run the
query, you should see that the recordset now contains 2155 records. You
should also see how the three tables have join lines.

Another possibility is that you are including fields in the SELECT portion
of the query that come from child tables. For example, in the above fixed
version of the query with 2155 records, we see "Alfreds Futterkiste" listed
12 times. In this case, you might want to have the Quantity as a part of the
WHERE clause in the query, but not in the SELECT. Perhaps using a grouped
query (or adding the DISTINCT keyword) will help prevent your triplicate
records.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
I think I understand what you are saying. I added all the linked tables to
the queries and corrected missing joins, but I still have 3 displaying, they
are just in alphabetical order now.

Did I miss something?



Tom Wickerath said:
Hi Annemarie,

You possibly have a cartesian product query in the recordsource for the
subform. A cartesian product results when a query contains more than one
table, but there is no join established between one or more of the tables.
When this happens, the recordset will contain the number of records selected
from each table multiplied together. As an example, open the Northwind sample
database (Northwind.mdb). Add the Customers table and the Order Details table
to the query. Select the Company Name and Quantity fields. When you run the
query, you should see 196,105 records (example given for a "clean" copy [ie.
no deleted or added records] in the copy of Northwind that shipped with
Access 2003). There are 91 customer records multiplied by 2155 Order Detail
records (91 * 2155 = 196,105).

Open the query in design view. Add the Orders table. When you re-run the
query, you should see that the recordset now contains 2155 records. You
should also see how the three tables have join lines.

Another possibility is that you are including fields in the SELECT portion
of the query that come from child tables. For example, in the above fixed
version of the query with 2155 records, we see "Alfreds Futterkiste" listed
12 times. In this case, you might want to have the Quantity as a part of the
WHERE clause in the query, but not in the SELECT. Perhaps using a grouped
query (or adding the DISTINCT keyword) will help prevent your triplicate
records.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Annemarie said:
I have a look up form that searches the database for names. When I select an
option to search by, the subform displays the results 3 times. I only have
each person listed once in the database, but for some reason the look up is
showing 3. Any ideas?
Click to expand...
Click to expand...
 
Hi Annemarie,

I'm a little confused when you state:
"I still have 3 displaying, they are just in alphabetical order now."

If the records are in alphabetical order, then this implies that they are
not identical ("triplicating data"). Is it possible for you to send a copy of
your database? You can remove any confidential data, replacing it with some
dummy data (Mickey M. Mouse, Minnie M. Mouse, Donald D. Duck, etc.). If you
are interested, send me a private e-mail message with a valid reply-to
address. My e-mail address is available at the bottom of the contributor's
page indicated below. Please do not post your e-mail address (or mine) to a
newsgroup reply. Doing so will only attract the unwanted attention of
spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with a query 1
Query Parameter using multiple options 0
Access Field won't show as currency in the form, shows just a number (but works fine in the query) 1
Access Need help in making asset history in my current database. 1
Create a Form (with Subforms) Based on A Query/Queries 1
How to fix To-do List and reminders showing triplicate items 1
Monthly Inspection Database 0
change to SQL - where and what? 1

Back
Top