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
|
|||
|
|||
Trying to combine multiple records into one record in a Query
I originally cross-posted this question in microsoft.public.access.queries &
microsoft.public.access.tablesdbdesign newsgroups, but did not get a response. I am new to Access, and very little knowledge about SQL. I read allot about normalization, need to know if I went to far with it with my Family table. Here is the structures for the tables I have, and of the query I want to create. Contact Table: tabContactID (Autonumber) ContactID (Create using VBA in a form) (PK) FirstName LastName etc.... Example: tabContactID ContactID FirstName LastName etc.... 0001 DonDuck Donald Duck 0002 MicMouse Mickey Mouse Next the Family Table: tabFamID (Autonumber)(PK) ContactID (FK from Contact Table) FirstName LastName FamRelshp Birthdate Example: tabFamID ContactID FirstName LastName FamRelshp Birthdate 0001 DonDuck Daisey Duck Spouse 0002 DonDuck Huey Duck Chld1 0003 DonDuck Dewey Duck Chld2 0004 DonDuck Lewey Duck Chld3 0005 MickMouse Minnie Mouse Spouse I would like to be able to create a Query, creating a single record for each family, using the multiple records from the Family Table, based on the same field, ContactID. A Query with each record as follows: ContactID SpouseFN SpouseLN SpouseBirth Chld1FN Chld1LN Chld1Birth etc... Or should I just set up the Family table like the above query. -- Add MS to your News Reader: news://msnews.microsoft.com Rich/rerat (RRR News) message rule Previous Text Snipped to Save Bandwidth When Appropriate |
#2
|
|||
|
|||
Trying to combine multiple records into one record in a Query
Based on your data, I would create a table tblFields4Xtab with a single text
field [FieldName] and three records with values FName, LName, DOB. Then create a crosstab with this SQL: TRANSFORM First(IIf([FieldName]="FName",[Family].[FirstName],IIf([FieldName]="LName",[Family].[LastName],[Family].[BirthDate]))) AS Expr1 SELECT Family.ContactID FROM tblFields4Xtab, Family INNER JOIN Contact ON Family.ContactID = Contact.ContactID GROUP BY Family.ContactID PIVOT [FamRelshp] & [FieldName]; -- Duane Hookom MS Access MVP "Rich/rerat" wrote in message ... I originally cross-posted this question in microsoft.public.access.queries & microsoft.public.access.tablesdbdesign newsgroups, but did not get a response. I am new to Access, and very little knowledge about SQL. I read allot about normalization, need to know if I went to far with it with my Family table. Here is the structures for the tables I have, and of the query I want to create. Contact Table: tabContactID (Autonumber) ContactID (Create using VBA in a form) (PK) FirstName LastName etc.... Example: tabContactID ContactID FirstName LastName etc.... 0001 DonDuck Donald Duck 0002 MicMouse Mickey Mouse Next the Family Table: tabFamID (Autonumber)(PK) ContactID (FK from Contact Table) FirstName LastName FamRelshp Birthdate Example: tabFamID ContactID FirstName LastName FamRelshp Birthdate 0001 DonDuck Daisey Duck Spouse 0002 DonDuck Huey Duck Chld1 0003 DonDuck Dewey Duck Chld2 0004 DonDuck Lewey Duck Chld3 0005 MickMouse Minnie Mouse Spouse I would like to be able to create a Query, creating a single record for each family, using the multiple records from the Family Table, based on the same field, ContactID. A Query with each record as follows: ContactID SpouseFN SpouseLN SpouseBirth Chld1FN Chld1LN Chld1Birth etc... Or should I just set up the Family table like the above query. -- Add MS to your News Reader: news://msnews.microsoft.com Rich/rerat (RRR News) message rule Previous Text Snipped to Save Bandwidth When Appropriate |
Thread Tools | |
Display Modes | |
|
|