Trying to Siplify a Query in MS Access 2003
in [Tables]
|
From: Rich/rerat on 25 May 2010 11:01 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>>
From: Jerry Whittle on 25 May 2010 12:09 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>> > > > > > . >
|
Pages: 1 Prev: How to add record in Subform which is in another Subform Next: how to set a certain format |