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

0
lmcc
6/1/2010 7:06:51 PM
access 16762 articles. 3 followers. Follow

3 Replies
537 Views

Similar Articles

[PageSpeed] 7

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?
> 
0
John
6/1/2010 7:40:20 PM
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

0
lmcc
6/1/2010 8:19:07 PM
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

0
lmcc
6/1/2010 9:59:20 PM
Reply:

Similar Artilces:

Business Portal Error-SQL server does not exist or access denied
Hi, We are running business portal 4.0 for one of our customer. It was running correctly, however, they have changed the SQL server port (previously it was set as default 1433). After that the business portal becomes very slow and while creating a new request (purchase requisition) if we open the item pop up; it is showing exception "SQL Server does not exist or access denied...." Can any body tell me how can I provide the new port number to business portal connection to the database. Thanks and Regards, Waliullah, Thanks for using the newsgroups. I have a...

What's happened to the findfirst function in Access 2010?
I have a working Access 2007 Application which is now failing miserably in 2010. It hinges on location the first available working document in a table runing the following VBA code: With Me.Recordset .FindFirst "([fld1] + [fld2]) = 0" If .NoMatch Then .FindLast "([fdl1] + [fld2]) <> 0" Exit Sub End If .... FURTHER PROCESSING ... What must I do to correct this? Go through a record by record search? End With In 2007, this works correctly, stopping at the appropriate record (approc. rec 1385 in the recordset...

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Access 2007 and Vista code problem
I am developing a database on a XP SP2 machine. On a form I have created some buttons with event procedures that run 2 queries and open a form. I sent the db to someone running Access 2007 on a Vista machine and nothing happens when she clicks the buttons and there are no messages. I know nothing about Vista so thanks in advance for any help. Tim Might be a reference problem, have them open the open a code window and click tools references. See what says missing and tell them what should be there, Add it and it should work. Duff "Tim Reid" <TimReid@discussions.micr...

Web query timeout setting.
Dear Group, I fill an Excel table using data that I take from an Internet site. Unfortunately, this site is very slow and so I often get a "query did not provide any data" error message. How can I increment the default web query timeout limit? Thanks in advance, Enrico. ...

Outlook 2007 Search always returns no matches
Any search I do in Outlook 2007's new search box returns no matches. I watched the Indexing Status as it progressed, and now it says "Outlook has finished indexing all of your items" But, searching on anything, even the word "The" or "a" returns no results. I'm searching all Outlook items. I'm on an Exchange Server via VPN. Anyone have any suggestions? I uninstalled Office and re-installed and the problem went away... "Tom" <none@none.com> wrote in message news:%23yGP6NPOHHA.2140@TK2MSFTNGP03.phx.gbl... > Any search I do i...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Publisher 2007 frequently crashes
Hello all, I'm having some difficulty with Publisher 2007. Whenever I try to open a particular file, the program crashes. I get no error message, except that Publisher needs to close. I've run Office Diagnostics (twice), with no results. I can open other Publisher files. The file that I am trying to open is an internal website for the company I work for. This file began acting strangely a couple of weeks ago - it wouldn't insert a duplicate page for a while, but I found a workaround, so I didn't worry about it. However, now I think that it may be a part of a larger probl...

Add PowerPoint 2007 to Office Basic
I have Office Basic installed on my laptop and need to add PowerPoint. What is the most recommended, easiest and least expensive way to do this? ...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Outlook 2007 Calendar -> Outlook 2003
In the past I could forward appointments and birthdays from one PC to another, if both PC's had Office 2003. I just tried forwarding a birthday from Outlook 2007 to Outlook 2003 and it doesn't work. I get the email in 2003. Open it, open the calendar item, then click on Save & Close. No error message. But it's not there. Neither in the current month or for next year. I forwarded it as a reoccurring item, that's the only way it will let you do that. Has anyone else been able to do this? Or, is this a backward compatibility issue? It's odd.... this works fine for...

compact database in VBA access 2007
Hello, I have recently upgrade to office 2007 from office 2003. To compact a database from within the database itself, I used the follwing code. Unfortunately it no longer works in access 2007. Is there some similar code that will work? Public Function FncCompactTheCurrentDB() CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction End Function Thank You, SL On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft....

Creating Text Box in Publisher 2007 Crashes the Application
Hello, we have a clean install of Publisher 2007 under Windows XP SP2, and when we try to create a text box in a document, (both an existing document and a blank document), publisher crashes. Office is fully patched. I ran Office Diagnostics from the help menu and no problems were found and the issue persists. Any thoughts on how to resolve the issue? Thanks, Syd See if selecting a different printer as default helps. How to view error signatures if an Office program experiences a serious error and quits http://support.microsoft.com/kb/289508/en-us -- Mary Sauer MSFT MVP http://of...

LDAP Write access?
My ldap server allows Write access to entries - and a few clients now support this. Any plugins available for Outlook to allow this too? Thanks None that I'm aware of. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Subscribe to Exchange Messaging Outlook newsletter: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Outlook Tips: http://www.outlook...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

MS Access 2007 code not running
I have created a brand new database in Access 2007 and linked some VB code to run on Form_Load. This code worked perfect with no problems the day I created it. Now today I opened the same form and it is not running the Form_Load code anymore (and yes it is still there, I did not delete it). I got frustrated so I copied and pasted the code to Form_Current and several On_Change events and set breakpoints at the beginning of all the events just to see if it was the Form_Load that was not triggering, but NO events are triggering my VB code. Any ideas? See this page: http://accessjunkie.com/fa...

unable to grant existing user access to TEST company
Set up test company using live company data; ran the script referenced in CustomerSource article ID#871973; can grant and remove user access in all other companies; ran grantsql 9.2; the dexsql log shows the user already has access probably because of using the other company data for the test; I can set up a new user and grant them access, existing users who already have access can view the company...but, I cannot figure out how to grant access to the TEST company to an existing user. The error popup reads "The user could not be added to one or more databases." Dynamics 9.0,...

MS Project 2007 question
Hello, I am developing a simple schedule with no resources but 2 tasks out of 100 are hazardous and I would like to make the schedule so that when these tasks occur, no other tasks can occur. In other words, even though all tasks are moving around as the schedule is being developed, I would like to make sure no other tasks can be scheduled wherever these 2 hazardous tasks fall. These 2 tasks are serial to every other task in the schedule. Is there a way to do this? Thanks for your help. Mike Create two milestones. Use milestone 1 as the successor task to all tasks th...

Office 2007 Templates & Themes
Is there anyway the content for Office 2007 at the MS Office web site could be made available to Office:Mac users. Currently need to have OGA validation via ActiveX control or from Office for Windows programs. Surely Mac users could be given access! Dave We have asked that question. We don't have an answer yet (at least, it wasn't "No", that would have been quick!). They are trying to work out how to let us in without letting in all the people using the competitor products. I think they will set up a mirror on the MacTopia site that will allow us to reach the ones tha...

Accessing another user's calendar OL2000/OL2003/Ex2000
Here's the situation: Running Exchange2000, v 6.0, sp4. Users are on XP/OL2003 or NT/OL2000. (we're in the process of upgrading all of the workstations to XP.) VIP user is on XP/OL2003. He can access his calendar just fine on his workstation. His secretary and other office admin type people are still on NT/OL2000. They have reviewer or editor rights to his calendar. They try to access his calendar by chosing File/Open/Other User's Folder... and put in his name and calendar. When they do this, the process hangs. In Task Manager, Outlook is "Running." End the Outloo...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...