Retrieving the most current record

[Les]

I have an address table, indexed by address_id. Each user can have mulitple
addresses.

Assume "john doe" has address_id's 123, 456, and 789. "789" being the most
current. In addition "123" and "456".

When I do a query for John Doe, it pulls all 3 addresses. How do I pull
just the most current one, being "789"?

Thanks

 

[Jerry Whittle]

How do you know that 789 is the most current? Are you using autonumbers for
the address_id field? If so there are a couple of problems.

1. Autonumbers don't always increment sequentially. In theory there's not
much to stop them from creating even a negative number.

2. What do you do if someone moves back into a previous address? I guess if
you assign them a new, larger address_id that would work.

Your best bet would be using a date/time field. If you don't have that,
something similar to this could work.

SELECT TOP 1 *
FROM Address
WHERE [Names] = [Enter Name]
ORDER BY Address_id DESC);

Something like this should work. A lot depends on how you query for John Doe.

 

[Les]

Thanks for your help, but I don't know how to apply your query. This is what
I have created and used in Excel, except it's pulling multiple shipping
address.

SELECT Buyers.Userid, Buyers.Email, Addresses.FirstName, Addresses.Initial,
Addresses.LastName, Addresses.Company, Addresses.AddressLine1,
Addresses.AddressLine2, Addresses.City, Addresses.State, Addresses.Zip,
Addresses.Country, Listings.ItemID, Sales.QtySold, Sales.ShippingCharged,
Sales.SalesID, Sales.StatusID, Sales.DatePaymentCleared,
Sales.ShippingAddressID
FROM `\\Netcenter\BTData`.Addresses Addresses, `\\Netcenter\BTData`.Buyers
Buyers, `\\Netcenter\BTData`.Listings Listings, `\\Netcenter\BTData`.Sales
Sales
WHERE Addresses.BuyerID = Buyers.BuyerID AND Buyers.BuyerID = Sales.BuyerID
AND Listings.ListingID = Sales.ListingID AND ((Sales.StatusID=15))
ORDER BY Sales.DatePaymentCleared, Buyers.Userid

 

[Jerry Whittle]

See if this works. No guarantees!

SELECT Buyers.Userid,
Buyers.Email,
Address.FirstName,
Address.Initial,
Address.LastName,
Address.Company,
Address.AddressLine1,
Address.AddressLine2,
Address.City,
Address.State,
Address.Zip,
Address.Country,
Listings.ItemID,
Sales.QtySold,
Sales.ShippingCharged,
Sales.SalesID,
Sales.StatusID,
Sales.DatePaymentCleared,
Sales.ShippingAddressID
FROM (SELECT TOP 1 *
FROM '\\Netcenter\BTData'.Addresses
WHERE Addresses.BuyerID = Buyers.BuyerID
ORDER BY Address_id DESC) As Address,
'\\Netcenter\BTData'.Buyers Buyers,
'\\Netcenter\BTData'.Listings Listings,
'\\Netcenter\BTData'.Sales Sales
WHERE Addresses.BuyerID = Buyers.BuyerID
and Buyers.BuyerID = Sales.BuyerID
and Listings.ListingID = Sales.ListingID
and ((Sales.StatusID=15))
ORDER BY Sales.DatePaymentCleared,
Buyers.Userid ;