Record Duplication, multiple copies in Form

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

Guest

Hello,

I have created three forms based off of two tables that have a one to many
relationship. The first two forms operate just fine. The third form however
displays multiple copies of the same records.

The third form has two identifying fields, both of which are numbers. The
first number is ID#, the second number is ORDER#. One ID# can have multiple
ORDER#s. In the third form, some combinations of ID# and Order# appear
multiple times when I browse through records using the arrow buttons located
at the bottom of the form.

This database is in the development stage. I only have two ID#s. The first
ID# has one ORDER# associated with it. The second ID# has 3 ORDER#s
associated with it. It is the second ID# / ORDER# combination that displays
multiple copies in form three. Each order# associated with the second ID#
appears three times when I browse through records in the third form. Can
someone help me identify what the problem is?

Thanks,
Diana
 
Hi, Diana.
Can
someone help me identify what the problem is?
Click to expand...

It appears that the third form's Record Source Property is a query or SQL
statement which contains a Cartesian join, instead of standard ANSI SQL-92
syntax. Look at the SQL Statement in this form. Does it follow the pattern:

SELECT ID, OrderNum
FROM Customers, Orders;

or:

SELECT ID, OrderNum
FROM Customers INNER JOIN Orders ON Customers.ID = Orders.ID;

The first pattern is the Cartesian join, which will match every possible
combination of ID and OrderNum which meet the criteria (none are shown in
this example, but criteria are found in the WHERE and HAVING clauses of
queries). It is rare that the Cartesian data set is needed when writing a
query. Most likely, the second pattern above is what you need in your third
form.

Since you are a new database developer and programmer, I highly recommend
that you avoid using special characters and spaces in your identifiers (those
are variables and object names). The amount of time chasing bugs or redoing
development work is just not worth it. By the way, it's an excellent idea to
use a query as a Record Source for a form instead of the table directly,
because this allows much flexibility when future changes are needed or
capabilities need to be added to the application.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.
 
Hi,

This is the same form we were discussing previously. I deleted it (it's in
database heaven now), so this isn't an issue for me anymore.

Thanks,
Diana
 
Back
Top