Beginner query questions.

[Brenda]

1) I am LEFT OUTER JOIN table1 and table2 on cust_name. Table1 has no
duplicates on cust_name, but table2 does. I do not want any duplicate
cust_name in my result set. The DISTINCT will not work because the entire row
will not be a duplicate. What can I do.

2) If I have a table1 of cust_no, how can delete these from table2? I just
do not know the format of a DELETE SQL and Access has almost no SQL help
items (that I could find)

3) How can I control the data type of my result fields? I combined several
fields with '&' and the resulting table had the field as a text field. How
can I force the field to be a memo? I am joining a date, and two text fields.

Thanks

Brenda

 

[Tom Ellison]

Dear Brenda:

Responses below:

1) I am LEFT OUTER JOIN table1 and table2 on cust_name. Table1 has no
duplicates on cust_name, but table2 does. I do not want any duplicate
cust_name in my result set. The DISTINCT will not work because the entire row
will not be a duplicate. What can I do.

The first thing to do is to determine what you WANT it to do. Since
DISTINCT does not remove the duplicated values from table2, as you say
because there are columns from that table that do duplicate your key
values, then you must create some rule that choses which of the
several rows are being joined from that table. You have presented the
query engine with a situation where there are several different
combinations of data possible and not told it which one you desire in
each case. All it could do is give you all the combinations.
Basically, it is doing the best possible thing given what you have
told it to do - give you all the combinations you asked of it.

2) If I have a table1 of cust_no, how can delete these from table2? I just
do not know the format of a DELETE SQL and Access has almost no SQL help
items (that I could find)

Do you want to delete all of them from table2? Or all but one for
each cust_no? If the latter, it is the same problem as your #1. You
must specify how it is to choose the one to keep and which to throw
away. This is your responsibility, not the database's. It will not
arbitrarily choose for you.

3) How can I control the data type of my result fields? I combined several
fields with '&' and the resulting table had the field as a text field. How
can I force the field to be a memo? I am joining a date, and two text fields.

It sounds like you are using a make-table query. This gives you no
control. It is a lazy way to do something quickly and gives you no
control. If you want control, design the destination table, clear it,
and append to it. That gives you control.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Vinson]

1) I am LEFT OUTER JOIN table1 and table2 on cust_name.

That's a BAD idea. Names are not unique. I have three friends name
Fred Brown, Fred Brown, and Fred Brown; what if they were all
customers?

Table1 has no
duplicates on cust_name, but table2 does. I do not want any duplicate
cust_name in my result set. The DISTINCT will not work because the entire row
will not be a duplicate. What can I do.

If there are multiple records for Fred Brown in Table2, which of them
do you want to see?

2) If I have a table1 of cust_no, how can delete these from table2? I just
do not know the format of a DELETE SQL and Access has almost no SQL help
items (that I could find)

A DELETE query deletes entire RECORDS, with all their data. Just what
is it that you want to "delete"? Do you want to Update a Cust_No field
to NULL? How do you want to identify the value to be "deleted"?

3) How can I control the data type of my result fields? I combined several
fields with '&' and the resulting table had the field as a text field. How
can I force the field to be a memo? I am joining a date, and two text fields.

This would be a VERY strange thing to do. Storing existing data from
three fields redundantly into a fourth composite field would violate
all sorts of relational rules! Could you explain what *real world*
problem you're trying to solve? I'm fairly sure it can be solved
without any of these kludges!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Brenda]

I work for one of the 10 largest banks in the country. We are merging with
another large bank and the work must be done by February. My manager has
attended over 100 hours of meeting with the clients to come up with specs. I
get delievered my very small peice of the big pie specs and am expected to
have them completed by the deadline. I could continue to ask my manager "are
you sure this is what they want", but given the deadlines and the fact that
these are the specs the clients signed off on, I know what the answer would
be.

That's a BAD idea. Names are not unique. I have three friends name
Fred Brown, Fred Brown, and Fred Brown; what if they were all
customers?

If there are multiple records for Fred Brown in Table2, which of them
do you want to see?

I try to simplify my questions to make them easier to understand. Yes
cust_name was a poor choice of an example. It is not cust_name but
exception_oid. I do not care which one I see. The other bank has bad data.
There should not be any duplicates, but there are. Nobody is going to "clean
up" the data. I have to deal with it.

A DELETE query deletes entire RECORDS, with all their data. Just what
is it that you want to "delete"? Do you want to Update a Cust_No field
to NULL? How do you want to identify the value to be "deleted"?

I want to delete the entire row from table2. If there are multiple rows in
table2 that have the same table1.exception_oid, then I want to delete them
all.

This would be a VERY strange thing to do. Storing existing data from
three fields redundantly into a fourth composite field would violate
all sorts of relational rules! Could you explain what *real world*
problem you're trying to solve? I'm fairly sure it can be solved
without any of these kludges!

I agree that this is very strange. I have already discussed it with my
manager and this is what the clients signed off on. I am joining a
date&":"&text&"+"&memo and the result column is a text field. I am worred
that some of the memo field might be truncated.

 

[Brenda]

1) I am LEFT OUTER JOIN table1 and table2 on cust_name. Table1 has no

The first thing to do is to determine what you WANT it to do. Since
DISTINCT does not remove the duplicated values from table2, as you say
because there are columns from that table that do duplicate your key
values, then you must create some rule that choses which of the
several rows are being joined from that table. You have presented the
query engine with a situation where there are several different
combinations of data possible and not told it which one you desire in
each case. All it could do is give you all the combinations.
Basically, it is doing the best possible thing given what you have
told it to do - give you all the combinations you asked of it.

I do not care which one is removed from the result set or which one stays.
The data is imperfect and it is out of my project scope to clean up the data.
Can I use a Last, First, or some other function? I just want to eliminate
duplicates on a specific field. Is that possible?

Do you want to delete all of them from table2? Or all but one for
each cust_no? If the latter, it is the same problem as your #1. You
must specify how it is to choose the one to keep and which to throw
away. This is your responsibility, not the database's. It will not
arbitrarily choose for you.

I want to delete all matching rows from table2. Would I have to do this with
multiple SQL statements? Again, the data is imperfect. There should not be
duplicates in table2, but over 10 years and various mergers, the data is not
perfect.

It sounds like you are using a make-table query. This gives you no
control. It is a lazy way to do something quickly and gives you no
control. If you want control, design the destination table, clear it,
and append to it. That gives you control.

Thanks. I will do that. Just curious though, is there a way to specifiy the
output data type. I come from a Visual FoxPro back ground and their SQL lets
you control the output field's definition in a make table query.

 

[John Vinson]

On Wed, 20 Oct 2004 07:07:02 -0700, Brenda

Answers inline.

I work for one of the 10 largest banks in the country. We are merging with
another large bank and the work must be done by February. My manager has
attended over 100 hours of meeting with the clients to come up with specs. I
get delievered my very small peice of the big pie specs and am expected to
have them completed by the deadline. I could continue to ask my manager "are
you sure this is what they want", but given the deadlines and the fact that
these are the specs the clients signed off on, I know what the answer would
be.

My sympathies.... :-{(

I want to delete the entire row from table2. If there are multiple rows in
table2 that have the same table1.exception_oid, then I want to delete them
all.

That's actually pretty easy:

DELETE Table2.*
FROM Table2 INNER JOIN Table1
ON Table1.exception_oid = table2.exception_oid;

This will delete all rows in Table2 if they have a matching
exception_oid in Table1. If you want to delete ALL BUT ONE of the
matching records, try instead

DELETE Table2.*
FROM Table2 INNER JOIN Table1
ON Table1.exception_oid = table2.exception_oid
WHERE Table2.PKField > DMin("[PKField]", "[Table2]",
"[Exception_OID] = " & [Exception_oid])

Don't expect this to run like greased lightning... (but it should
finish by February :-{) )

I agree that this is very strange. I have already discussed it with my
manager and this is what the clients signed off on. I am joining a
date&":"&text&"+"&memo and the result column is a text field. I am worred
that some of the memo field might be truncated.

The only way to ensure this is to have a prexisting Memo field and use
an Append query rather than a MakeTable query. Note that you CANNOT
join on a memo field (but you can join two tables on multiple fields,
so it should not be necessary to concatenate the data in the first
place... but yeah, it's in the specs...)

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Tom Ellison]

Dear Brenda:

If you use an aggregate function like FIRST() on a column you will
then get just one value for that column. Which one you get is
somewhat arbitrary. If that's good enough, do so, GROUPing on the
common key. You should then be able to JOIN to this query instead of
the whole table and get results without the duplication.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts