Trying to create a mailing list from one table

I have a table that has these fields in it:

Closing Date (date field)

Primary First Name (short text)
Primary Last Name (short text)
(these 2 fields will be combined in the query)

Mailing Street Number (short text)
Mailing Street Direction (short text)
Mailing Street Name (short text)
Mailing Street Type (short text)
(these 4 fields will be combined in the query)

Mailing City (short text)
Mailing State (short text)
Mailing Zip Code (short text)
(these 3 fields will be combined in the query)

There are also two additional fields:

Secondary First Name (short text)
Secondary Last Name (short text)
Type (always either "Buyer" or "Seller"

In every case if the primary name fields (combined) is in more than one record then I only want the one with the most recent Closing Date.

I can develop this query with no problem in query designer.

But here is the catch.  If the record has a Secondary Last Name that is different than the Primary Last Name then we want to assume that this name (combined) should also be included in the final query.  If the last name in Secondary Last Name is the same as the Primary Last Name then we don't want that name included in the query.

How can I do this?

Oh, one more criteria...  if we get two records that are identical then we want to use the one with "Buyer" and not the one with "Seller" in the type field.

--Steve
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveL13Author Commented:
Here is where I'm at so far.  I have in my exported table for example:

John Doe     11622 North West Drive     Somewhere, IL 61107     Buyer
John Doe     11622 North West Drive     Somewhere, IL 61107     Seller

I only want the one with the "Buyer" to exist.  How can I remove the one that reads "Seller" ?
Jeffrey CoachmanMIS LiasonCommented:
Not sure i fully understand, nor do I know the query field names you have there ...

But a query based on your data above might look something like this:
SELECT, FullName, Address, CityStateZip,  Status
WHERE Status="Buyer"
...or
WHERE Status<> "Seller"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eric ShermanAccountant/DeveloperCommented:
Probably will need multiple queries and or a VBA function ... would need to see your db to get it correct.  
I would detail out the specifics for each criteria you mention above and evaluate what's the best method to start excluding records.  Looks easier than you what you have described thus far.

>>>>> if we get two records that are identical then we want to use the one with "Buyer" and not the one with "Seller" in the type field.<<<<<

The records based on the fields are not identical.

ET
jjnet123Commented:
You could probably achieve some of this using totals, and Max/Min.

For example specifying Min on the 'Status' field would bring back the lowest alphanumeric (being 'Buyer') but would bring back Seller if there were no Buyer.

As mentioned above, this is probably only part of what might be a bit of a monster.
Jeffrey CoachmanMIS LiasonCommented:
ok
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.