Omit duplicates

  • Thread starter Thread starter Phil
  • Start date Start date
P

Phil

Hi,

I'm trying to print labels from a transaction table and a
mailing-list table. The mailing-list table has a one to
many relationship with the transaction table. My problem
is that I get duplicate labels because of the one to many
relationship. How do I get rid of the duplicates? I
tried using the DISTINCT command in the following code,
but it didn't work. Any Ideas? I have the date and
dollar fields to be able to restrict on those fields. The
Transaction.Mailning_List_Key is the field that duplicates
should be elimated on.

SELECT DISTINCT Transaction.Mailing_List_Key,
Transaction.Dollar_Amount, Transaction.Date, [Mailing
List].First_Name, [Mailing List].Last_Name, [Mailing
List].Organization, [Mailing List].Address_1, [Mailing
List].Address_2, [Mailing List].City, [Mailing
List].State, [Mailing List].Zip
FROM [Mailing List] INNER JOIN [Transaction] ON [Mailing
List].Mailing_List_Key = Transaction.Mailing_List_Key;

Thanks,
Phil
 
In the query design view set the unique value property to
yes. (This is the same as select distinct ......etc.)Keep
in mind this only hides duplicates where each data element
is identical. For instance if six people live at the same
address you will still get 6 mailing labels if their names
are not identical.
Hope this helps.
Fons
 
Thanks Fons
I tried that, but the problem is that I have a date and a
dollar amount (used to restrict the output) that would be
different for the same person. Therefore, the entire
record is not unique and I get as many lables for one
person as there are transactions in the transaction table.

I would think that this would be a common problem for
tables with a one to many relationship and that there
would be a solution.

Thanks again,
Phil
-----Original Message-----
In the query design view set the unique value property to
yes. (This is the same as select distinct ......etc.) Keep
in mind this only hides duplicates where each data element
is identical. For instance if six people live at the same
address you will still get 6 mailing labels if their names
are not identical.
Hope this helps.
Fons
-----Original Message-----
Hi,

I'm trying to print labels from a transaction table and a
mailing-list table. The mailing-list table has a one to
many relationship with the transaction table. My problem
is that I get duplicate labels because of the one to many
relationship. How do I get rid of the duplicates? I
tried using the DISTINCT command in the following code,
but it didn't work. Any Ideas? I have the date and
dollar fields to be able to restrict on those fields. The
Transaction.Mailning_List_Key is the field that duplicates
should be elimated on.

SELECT DISTINCT Transaction.Mailing_List_Key,
Transaction.Dollar_Amount, Transaction.Date, [Mailing
List].First_Name, [Mailing List].Last_Name, [Mailing
List].Organization, [Mailing List].Address_1, [Mailing
List].Address_2, [Mailing List].City, [Mailing
List].State, [Mailing List].Zip
FROM [Mailing List] INNER JOIN [Transaction] ON [Mailing
List].Mailing_List_Key = Transaction.Mailing_List_Key;

Thanks,
Phil
.
Click to expand...
.
Click to expand...
 
Phil. The key is to restruict the data in the underlying
query to only tose data items required for the label.
With other words, if you do not need the dollar amounts to
be printed ion the labels, do not include that field in
the query, you'll see that once you remove the unneeded
fields from the query and you have only the names, address
cities zip etc, youll endup with only one label per
address (assuming it to be an address label). You do want
the unique data but you do not want to include the other
data elements.
Hope this helps.
Fons
-----Original Message-----
Thanks Fons
I tried that, but the problem is that I have a date and a
dollar amount (used to restrict the output) that would be
different for the same person. Therefore, the entire
record is not unique and I get as many lables for one
person as there are transactions in the transaction table.

I would think that this would be a common problem for
tables with a one to many relationship and that there
would be a solution.

Thanks again,
Phil
-----Original Message-----
In the query design view set the unique value property to
yes. (This is the same as select distinct ......etc.) Keep
in mind this only hides duplicates where each data element
is identical. For instance if six people live at the same
address you will still get 6 mailing labels if their names
are not identical.
Hope this helps.
Fons
-----Original Message-----
Hi,

I'm trying to print labels from a transaction table and a
mailing-list table. The mailing-list table has a one to
many relationship with the transaction table. My problem
is that I get duplicate labels because of the one to many
relationship. How do I get rid of the duplicates? I
tried using the DISTINCT command in the following code,
but it didn't work. Any Ideas? I have the date and
dollar fields to be able to restrict on those fields. The
Transaction.Mailning_List_Key is the field that duplicates
should be elimated on.

SELECT DISTINCT Transaction.Mailing_List_Key,
Transaction.Dollar_Amount, Transaction.Date, [Mailing
List].First_Name, [Mailing List].Last_Name, [Mailing
List].Organization, [Mailing List].Address_1, [Mailing
List].Address_2, [Mailing List].City, [Mailing
List].State, [Mailing List].Zip
FROM [Mailing List] INNER JOIN [Transaction] ON [Mailing
List].Mailing_List_Key = Transaction.Mailing_List_Key;

Thanks,
Phil
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Thanks again,

That's where my problem lies. I need to include the
dollar amounts and date so that I can print labels for a
certain dollar range and a certain date range. But as you
said, when those fields are included then the records are
no longer unique and I get multiple labels. I still think
there must be a way around this problem. If I can't find
a solution I'll post my question again and try to explain
my problem better.

Phil
-----Original Message-----
Phil. The key is to restruict the data in the underlying
query to only tose data items required for the label.
With other words, if you do not need the dollar amounts to
be printed ion the labels, do not include that field in
the query, you'll see that once you remove the unneeded
fields from the query and you have only the names, address
cities zip etc, youll endup with only one label per
address (assuming it to be an address label). You do want
the unique data but you do not want to include the other
data elements.
Hope this helps.
Fons
-----Original Message-----
Thanks Fons
I tried that, but the problem is that I have a date and a
dollar amount (used to restrict the output) that would be
different for the same person. Therefore, the entire
record is not unique and I get as many lables for one
person as there are transactions in the transaction table.

I would think that this would be a common problem for
tables with a one to many relationship and that there
would be a solution.

Thanks again,
Phil
-----Original Message-----
In the query design view set the unique value property to
yes. (This is the same as select distinct ......etc.) Keep
in mind this only hides duplicates where each data element
is identical. For instance if six people live at the same
address you will still get 6 mailing labels if their names
are not identical.
Hope this helps.
Fons
-----Original Message-----
Hi,

I'm trying to print labels from a transaction table
Click to expand...
and
a
mailing-list table. The mailing-list table has a one to
many relationship with the transaction table. My problem
is that I get duplicate labels because of the one to many
relationship. How do I get rid of the duplicates? I
tried using the DISTINCT command in the following code,
but it didn't work. Any Ideas? I have the date and
dollar fields to be able to restrict on those fields.
The
Transaction.Mailning_List_Key is the field that
duplicates
should be elimated on.

SELECT DISTINCT Transaction.Mailing_List_Key,
Transaction.Dollar_Amount, Transaction.Date, [Mailing
List].First_Name, [Mailing List].Last_Name, [Mailing
List].Organization, [Mailing List].Address_1, [Mailing
List].Address_2, [Mailing List].City, [Mailing
List].State, [Mailing List].Zip
FROM [Mailing List] INNER JOIN [Transaction] ON [Mailing
List].Mailing_List_Key = Transaction.Mailing_List_Key;

Thanks,
Phil
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
You don't have to DISPLAY the fields if you only need to apply criteria against them.
Thanks again,

That's where my problem lies. I need to include the
dollar amounts and date so that I can print labels for a
certain dollar range and a certain date range. But as you
said, when those fields are included then the records are
no longer unique and I get multiple labels. I still think
there must be a way around this problem. If I can't find
a solution I'll post my question again and try to explain
my problem better.

Phil
-----Original Message-----
Phil. The key is to restruict the data in the underlying
query to only tose data items required for the label.
With other words, if you do not need the dollar amounts to
be printed ion the labels, do not include that field in
the query, you'll see that once you remove the unneeded
fields from the query and you have only the names, address
cities zip etc, youll endup with only one label per
address (assuming it to be an address label). You do want
the unique data but you do not want to include the other
data elements.
Hope this helps.
Fons
-----Original Message-----
Thanks Fons
I tried that, but the problem is that I have a date and a
dollar amount (used to restrict the output) that would be
different for the same person. Therefore, the entire
record is not unique and I get as many lables for one
person as there are transactions in the transaction table.

I would think that this would be a common problem for
tables with a one to many relationship and that there
would be a solution.

Thanks again,
Phil

-----Original Message-----
In the query design view set the unique value property to
yes. (This is the same as select distinct ......etc.)
Keep
in mind this only hides duplicates where each data
element
is identical. For instance if six people live at the
same
address you will still get 6 mailing labels if their
names
are not identical.
Hope this helps.
Fons
-----Original Message-----
Hi,

I'm trying to print labels from a transaction table and
a
mailing-list table. The mailing-list table has a one to
many relationship with the transaction table. My
problem
is that I get duplicate labels because of the one to
many
relationship. How do I get rid of the duplicates? I
tried using the DISTINCT command in the following code,
but it didn't work. Any Ideas? I have the date and
dollar fields to be able to restrict on those fields.
The
Transaction.Mailning_List_Key is the field that
duplicates
should be elimated on.

SELECT DISTINCT Transaction.Mailing_List_Key,
Transaction.Dollar_Amount, Transaction.Date, [Mailing
List].First_Name, [Mailing List].Last_Name, [Mailing
List].Organization, [Mailing List].Address_1, [Mailing
List].Address_2, [Mailing List].City, [Mailing
List].State, [Mailing List].Zip
FROM [Mailing List] INNER JOIN [Transaction] ON
[Mailing
List].Mailing_List_Key = Transaction.Mailing_List_Key;

Thanks,
Phil
.

.

.
Click to expand...
.
Click to expand...
Click to expand...
 
Thank you John,

Once I clicked OFF the "show" button on my Date and Dollar
fields, I got just what I needed. I knew there must be a
way but I never tried that. So simple.

Thanks again
Phil
-----Original Message-----
You don't have to DISPLAY the fields if you only need to apply criteria against them.
Thanks again,

That's where my problem lies. I need to include the
dollar amounts and date so that I can print labels for a
certain dollar range and a certain date range. But as you
said, when those fields are included then the records are
no longer unique and I get multiple labels. I still think
there must be a way around this problem. If I can't find
a solution I'll post my question again and try to explain
my problem better.

Phil
-----Original Message-----
Phil. The key is to restruict the data in the underlying
query to only tose data items required for the label.
With other words, if you do not need the dollar amounts to
be printed ion the labels, do not include that field in
the query, you'll see that once you remove the unneeded
fields from the query and you have only the names, address
cities zip etc, youll endup with only one label per
address (assuming it to be an address label). You do want
the unique data but you do not want to include the other
data elements.
Hope this helps.
Fons
-----Original Message-----
Thanks Fons
I tried that, but the problem is that I have a date
Click to expand...
and
a
dollar amount (used to restrict the output) that would be
different for the same person. Therefore, the entire
record is not unique and I get as many lables for one
person as there are transactions in the transaction table.

I would think that this would be a common problem for
tables with a one to many relationship and that there
would be a solution.

Thanks again,
Phil

-----Original Message-----
In the query design view set the unique value property
to
yes. (This is the same as select distinct ......etc.)
Keep
in mind this only hides duplicates where each data
element
is identical. For instance if six people live at the
same
address you will still get 6 mailing labels if their
names
are not identical.
Hope this helps.
Fons
-----Original Message-----
Hi,

I'm trying to print labels from a transaction table and
a
mailing-list table. The mailing-list table has a one
to
many relationship with the transaction table. My
problem
is that I get duplicate labels because of the one to
many
relationship. How do I get rid of the duplicates? I
tried using the DISTINCT command in the following code,
but it didn't work. Any Ideas? I have the date and
dollar fields to be able to restrict on those fields.
The
Transaction.Mailning_List_Key is the field that
duplicates
should be elimated on.

SELECT DISTINCT Transaction.Mailing_List_Key,
Transaction.Dollar_Amount, Transaction.Date, [Mailing
List].First_Name, [Mailing List].Last_Name, [Mailing
List].Organization, [Mailing List].Address_1, [Mailing
List].Address_2, [Mailing List].City, [Mailing
List].State, [Mailing List].Zip
FROM [Mailing List] INNER JOIN [Transaction] ON
[Mailing
List].Mailing_List_Key = Transaction.Mailing_List_Key;

Thanks,
Phil
.

.

.

.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top