Trying to Siplify a Query in MS Access 2003

  • Thread starter Thread starter Rich/rerat
  • Start date Start date
R

Rich/rerat

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.
 
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 said:
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>>




.
Click to expand...
 
Back
Top