Trying to Siplify a Query in MS Access 2003

I am a real Novice to MS Access 2003. I am trying to create a query to 
populate a form to be used as a subform in another form. I have created the 
form that use a table view of the a table. But would like to be able to use 
text boxes to view the data. I do not know if I should change the table 
designs that I am currently using. Here is what I have.

1. t_Contacts Table:

ContactTabID (Autonumber)
ContactID (Text value)
FirstName  (Text value)
LastName (Text value)
Birthdate (Date/Time value)

ContactTabID ContactID FirstName LastName Birthdate
001                 DonDuck         Donald         Duck
005                MicMouse        Minnie        Mouse
006                BugBunny        Bugs            Bunny

2. t_FamContacts Table:

T_FamilyID (Autonumber)
FamContactID (Text value)
FirstName (Text value)
LastName (Text value)
FamRelshp (Text value)
Birthdate (Date/Time value)

T_FamilyID FamContactID FirstName LastName FamRelshp Birthdate
001               DonDuck         Dewey         Duck         Chld1
002               DonDuck         Huey            Duck         Chld2
003               DonDuck         Lewey          Duck         Chld3
004                DonDuck         Daisey         Duck         Spouse
005              MicMouse        Minnie        Mouse       Spouse
006              BugBunny        Bugs Jr                          Chld1

FIELD: t_Contacts.ContactID = FIELD: t_FamContacts.FamContactID

I have individual Queries based on "FamRelshp" field. Adding up to 7 Queries 
(Spouse
+ 6 Children)

SELECT t_ContactsFamily.FamContactID, t_ContactsFamily.FirstName,
t_ContactsFamily.LastName, t_ContactsFamily.FamRelshp,
t_ContactsFamily.Birthdate
FROM t_ContactsFamily
WHERE (((t_ContactsFamily.FamRelshp)="spouse"));

ETC...

A combination Query with the 7 above Queries I can make a "Dependents" 
Query..

SELECT t_Contacts.ContactID, t_Contacts.FirstName AS ContactFN, 
t_Contacts.LastName AS ContactLN, t_Contacts.Birthdate AS ContactBirth, 
Q_FamilySpouse.FirstName AS SpouseFN, Q_FamilySpouse.LastName AS SpouseLN, 
Q_FamilySpouse.Birthdate AS SpouseBirth, Q_FamilyChld1.FirstName AS Chld1FN, 
Q_FamilyChld1.LastName AS Chld1LN, Q_FamilyChld1.Birthdate AS Chld1Birth, 
Q_FamilyChld2.FirstName AS Chld2FN, Q_FamilyChld2.LastName AS Chld2LN, 
Q_FamilyChld2.Birthdate AS Chld2Birth, Q_FamilyChld3.FirstName AS Chld3FN, 
Q_FamilyChld3.LastName AS Chld3LN, Q_FamilyChld3.Birthdate AS Chld3Birth, 
Q_FamilyChld4.FirstName AS Chld4FN, Q_FamilyChld4.LastName AS Chld4LN, 
Q_FamilyChld4.Birthdate AS Chld4Birth, Q_FamilyChld5.FirstName AS Chld5FN, 
Q_FamilyChld5.LastName AS Chld5LN, Q_FamilyChld5.Birthdate AS Chld5Birth, 
Q_FamilyChld6.FirstName AS Chld6FN, Q_FamilyChld6.LastName AS Chld6LN, 
Q_FamilyChld6.Birthdate AS Chld6Birth
FROM ((((((t_Contacts LEFT JOIN Q_FamilyChld1 ON t_Contacts.ContactID = 
Q_FamilyChld1.FamContactID) LEFT JOIN Q_FamilyChld2 ON t_Contacts.ContactID 
= Q_FamilyChld2.FamContactID) LEFT JOIN Q_FamilyChld3 ON 
t_Contacts.ContactID = Q_FamilyChld3.FamContactID) LEFT JOIN Q_FamilyChld4 
ON t_Contacts.ContactID = Q_FamilyChld4.FamContactID) LEFT JOIN 
Q_FamilyChld5 ON t_Contacts.ContactID = Q_FamilyChld5.FamContactID) LEFT 
JOIN Q_FamilyChld6 ON t_Contacts.ContactID = Q_FamilyChld6.FamContactID) 
LEFT JOIN Q_FamilySpouse ON t_Contacts.ContactID = 
Q_FamilySpouse.FamContactID
ORDER BY t_Contacts.LastName;

I was wondering if there was a way to simplify this, so that i would just 
need to do a single query with the "t_FamContacts Table" using DLookUps, 
with something like this in individual fields in the query:

SpouseFN: DLookup("FirstName", "t_ContactsFamily" , "famrelshp= 'spouse'" 
AND
"[t_ContactsFamily]![FamContactID]'=" & 'FamContactID  & "")

I have tried using:
(SQL View)
SELECT DISTINCT t_ContactsFamily.FamContactID, 
DLookUp("FirstName","t_ContactsFamily","famrelshp= 'spouse'" And 
"[t_ContactsFamily]![FamContactID]'=" & '[FamContactID]' & "") AS SpouseFN, 
DLookUp("FirstName","t_ContactsFamily","famrelshp= 'chld1'" And 
"[t_ContactsFamily]![FamContactID]'=" & '[FamContactID]' & "") AS Child1FN
FROM t_ContactsFamily;

But all the First Name Fields in the new query, are populated with the First 
Record's First Name value, in the "t_ContactsFamily" table.
FamContactID    SpouseFN    Child1FN
DonDuck             Dewey         Dewey
MicMouse            Dewey         Dewey
BugBunny            Dewey         Dewey

I hope I have explain it right, any Help would be appreciated.


-- 
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News)    <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>




0
Rich
5/25/2010 3:01:13 PM
access.tablesdbdesign 510 articles. 0 followers. Follow

1 Replies
956 Views

Similar Articles

[PageSpeed] 2

The 7 queries for the spouse and 6 children is the problem. If nothing else, 
there are families with more than 6 kids!

Instead of these 7 queries, you should try creating a single Dependents 
crosstab query and see if that meets your needs.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Rich/rerat" wrote:

> I am a real Novice to MS Access 2003. I am trying to create a query to 
> populate a form to be used as a subform in another form. I have created the 
> form that use a table view of the a table. But would like to be able to use 
> text boxes to view the data. I do not know if I should change the table 
> designs that I am currently using. Here is what I have.
> 
> 1. t_Contacts Table:
> 
> ContactTabID (Autonumber)
> ContactID (Text value)
> FirstName  (Text value)
> LastName (Text value)
> Birthdate (Date/Time value)
> 
> ContactTabID ContactID FirstName LastName Birthdate
> 001                 DonDuck         Donald         Duck
> 005                MicMouse        Minnie        Mouse
> 006                BugBunny        Bugs            Bunny
> 
> 2. t_FamContacts Table:
> 
> T_FamilyID (Autonumber)
> FamContactID (Text value)
> FirstName (Text value)
> LastName (Text value)
> FamRelshp (Text value)
> Birthdate (Date/Time value)
> 
> T_FamilyID FamContactID FirstName LastName FamRelshp Birthdate
> 001               DonDuck         Dewey         Duck         Chld1
> 002               DonDuck         Huey            Duck         Chld2
> 003               DonDuck         Lewey          Duck         Chld3
> 004                DonDuck         Daisey         Duck         Spouse
> 005              MicMouse        Minnie        Mouse       Spouse
> 006              BugBunny        Bugs Jr                          Chld1
> 
> FIELD: t_Contacts.ContactID = FIELD: t_FamContacts.FamContactID
> 
> I have individual Queries based on "FamRelshp" field. Adding up to 7 Queries 
> (Spouse
> + 6 Children)
> 
> SELECT t_ContactsFamily.FamContactID, t_ContactsFamily.FirstName,
> t_ContactsFamily.LastName, t_ContactsFamily.FamRelshp,
> t_ContactsFamily.Birthdate
> FROM t_ContactsFamily
> WHERE (((t_ContactsFamily.FamRelshp)="spouse"));
> 
> ETC...
> 
> A combination Query with the 7 above Queries I can make a "Dependents" 
> Query..
> 
> SELECT t_Contacts.ContactID, t_Contacts.FirstName AS ContactFN, 
> t_Contacts.LastName AS ContactLN, t_Contacts.Birthdate AS ContactBirth, 
> Q_FamilySpouse.FirstName AS SpouseFN, Q_FamilySpouse.LastName AS SpouseLN, 
> Q_FamilySpouse.Birthdate AS SpouseBirth, Q_FamilyChld1.FirstName AS Chld1FN, 
> Q_FamilyChld1.LastName AS Chld1LN, Q_FamilyChld1.Birthdate AS Chld1Birth, 
> Q_FamilyChld2.FirstName AS Chld2FN, Q_FamilyChld2.LastName AS Chld2LN, 
> Q_FamilyChld2.Birthdate AS Chld2Birth, Q_FamilyChld3.FirstName AS Chld3FN, 
> Q_FamilyChld3.LastName AS Chld3LN, Q_FamilyChld3.Birthdate AS Chld3Birth, 
> Q_FamilyChld4.FirstName AS Chld4FN, Q_FamilyChld4.LastName AS Chld4LN, 
> Q_FamilyChld4.Birthdate AS Chld4Birth, Q_FamilyChld5.FirstName AS Chld5FN, 
> Q_FamilyChld5.LastName AS Chld5LN, Q_FamilyChld5.Birthdate AS Chld5Birth, 
> Q_FamilyChld6.FirstName AS Chld6FN, Q_FamilyChld6.LastName AS Chld6LN, 
> Q_FamilyChld6.Birthdate AS Chld6Birth
> FROM ((((((t_Contacts LEFT JOIN Q_FamilyChld1 ON t_Contacts.ContactID = 
> Q_FamilyChld1.FamContactID) LEFT JOIN Q_FamilyChld2 ON t_Contacts.ContactID 
> = Q_FamilyChld2.FamContactID) LEFT JOIN Q_FamilyChld3 ON 
> t_Contacts.ContactID = Q_FamilyChld3.FamContactID) LEFT JOIN Q_FamilyChld4 
> ON t_Contacts.ContactID = Q_FamilyChld4.FamContactID) LEFT JOIN 
> Q_FamilyChld5 ON t_Contacts.ContactID = Q_FamilyChld5.FamContactID) LEFT 
> JOIN Q_FamilyChld6 ON t_Contacts.ContactID = Q_FamilyChld6.FamContactID) 
> LEFT JOIN Q_FamilySpouse ON t_Contacts.ContactID = 
> Q_FamilySpouse.FamContactID
> ORDER BY t_Contacts.LastName;
> 
> I was wondering if there was a way to simplify this, so that i would just 
> need to do a single query with the "t_FamContacts Table" using DLookUps, 
> with something like this in individual fields in the query:
> 
> SpouseFN: DLookup("FirstName", "t_ContactsFamily" , "famrelshp= 'spouse'" 
> AND
> "[t_ContactsFamily]![FamContactID]'=" & 'FamContactID  & "")
> 
> I have tried using:
> (SQL View)
> SELECT DISTINCT t_ContactsFamily.FamContactID, 
> DLookUp("FirstName","t_ContactsFamily","famrelshp= 'spouse'" And 
> "[t_ContactsFamily]![FamContactID]'=" & '[FamContactID]' & "") AS SpouseFN, 
> DLookUp("FirstName","t_ContactsFamily","famrelshp= 'chld1'" And 
> "[t_ContactsFamily]![FamContactID]'=" & '[FamContactID]' & "") AS Child1FN
> FROM t_ContactsFamily;
> 
> But all the First Name Fields in the new query, are populated with the First 
> Record's First Name value, in the "t_ContactsFamily" table.
> FamContactID    SpouseFN    Child1FN
> DonDuck             Dewey         Dewey
> MicMouse            Dewey         Dewey
> BugBunny            Dewey         Dewey
> 
> I hope I have explain it right, any Help would be appreciated.
> 
> 
> -- 
> Add MS to your News Reader: news://msnews.microsoft.com
> Rich/rerat
> (RRR News)    <message rule>
> <<Previous Text Snipped to Save Bandwidth When Appropriate>>
> 
> 
> 
> 
> .
> 
0
Utf
5/25/2010 4:09:01 PM
Reply:

Similar Artilces:

Problems printing in Publisher 2003 with HP PSC 2410 #2
I am new to publisher 2003 and am having great difficulties printing anything properly using an HP PSC 2410 all in one. As an example when trying to print a half page side folded pre-designed greetings card all that prints is page 1 and part of page 4, minus any text. Pages 2 and 3 are missing altogether. The print preview however displays everything correctly. I have reinstalled Office 2003 and updated the printer drivers to the latest versions but the problem remains. Any help is much appreciated. ...

Allow or Deny when a Program tries to send an email
I am trying to send a message through Outlook with a vb.net program. I receive a message saying that I can allow or deny this message. After a few of these messages, I started getting a different message that gave me a choice to ALWAYS allow messages to be sent from this program. I don't know what triggered the appearance of this send message. My problem is that I need to install my application on my user machines, does anyone know how to disable this allow or deny message OR trigger the send message which allows me select always allow? Thanks, What version of Out...

Cannot access by OWA
Hi there, we�ve two ex2k servers. First in domain 1 with internet connection. Second in subdomain, connected by a dedicated line. Both servers are in same org and site. When I try to access the second server by owa I can�t access the mailboxes there. That�s only possible on the first server (SSL security is enabled). Do I�ve missed to configure something? Thanks for any advice. JK Sorry, found it myself. Just have to configure the server as a frontendserver! "J. Kuenzel" <kuenzelj@yahoo.com> schrieb im Newsbeitrag news:OT3swrqLEHA.3696@TK2MSFTNGP09.phx.gbl... > Hi the...

InPlace ugrade from Exchange 2000 to 2003 recovery question
Hi, Will shortly be doing an inplace upgrade from Exchange 2000 enterprise edition to Exchange 2003 Enterprise edition. After we do all the steps ie. Forestprep/domainprep etc then do the actual upgrade if the upgrade fails how do we roll back. We use Brightstor Arcserve version 11 which is supported by Exchange 2000 and we backup doing the Full method not bricks level. I have documentation on how to restore Exchange using this but this just restores the database to my knowledge (never had to do it) with exchange 2000 so how do I roll back a failed upgrade. We have to do an inplace...

exchange 2003
I just set up a mail server at things are working fine in the network where it resides. The issue is that I have 5 other buildings that are connected via a VPN, and they can not connect. Do I need to change settings at their locations? DNS?? WINNS??? It depends on type of client, but generally proper DNS setup is required. Also make sure that no firewalls interfere. laker18 wrote: > I just set up a mail server at things are working fine in > the network where it resides. The issue is that I have 5 > other buildings that are connected via a VPN, and they > can not co...

Exchange 2003 SP1 Install Error
When I try to install E2003 SP1 I get the error: EventType exchangesetuperror, P1 6_5_7226_0, P2 update, P3 microsoft exchange messaging and collaboration services, P4 install, P5 e0070101, P6 _null_, P7 1, P8 NIL, P9 NIL, P10 NI The log says it cannot find a file but I can't tell which file it's looking for. Any ideas how to fix this? Thanks ...

Access attachments don't work anymore
Outlook 2000's security update makes it impossible to receive MS Access .mdb or .mde files. But I need to do this to earn a living as an Access developer! Other than have the sender rename the file there must be a simpler way to override the security patch. Outlook Express let's you turn off this feature but OL 2000 has no such setting. (The feature is basically worthless anyway - any hacker would know you just rename the file, put your hacker code in it and send it. Access will open ANY file, regardless of what you name it!!) See if the information on the following page help...

This query not giving correct results
I am trying to find the date when we had the most rainfall out of 3234 records, so with the first query to get the maximum rainfall in a month I get 110 records with one null and one '0' value. So this query is saying that out of 3234 records there has only been 108 days when we had rain. We probaly had more than that in one year never mind in 10 years. This cannot be right because we had 24 days of rain in November 2009 but the query only shows 16 for that month!. So how does it actually work? SQL for this below: SELECT Max(tblWXDataLeeds.ReadingDate) AS MaxOfReadin...

Trying to Get Subset of XML
I want to display a segment of an XML (see below) file in a datagrid. I want to select a specific <EventID> and get all of its children into a dataset so that I can bind a datagrid to those values. I am trying an expression as follows but I get an error "Value of type 'System.Xml.XmlNode' cannot be converted to 'System.Xml.XmlNodeList'." myNodes = xmldoc.ChildNodes("/Dataset/Events/[ShowName=Round 2]") What is the correct way to "get" that set of nodes and convert them to a dataset? ================== Portion of XML File ===============...

frustrations with Access 2006
is anyone else frustrated with the number of time, when editing a project..., Access 2006 crashes, leaving behind a corrupt, un-repairable file (or somtimes just a corrupt form, never-the-less: frustrating)? short of going back to an earlier version of the program, does any one have ANY suggestions? thanks in advance... mark "Mark Kubicki" <mark.kubicki@verizon.net> wrote in message news:u7Pv4lHDIHA.4880@TK2MSFTNGP03.phx.gbl... > is anyone else frustrated with the number of time, when editing a > project..., Access 2007 crashes, leaving behind a corrupt, un-re...

outlook 2003 add a dictionary
Hi, how can add a dictionary for spell checking ? in outlook 2003 su tools / options / spelling actually i have only English e French. ciao francesco ...

Outlook 2003 Newsreader
Hi, I've Outlook 2003 and I tried to use it to read newsgroups, using the customize in the menu bar and putting the option news on the go menu. It worked, but only the first time. The optino news now disapeared from the menu go, and from the customize. Where it go? I tried to repair and don't fix. Another thing is the Business Manager that I installed I think version beta, that crashes often, but I imagine that we can't have a solution on that without more info. Thanks Joao Bras To get the News command back, go back to the Programs tab in Control Panel | Internet Options and...

Do you need Access to use an Access Database for data entry only?
We have an Access database completed. Now we will use it for reading as well as for data entry. Do we need to install Access for every workstation needing the database for data entry or reading? No you don't necessarily need Access on every computer. You CAN use the Access Runtime and if it is in Access 2007 you can use it for free. If you are using another version then you would need to purchase the developer's edition to be able to legally distribute the associated Access runtime. If you do have 2007 and want to use the runtime, make sure your Full version of Access doe...

Access, average several fields in one row
I have several rows of data in a field, I need to average all the entries in one row I have 12 fields for 12 months of data, I need the average of the sum of all non blank entries. For example 3 months completed, the solution in Excel is (field1+field2+field3)/3 I am looking for method to average the sum in Access One way if you can't change your data is to use a VBA function. I've posted one below. You would call it in a calculated field in a query. Assuming your field names are the abbreviated month names the expression might look like the following. Field: fRow...

Access 2007 Reports
I'm using an Access 2000 mdb (split ends) in Access 2007. I cannot up- convert because other terminals don't have Office 2007. It works as a database just fine except: When I try to change a (front end) report from the default printer to another specific printer in design view, page setup, it all LOOKS good except it does not save my changes upon return and goes back to the default printer. I never had this problem with earlier versions of Access and the specific printer in questions works fine with other apps and terminals. Any ideas people? Many thanks. JM This is a known prob...

Is store procedure always fast than Access linked table via ODBC?
I was assigned to upgrade one program from Access(using ODBC to connect to SQL 2000) to ASP.NET(using store procedure in SQL 2000). Finally, I tested them and found that ASP.NET is slower than Access. The mojority job of program is select some data from SQL 2000 tables, modify and then insert into some tables. Is store procedure always fast than Access linked table via ODBC? -- Message posted via http://www.sqlmonster.com Stored procedures don’t add any overhead and they can save compile time. It's the code in the stored procedure and the underlying tables / indexes that ...

IE8 can't access Microsoft Office site when all other browsers can
When trying to access the Microsoft Office page using IE8 I keep getting the "Internet Explorer cannot display the webpage" message. If I use any other browser (Firefox, Opera, Safari, Chrome) I have no problem accessing the page. This happens whether running in normal or No Add-on's mode. Despite how many people keep complaining about all the problems with IE8, neither the MVP's or Microsoft acknowledge that they exist. Asus P5E Intel E8400 Core2Duo 3.0GHz 4 GB PC2-6400 DDR2 Windows 7 Ultimate (with all the latest updates installed) NIS 2010 (all up to date)...

Unable to open Access 95 mdb in Access 97
Hi I am trying to open an old Access 95 mdb in Access 97...I didn't create it but someone who knew the database way back when said that a system.mda file was created for that particular database because it had a lot of personal info in it. (I am not familiar with mda files...the database folder also had a system.ldb file there as well...I deleted that file...but am unsure why it wasn't closed out to begin with) I was able to open Access 97 and repair it...but when I go to open it in Access 97...I get an Automation Error...Cannot find VEENLR3.hlp file. Do you think it may ha...

Start Outlook 2003 at windows startup??
Hi, I have a simple question: how do I have Outlook 2003 started automatically and minimized to system tray at windows startup? Thanks in advance edomonet@libero.it <edomonet@libero.it> wrote: > how do I have Outlook 2003 started automatically and minimized to > system tray at windows startup? Put a shortcut to it in your Startup program group. However, I don't believe it will start minimized. -- Brian Tillman It's better to put a shortcut in your Quick Launch bar or you will get errors if OL loads too soon before your connection and antivirus. You can customiz...

Backup error Access Denied
I have been recieving the following error message for a while and can't figure out how to fix it. It is only happening on one mailbox. "Backup - \\MBMAIL\Microsoft Exchange Mailboxes Access denied to file Jhon Doe [jdoe]Top of Information StoreSync IssuesServer FailuresMail Delivery (failure jdoe@xyz.com)." Help Please Can you access the mailbox from a mail client? If so, you can use mdbvu to look at the Top of the Information store folder, and delete the message in there. If you cannot access the mailbox, then I have seen it where some users think they are ...

SBS 2003 migration to Server 2008 STD
Hi. We are at the limit of 75 Users and want to Migrate away from our old SBS 2003 Server to two new 2008 STD Server (one for Exchange 2010) I have looked for infos how to do that correctly but i could not find any good basis to start with. Someone already accomplished that task? I recommend Server 2008 R2 over 2008. If you use Server 2008 make sure you have SP2 or better installed before you try to install Exchange 2010. I have done it. It was fairly straightforward. That said it was not easy. There is a lot of conflicting information on the web. The basic procedure is...

Clarification please on exchange 2003 with DPM Server 2010
I would like to protect an exchange 2003 setup remotely. In the event of a problem such as accidently deleted mail etc. the way to restore the database files would is to use a recovery storage group? Now does this need to be restored back over the internet to the Exchange server or can the databases be restored onto a disk taken out to the server and then loaded into the recovery storage group? I am not sure what you mean "Now does this need to be restored back over the internet to the Exchange server" but yes to restore the exchange databases you can restore the files ...

An error occurred while trying to promote the e-mail. Try again
I have an email that I sent using Outlook (didn't use the CRM Email option). I now want to promote the email into CRM so that we have a permanent record of the email associated wioth the contact. I open my sent items, select the email and click the 'Promote E-Mail to CRM Activity' button in SFO. I get the error "An error occurred while trying to promote the e-mail. Try again" No matter how many times I 'try again' I get the same message. Can anyone tell me what I'm doing wrong and how I can get the email into CRM? Cheers Graeme SFO doesn't have any...

Unable to send a message, its been trying for 24hrs #2
I have been tring to send an email that contains a photo, for the past 24hrs. it wont send. all I am able to see is "cannot open this file, Outlook has already begun transmission. It wont even let me delete the message http://www.howto-outlook.com/faq/messagestuckinoutbox.htm=20 --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, tillybird asked: | I have been tring to send an email that contains a photo, for the | past ...

E-mail Tracking 2003
I know in Outlook 2003 that e-mail recipients have the option to not send "read receipts" - can this be disabled? A lot of my users in the past have relied on getting e-mail receipts and tracking their e-mails - they are very unhappy about the recipient having an option to decline their request for receipt in Outlook 2003. Jane Jane wrote: > I know in Outlook 2003 that e-mail recipients have the > option to not send "read receipts" - can this be > disabled? A lot of my users in the past have relied on > getting e-mail receipts and tracking their e-m...