duplicate data

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

Guest

My tables in my query are all linked by an account number. In all of the
tables there is only data for each account number once, except in my Alert
table. If the account number is in the table more than once it means it has a
different alert number.

If an account is in the Alert table twice, it will pull both alert numbers,
but it will give me the data for all of the other fields twice. My
informaiton is being duplicated. Is it possible to only pull the data from
the other tables once, but display all of the alet numbers from the Alert
table?
 
Dear Tracy:

What you describe is standard for queries. When there is a many-to-one
relationship between two tables (which is exactly what you seem to have
described) then all the selected information for columns from the table on
the "one" side of the relationship will be displayed.

Keep in mind that it is easily possible to sort the table by some column(s)
in the table on the "many" side of the relationship such that the several
rows related to the same row on the "one" side of the relationship are not
adjacent. Perhaps this idea will give you some idea why it must function as
you have observed.

If you choose to create a report of this that sorts it first by a unique key
to the table on the "one" side, it is easy to suppress repeating values
based on this key, and thus give a format of the information that is much
more like what you probably want. However, such an appearance is not
typical in a query.

Now, with a bit of work, this actually can be done in a query. I suggest
that before doing this much work there should be a pretty good reason for
why you must have it this way. Making a report of it will be much easier
and much more logical.

To do this in a query, we would rank all the rows within each "group" by the
key on the "one" side of the relationship, ranking by the unique key in the
"many" side. Then we would suppress all the values from the "one" side when
that rank is not 0 (which is to say, the first row).

This is some moderately advanced query work, and again it is harder to do
than in a report. In addition, a report does it much more quickly.

Tom Ellison
 
Back
Top