Trying to combine multiple records into one record in a Query

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 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>>



0
Rich
4/13/2010 4:01:41 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1146 Views

Similar Articles

[PageSpeed] 38

Rich/rerat wrote:
>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. 

No, you did not go too far.

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

That tabContactID field is not serving any purpose and
should be deleted.  Just make sure you can differentiate two
ContactIDs for different folks with similar names (eg.
Michel Mouse, unrelated to Mickey).

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

No, leave the tables as is.

Take a step back and rethink why you want to have such a
query.  You can easily display the family data using a
subform or subreport without using that kind of query so I
don't see any reason for what you think you want to do.

-- 
Marsh
MVP [MS Access]
0
Marshall
4/13/2010 5:21:49 PM
Reply:

Similar Artilces:

trying this out
This is a test posting. On Tue, 13 Nov 2007 16:06:51 +0800, "hmmmm" <bobo123@dotdot.dyndns.org> wrote: >This is a test posting. > Please use microsoft.public.test or microsoft.public.test.here for your testing. This is a working newsgroup. If you have questions about an Access database, please post them. John W. Vinson [MVP] ...

Need to compare 2 tables and only pull certain records from one ta
I have a monthly sales file that contains several invoices that allows duplicate records of parts. My second table is a different sales file that also contains several invoices and allows duplicate records. I want to match from the second table as a basis. How do I pull matching invoices from the two tables without pulling all the data from the first monthly sales file? UNTESTED UNTESTED SELECT SecondTable.* FROM SecondTable INNER JOIN FirstTable ON SecondTable[Invoices] = (SELECT FirstTable.[Invoices] FROM FirstTable GROUP BY FirstTable.[Invoices]) ORDER BY SecondT...

Recorded macros not working using the shortcut keys
Hello, I have recorded two macros that work fine except they do not run using the shortcut keys I defined. I've stepped through the macros and the shortcut keys are in the scripts, but I can only run them by pulling up the list of macros and selecting the macro and clicking run. I'm using Office 2007 in XP. TIA, Robert When you pull up the list of Macros and select your macro, go to the options box and create a shortcut key. On Fri, 13 Feb 2009 06:11:40 -0800 (PST), CurlyDave <davesexcel@gmail.com> wrote: >When you pull up the list of Macros and select your macro, go to...

I am trying to round up to the nearest 25 cents
I am trying to round a money value to the nearsest 25 cents and it keeps rounding to the nearest dollar HELP Try =CEILING(A1, 0.25) In article <2DB8F007-1E27-4A48-923B-C7D4D0238B5E@microsoft.com>, sypher <sypher@discussions.microsoft.com> wrote: > I am trying to round a money value to the nearsest 25 cents and it keeps > rounding to the nearest dollar HELP =ROUND(A1*4,0)/4 -- HTH Bob Phillips "sypher" <sypher@discussions.microsoft.com> wrote in message news:2DB8F007-1E27-4A48-923B-C7D4D0238B5E@microsoft.com... > I am trying to round a mone...

Trying to clear up Received but not invoiced report
I am trying to clean up the received not invoiced items. The problem began with entering a purchase order and then not matching the invoice to the items received agaisnt the purchase order. SO we thought that doing a returned transaction entery would solve the problem. The only thing is now the return shows up on our Received/ Not invoiced report. And the report is becoming a huge mess and difficult to use. What is the best way to clear up items that have been received but were not matched and already paid through the invoice entery? There is a knowledgebase article on this very thing av...

send/receive (2nd try)
New computer. Microsoft XP Home. Microsoft Office XP Small Business 2002. Earthlink dial-up account. Two e- mail accounts. After opening Outlook, 1st send/receive (option 3, all accounts) usually prompts for both passwords, downloads messages, all good. Next time, though, no prompt for account 2, error cannot connect to server, account 1 usually gets messages. Every five minute send/receive option eventually degenerates to cannot connect to server errors for both accounts. Meanwhile internet browsing slows to a crawl. Any ideas? ...

How do I cancel a msg Outlook keeps trying to send unsuccessfully?
How do I cancel an "in process" email Outlook keeps trying to send unsuccessfully due to a huge email message I tried to send with way too many attachments? It's stuck in "send" mode, and as a result, I can't send any emails because Outlook cannot get past trying to send this message. How do I cancel a pending outbox transmission while it's trying to send? I've tried everything I can think of to no avail. Please help! Angelczech wrote on Fri, 12 March 2010 07:32 > How do I cancel an "in process" email Outlook keeps trying to send ...

Change multiple users e-mail address at one go
Does any one know any script/tool/command in E2k3 on win2k3 where in i can change multiple users primary e-mails address at one go. -- Manoj " Jack of all trades, master of the moment" Have a look at admodify. http://www.gotdotnet.com/workspaces/workspace.aspx?id=f5cbbfa9-e46b-4a7a-8ed8-3e44523f32e2 -- Ben Winzenz Exchange MVP MessageOne Read my blog! http://winzenz.blogspot.com http://feeds.feedburner.com/winzenz (RSS Feed) "manoj" <manoj@discussions.microsoft.com> wrote in message news:79D66A7A-B875-45DF-8888-E54D92CD84B2@microsoft.com... > Does an...

trying to insert a 0 in front of zipcode
Hello, I do not use excel at all so i have no clue! I have a list of daycares that i want to create mailing labels with...about 700 of them but the problem is my zipcode column there needs to be a zero in front of all 700 zipcodes how to i insert a zero in front of them without having to type it into each box? the other question is can i print labels directly from excel or do i have to go thru ms word? thanks Greg One of these 1) format the cells with custom format 00000 2) format the cells with Special / Zip Codes 3) format the cells as Text 4) begin all zip codes with an sin...

CRM Report shows only 10 records
Hi Guys I am trying to show more than 10 records from a report, some reports shows me nothing and others only 10 records How can I fix that? -- Luis Quiroz ...

Select TOP 4 Records For Each Group
I have a query that I need to select the TOP 4 records for each distintive group. The layout: 2 Columns of data(Charge and ICV). Charge is an integer and ICV is a single. I need to get the TOP 4 ICVs for each charge. How do I set Grouping and/or Summing to do this? Thank You See: Subquery basics: TOP n records per group at: http://allenbrowne.com/subquery-01.html#TopN -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Tirelle" <Tirelle@disc...

error message when trying to install WMP 10 or 11
My operating system is Windows XP Home Edition 2002. I'm trying to download either WMP 10 or 11 and I get a message saying it was not possible to complete setup, see webhelp for more assistance, however there is no additional information when I click Web help. the Error message is 8007F0DA. Can anyone help me? Thanks. On Tue, 30 Mar 2010 07:54:01 -0700, Nate <Nate@discussions.microsoft.com> wrote: > >My operating system is Windows XP Home Edition 2002. I'm trying to download >either WMP 10 or 11 and I get a message saying it was not possible to &g...

One driver for multiple OS
Is it possible to use one build environment to create one bulk USB driver binary to run on WinXP, Vista and Windows 7 using WDK 7600.16385.0? Or do I need 3 driver binaries per platform (eg x86, amd64)? -- Thanks, Eric You can do it with one driver, just use the build environment of the oldest platform that you want to target (XP in this case). See http://msdn.microsoft.com/en-us/library/aa906294.aspx for details on how to dynamically take advantage of the features available on later platforms (if you need or want to, that is). -scott -- Scott Noone Consulting ...

oledb query problem
Hello. I have a connection with a Access 2007 db back end (tables only) using OLEDB. The db is on a shared drive on the network. So a UNC is used. A simple query works but a query in which other tables are included just never works. Here is the query I'm trying to use; SELECT tblChangeRequest.ChangeRequestID, tblChangeRequest.ChangeRequest, tblChangeRequest.DevCompletionDate, tblProjectLead.ProjectLead, tblSPGName.SPGName, tblChangeRequest.QuickHit, tblChangeRequest.BugFix FROM (((tblChangeRequest LEFT JOIN tblProjectLead ON tblChangeRequest.Project...

Try or TRY
Hello, Will there be any opitmization or ... when we use INT or TRY macros instead of int or try ? Which one is better to be used? Thanks, JSmith "JSmith" <jsmithmitra@yahoo.com> wrote in message news:%23G1sOVdoEHA.692@TK2MSFTNGP12.phx.gbl... > > Hello, > > Will there be any opitmization or ... when we use INT or TRY macros instead > of int or try ? Which one is better to be used? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore/html/_core_exceptions.3a_.converting_from_mfc_exception_macros.asp -- Jeff Partch [VC++ MVP] FYI, TRY ...

Problem with one e-mail message
In my inbox, a message is shown in red with the "blocked" symbol. But when I click it to delete, it crashes Windows Mail instead. I ran my full antivirus scan (Trend Micro) but still have the problem. Windows Update no help. Any suggestions short of dumping Windows Mail with all my mail, contacts, etc.?? Upgrade to Windows Live Mail: http://download.live.com/wlmail It will automatically import all your Windows Mail data. Gary VanderMolen, Microsoft MVP (Mail) "Ludlow37" wrote in message news:29D1C42E-D4FB-4748-9770-BF2F7F51B685@microsoft.com... In...

error message when trying to delete messages in inbox
I cannot delete messages in my email inbox. When attempting to delete messages, Outlook says "The messaging interface has returned an unknown error. If the problem persists, restart Outlook. I continue to get the message even though I restarted Outlook. My mail works fine, but I just cannot delete anything. Does someone know a solution? Can you delete them if you hold shift as you delete? How many items are in the deleted items folder? Are you using any type of AV scanner on the message store? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coa...

Can you have 2 date ranges in one row on an Excel Gantt chart?
Is it possible to do a gantt chart in excel with the following criteria: Bus Start Date Duration End Date Start Date Duration End Date L180 9/1/2007 5 9/6/2007 9/15/2007 6 9/21/07 L220 9/15/2007 4 9/19/2007 9/25/2007 4 9/29/07 I am able to get the first set of start and end dates to chart, but not the 2nd series. Any help would be appreciated. I've been trying this for months. Karen On Fri, 7 Dec 2007, in microsoft.public.excel.charting, KAREN_W <KARENW@discussions.microsoft.com> said: >Is ...

Problem trying to export e-mail messages
When I go into Windows Mail and export my messages, the export completes but then I go and the folders don't match my existing e-mail folders. There are folders showing which I had deleted months ago. Could it be a corrupt file? How can I just transfer the e-mail messages I need to a new PC with Windows 7? Thanks> Windows Mail uses internal mail folder names that may not correspond exactly to the folder name in the user interface. What are you doing with your Windows Mail messages in Windows 7? Windows Mail was not designed to run on Windows 7. --=20 Gary VanderMole...

try/catch vs TRY/CATCH
Hi, What is in your opinion the preferred way of doing exception handling for MFC objects (such as the CFile)... the try/catch mechanism or the MFC based TRY/CATCH macros? Thanks Adeel Use try/catch. I don't recall what TRY/CATCH does but, basically, that was provided before VC++ had full support fro try/catch. -- Jonathan Wood SoftCircuits Programming http://www.softcircuits.com "Adeel" <dontWantSpam@All> wrote in message news:ORLqQz3fGHA.4568@TK2MSFTNGP03.phx.gbl... > Hi, > > What is in your opinion the preferred way of doing exception handling for ...

Run Time error when trying to use DMax on a line value
Please can someone help me with run time error I am getting when I try to use a DMax function on a LineID control in a subform. I have a main form frmDeliveries and a subform sfrmDeliveryDetails They are linked on master and child by a DeliveryID control. On the sfrmDeliveryDetails BeforeUpdate I entered : Private Sub Form_BeforeInsert(Cancel As Integer) Me![LineID] = Nz(DMax("[LineID]", "[sfrmDeliveryDetails]"), 0) + 1 End Sub (which I modified from a previous posting) When I try to enter a selection from a Productscombo on the sfrmDeliveriesDetails I get a Run Ti...

copy all and insert into Word as one item. 03 upgrade is differen
Pub 2000 - used to copy multiple items (text - pictures, etc) by select all. Then pasted into Word - and it came though as ONE item. This option was great - because I could use it easily change the size and it all stayed conformed to the original design. Miss it for webpages and PR. Is there any way to set the paste option to do the "old" one item paste? svlm wrote: || Pub 2000 - used to copy multiple items (text - pictures, etc) by || select all. Then pasted into Word - and it came though as ONE item. || This option was great - because I could use it easily change the || siz...

How do you merge data from numerous cells into just one cell?
I have a list of around 4 thousand people. Each person belongs to one "Group" which is tagged with a number. Basically there are two columns one showing the name and one with the group tag. What i want to do is have the group tags showing all the people who belong to that group. So I need to merge the data from a number of cells into one cell without losing the data. Any ideas? Thanks for your help. Try: =B3&""&C3&""&D3 I added the "" so a space is included in between cells. You can remove it if you don't need a space. ...

"record not locked" Can't post Order Entry
I am trying to run the Post Module Transaction OE4800 I get "Record not locked processing OEwork.dat (1200)" This started when I was interupted doing this process from home on a dialup. Please help, thanks Ed ...

Trying to finish up a worksheet function (if/or??)
Okay, here are the details and the exact data: A12 thru A17 = Income Source (text, user defined [examples would be Social Security, Pension, etc.]) B12 thru B17 = Amount Received (will be currency in $dddd.cc format) C12 thru C17 = Tax Deductible? (currenty have a drop-down list option, YES or NO) A18 = Subtotal (text) B18 = Subtotal (calculated sum of above columns) C18= [empty] A20 = Choose an Investor (text) B20 = Investor name (from drop down list) C20 = Mark up amount (i.e., 125%, would like it to auto fill from the investor chosen) A22 = Total (text) B22 = Total (calculated su...