[RESOLVED] [Access SQL] Pull only 1 record per account from Customer.

How would I create a query, that pulls only 1 customer name from the customer table per account?

Tables

AccountTable
AccountNumber (PK)
Address
OtherInfo, etc...

CustomerTable
AccountNumber
FullName

If I do a left, inner, or right join, it will still get the incorrect number of records.

For instance.

Account table has 2 accounts.

1, 123 main street
2, 443 apple ave

Customer has 3 names

1, John
1, Jane
2, Bob

The returned results would be

1, 123 main street, John
1, 123 main street, Jane
2, 443 apple ave, Bob

How do I make it pick only 1 customer name, so that I am not duplicating address information?

Use a subquery

Usually you would do that with a group by or a sub query as Tiom suggests. A group by is better but slightly less flexible. The first thing you need to decide though, is how you're going to decide which name to pull.

For example:-

Code:

---

Select AccountNumber, max(FullName)
From AccountTable
Inner Join CustomerTable
 on AccountTable.AccountNumber = CustomerTable.AccountNumber
Group By AccountNumber

---

Would return you 1, John because John is 'higher' than Jane (lucky John). Changing the Max to a Min would have returned Jane instead.

If a max or a min is enough to decide which name to get then a group by is your best bet. If it's more complicated you're probably better off with a sub query:-

Code:

---

Select AccountNumber, (Select FullName
                                From CustomerTable
                                Where CustomerTable.AccountID = AccountTable.AccountID
                                And enough other criteria to identify a unique name)
From AccountTable

---

Nice, so If I wanted to get just the newest fullname, I could add an import date, or update data to the customer table.

Then do the sub query like both of you suggested with the restriction of TOP 1, and do an ORDER BY ImportDate.

Code:

---

Select AccountNumber, (Select TOP 1 FullName
                                From CustomerTable
                                Where CustomerTable.AccountID = AccountTable.AccountID
                                ORDER BY CustomerTable.ImportDate)
From AccountTable

---

Or would I need to code it different to get the newest entered one?

That's spot on. Basically, you can define the order in any way you like as long as there are fields to support it. So you could pick the wealthiest customer, the tallest, the last added or the most likely to go on a killing spree. As long as you've got the fields to support it you just drop in the apropriate order by and select the top 1.

Right on! It works

Thank you guys =)