If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query--using Access 2007
I have a tblAddress table with a lookup field called TypeofAddressID.
A company may have many addresses with different TypeofAddressID—such as 1 = Business, 2 = Mailstop, 3 = PO Box, and so on. I need to pull out TypeofAddressID # 1. Then if a 1 is not available, give me the type that is (which may be the PO address or Mailstop). I tried DLookup, Xor, and IIf([TypeofAddressID]=1, 1, IIf([TypeofAddressID]=2, 2, IIf([TypeofAddressID]=3, 3, 4))), but it is listing all addresses per company instead of either or. Any suggestions? -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Query--using Access 2007
One method.
SELECT tblCompanies.*, tblAddress FROM tblCompanies INNER JOIN tblAddress ON tblCompanies.CompanyID = tblAddress.CompanyID WHERE tblAddress.TypeOfAddressID = (SELECT Min(Temp.TypeOfAddressID) FROM tblAddress as TEMP WHERE Temp.CompanyID = tblAddress.CompanyID) If that is too slow then post back for an alternative query. When you do tell us the names of the key fields and the names of the tables involved. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County lmcc via AccessMonster.com wrote: I have a tblAddress table with a lookup field called TypeofAddressID. A company may have many addresses with different TypeofAddressID—such as 1 = Business, 2 = Mailstop, 3 = PO Box, and so on. I need to pull out TypeofAddressID # 1. Then if a 1 is not available, give me the type that is (which may be the PO address or Mailstop). I tried DLookup, Xor, and IIf([TypeofAddressID]=1, 1, IIf([TypeofAddressID]=2, 2, IIf([TypeofAddressID]=3, 3, 4))), but it is listing all addresses per company instead of either or. Any suggestions? |
#3
|
|||
|
|||
Query--using Access 2007
Thanks John,
I ran Database Documenter, so here are the tables and fields: Table: Address AddressID CompanyID AddressAttention Text AddressStreetNumber AddressPreDirection_Lkp StreetID StreetTypeID AddressPostDirection_Lkp DesignationTypeID AddressDesignation AddressBoxType_Lkp AddressBoxNumber AddressCity AddressCounty AddressState AddressPostalCode CountryID TypeofAddressID AddressAssignedTo AddressAssignedToName AddressInactive Table: Company CompanyID CompanyDateEntered CompanyName CompanyAlsoKnownAs CompanyFormerlyKnownAs CompanyEmployerIdentificationNumber DepartmentID CompanyEmail CompanyWebsite Table: tlkpTypeofAddress TypeofAddressID TypeofAddressName All the schooling I went to teach that the address info is added in the company table, but in reality many companies have more than one address. Even the many many books I have read all put address data with the company table--you know, Address1, Address2, and so on. I did the address like this to handle the various addresses; also, to help with data entry and spelling. So far in Excel there are about 3,000 records I will be exporting to Access. -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Query--using Access 2007
Hey John,
I think I finally got your code to work. Below is what I created in a new query and it seems to have done the job: SELECT Address.* FROM Company INNER JOIN Address ON Company.CompanyID = Address.CompanyID WHERE (((Address.TypeOfAddressID)=(SELECT Min(Temp.TypeOfAddressID) FROM Address as TEMP WHERE Temp.CompanyID = Address.CompanyID))); When I took a break and came back, then the code became clear of what you were saying. Thanks!!! John Spencer wrote: One method. SELECT tblCompanies.*, tblAddress FROM tblCompanies INNER JOIN tblAddress ON tblCompanies.CompanyID = tblAddress.CompanyID WHERE tblAddress.TypeOfAddressID = (SELECT Min(Temp.TypeOfAddressID) FROM tblAddress as TEMP WHERE Temp.CompanyID = tblAddress.CompanyID) If that is too slow then post back for an alternative query. When you do tell us the names of the key fields and the names of the tables involved. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County I have a tblAddress table with a lookup field called TypeofAddressID. [quoted text clipped - 9 lines] Any suggestions? -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|