Query criteria help?? (1 Viewer)

Hey guys,

I am trying to figure out how to write this query.:banghead:

The data has a list of numbers and an address. One for business and one for Home. What I need is the business address....which would be easy but there are some that just have a home address.

So what I am trying to write is this:
Give all the numbers with business address
(here is where I am having trouble)
If it only has a home address show that.

example

Number | field1 | Address | City | State | Zip | Type |
---------------------------------------------------------------------
12345 | blah | 12 blah St | ORL | FL | 32804 | BSNSS |
12345 | blah | 58 floor St | ORL | FL | 32819 | HOME |
15648 | blah | 15 Red St | ORL | FL | 32804 | HOME |

So in this example I would return the first line and the 3rd line because the 1st one is a BSNSS and the 3rd only has a Home address

I have tried all kinds of stuff and have not gotten it yet

any ideas?

Thanks

make 2 queries:
1 for Bus addr
1 for home addr

then you can pull what you need in query or in forms.

Try this

Good luck with your project.

jdraw said:

Try this

Code:Copy to clipboard

SELECT Addr.Number
	,Addr.field1
	,Addr.Address
	,Addr.City
	,Addr.STATE
	,Addr.Zip
	,Addr.Type
FROM Addr
WHERE (((Addr.Type) = "Bsnss"))

UNION

SELECT Addr.Number
	,Addr.field1
	,Addr.Address
	,Addr.City
	,Addr.STATE
	,Addr.Zip
	,Addr.Type
FROM Addr
WHERE addr.number NOT IN (
		SELECT Addr.Number
		FROM Addr
		WHERE (((Addr.Type) = "Bsnss"))
		)

Good luck with your project.

Click to expand...

Wow...this worked great!!!!

It took a while to run because there was alot of data. This is a keeper template for sure...At least for me!

Your awesome chief!!

Thanks again!!

Happy to help.
Good luck with your project.