Solved Query with conditional criteria (1 Viewer)

[Juett]

Hi everyone,

I'm trying to make a query off of two tables that is connected/used with another database I don't directly manage.

There is a field called ADDRESS_TYPE in the Delivery Address table that has two values as data options: 'Delivery' and/or 'Contact Address'. The Delivery Address table is linked to the Customer Table. One customer can have more than one entry in the Delivery Address table. It's a one-to-many relationship.

Here's the issue......I'm trying to create a query that lists each customer once, with the primary chosen ADDRESS_TYPE entry (which is usually Delivery)....but the problem I'm facing is that there are some instances where there are two entries for the customer with both a 'Delivery' and 'Contact Address' entry.

What I'd like to achieve is a query that can do the following:

If [ADDRESS_TYPE]="Delivery" then use this and nothing else. If there is no "Delivery" but [ADDRESS_TYPE]="Contact Address" then use this instead. But If there is both "Delivery" and "Contact Address" then only use "Delivery".

So far the flowing SQL is the closest I can get to identifying each value, but it of course returns multiple results for the same company where that company has both values.

IIf([ADDRESS_TYPE]= "Delivery","Delivery",IIf([ADDRESS_TYPE]="Registered Address","Registered Address", ""))

Am I asking for too much here?!

 

[plog]

Not asking too much, but its going to take another approach to solve. You need another query to determine how to treat each contact. I don't know how familiar you are with SQL so I am just going to give the broad strokes at first, if you need more specific SQL help just let me know.

You need a subquery that uses the Address table to find the maximum value for each customer's ADDRESS_TYPE field (Let's call this field MAX_ADDR_TYPE). From a min/max perspective, 'Delivery' is larger than 'Contact' so if a customer has a Delivery record that will be the value, if they only have a Contact record that will be the value.

You then use that query in the query you have linking it to the Address table. You link them CUSTID to CUSTID and MAX_ADDR_TYPE to AddressType. Then you need no logic field, the linked query does the criteria for you.

 

[Minty]

You need to break this down into a couple of steps. This can be done with sub queries but it's easier to visualise if you do this in stages.
Firstly create a query that only pulls in the Customer_ID and where the Delivery address is not null. Think of this a Query1
Now create a second query that pulls in only the Customer_ID and Registered Addresses, but join it to the first query on the Customer_ID where the Customer_ID in Query1 is null.

You should now have two queries of the unique addresses.
You could union them or use the structure to create a single more complex query

 

[tvanstiphout]

I think this requires a union query:

select * from DeliveryAddress where AddressType = 'Delivery'
union
select da1.* from DeliveryAddress da1 where da1.AddressType = 'ContactAddress' and not exists (select da2.* from DeliveryAddress da2 where da2.AddressType = 'Delivery' and da2.CompanyID = da1.CompanyID)

 

[Josef P.]

without union:

select
    *
from
    DeliveryAddress  As DA
where
     DA.AddressType = 'Delivery'
     or
     DA.AddressType = 'ContactAddress'
         and not exists (select da2.* from DeliveryAddress da2 where da2.AddressType = 'Delivery' and da2.CompanyID = DA.CompanyID)

 

[Juett]

I've had some limited success with this. The following union query works if I first copy the source data from the Delivery Address table into two local access tables (table1 and table2) using append queries.

SELECT * from Table1 where AddressType = 'Delivery'

UNION SELECT da1.* from Table2 da1 where da1.AddressType = 'Registered Address' and not exists (select da2.* from Table1 da2 where da2.AddressType = 'Delivery' and da2.OrderNom = da1.OrderNom);

However, despite managing to create individual select queries that work on the original source table, when I then try to use the above code to union those queries, I get the odbc--call failed. Invlaid column specification (#0) error.

I'd rather not have to copy out the source data into two tables and then union that. It would better to union the two select queries, but for some reason, Access wont have it. Might it be because the source table is an external, linked SAGE table?

 

[Minty]

Is it Sage 50 by any chance?
If it, is save yourself a lot of pain and import the data, the connections to Sage 50 are painfully slow, and dreadful to work with in a live situation.

Otherwise, I don't know why you can't union two queries if the queries work by themselves independently?

 

[Juett]

It is sage 50 and I think you're right there! I'll import the data! Thanks to all.