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

Similar Articles

[PageSpeed] 33

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:

FRx statements exported to one Excel workbook
Clients would like to export chained FRx reports to export to one Excel workbook for easier e-mailing. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=5c667c48-735e-42b4-99...

tg
I have been running 2 Exchange 2000 servers for 2 years. I recently installed an Exchange 2003 server and moved all mailboxes from one of the Exchange 2000 servers to this new 2k3 server. It was 35 mailboxes and all worked fine. I have had no problems whatsoever as I took my time making sure to prep everything needed for a smooth transition to Exchange 2003. I now have one Exchange 2003 server and one Exchange 2003 server running. This morning I setup a new computer (XP Pro SP2) for an existing user that already has a mailbox on the Exchange 2003 server. When configuring Outlook 2003 ...

Multiple Exchange accounts into one Inbox
I have an Exchange 2000 Server and 16 employees, most of which who work at our client's offices. They have laptops with WinXP and Outlook 2002 which are attached to the local network. Our clients have Exchange as well. Currently, we use Outlook to read our email and calendaring for our accounts on the client's network. Additionally, we log in via WebAccess to do the same for our company email and calendaring. My employees find that checking multiple locations for email/calendar/tasks very time-consuming. I'm considering the idea of installing a VPN so employees ...

Trying again for an answer.
I have macros running in excel which massage data created by Live Office (Business Objects Queries.) Every time a macro tries to edit the data returned by the query I am hounded by a message which says, "The value(s)that came from a BusinessObjects Enterprise data source has just been edited/deleted. Do you want to proceed with the change?". "With most popup messages I just use the displayalerts statement to get around it. That does not work for this message which specifically addresses the Live Office edits. Does anyone know how to suppress it. I have disc...

Email multiple reports 11-16-07
Hello, I have a database where I need to send one email with several reports in it. Is there vb code to do that? On Fri, 16 Nov 2007 05:32:01 -0800, Brett <Brett@discussions.microsoft.com> wrote: Nothing built-in. DoCmd.SendObject can only send one report at a time. So you need to first save the reports (e.g. to PDF files), then create an email with multiple attachments. If you use Google, I'm sure you can locate some sample code. -Tom. >Hello, >I have a database where I need to send one email with several reports in it. > Is there vb code to do that? How about this...

Excel Opens 2 files, I only created one?
I have been working on a spreadsheet for the past 6 months with no issues. The file size is 3.66mb, Excel 2000, Windows 2000. Everytime I open the file "abc" I get two Excel files open named "abc:1" and "abc:2". Does anyone have any idea why this is and what I can do to rectify it? All other files open OK. Thanks in advance. Mark You get these by clicking on: Window > New Window To get rid of excess windows, click on "Close Window" (that's the cross in the top-right of the screen underneath the "Close" cross). -- Regards Andy W...

pivot table multiple criteria
Hi, I have a pivot table in which I can select a weeknumber to get the number of visitors in a pivot table and diagram. I'm looking for a possibility to select multiple weeknumbers and get the combined result in a pivot table and diagram. I was thinking of a kind of selection buttons or something like that. Any help or suggestion is more than welcome. Kind regards, Chris Hi Chris I am assuming you are working with XL2003 or earlier. Is your field a Page field? If so, drag it to the Row area, make your Multiple selections and drag back to the Page area where the selection will s...

Command button
Sorry for the bad Subject. Hopefully I can better explain my issue here. I have ONE command button to launch a query. But depending on the time of month and which month we are in will determine which query is ran. For example, the date is today, 5/11/07, the query will retrieve all records for the past 3 months including current month, May April and March. Come June 1st thru June 7th, I want the query to retrieve May, April and March but after June 7th, June, May and April. the queries are built, I am looking to create one command button so there is no confusion to the user. The process...

Help With Database Query
I am using a database query to retrieve data from another database excel file. The problem I am having is that the column of data which I have contains records which have both text and number formats. When the data transfers, it only brings those records which are number format. Is there any way to make is so that the database query will pull both number and text formats, as I can't easily change it so that it is one or the other? Thanks ...

Query re Microsoft CRM-Exchange E-Mail Router
We're thinking about purchasing Microsoft CRM. Question: if an email is directed to an address that shows up in Microsoft CRM, is the email a) no longer stored on the Exchange Server but only on the SQL server pointed at by CRM b) on both c) just on the Exchange Server but incorporated from there by the CRM system. Where can I find more information or documentation on this feature of Microsoft CRM? In general product description documentation seems very weak. All inbound e-mail goes to Exchange just as it would if you didn't have the CRM Router installed. When installed and...

Running saved query from Data menu
In older version of Excel, you use to be able to go to the Data menu and select "Run Saved Query". Now that I've got Office XP, it's disappeared from the menu. How can I run a saved query? ...

Transpose Microsoft Query
I need to transpose some data that I bring in via a microsoft query, however when I do the copy and special paste it works properly the first time, whenever i update the query, the transposed special paste goes back to the orginal format. Is there anyway to do this? Terry hi how are you updating the query now? how big is it? were are you transposing it? a tad short on details but you might try something like this code. Sub TransposeMSQ() Sheets("Sheet1").Activate Range("A1").QueryTable.Refresh BackgroundQuery = False Range("A1:A50").Copy Ran...

One contact multiple accounts
We work with several contacts who are responsible for multiple accounts and I can't figure out the best way to handle this in CRM. Does anyone have a suggestion for how to handle this? There are a few ways you can skin it. 1. Though contact can belong to only one account, it can be a primary contact for multiple accounts. 2. Built-in Relationship Roles (available under Settings) allow to create new relationships between accounts and contacts. Once relationship is set up, account records are going to be available on a contact form under Relationships. 3. If relationship needs to car...

Replacing Access query with SQL Select VBA?
I am in the process of extending my Access 2007 knowledge and hopefully VBA skills. I am reviewing samples, reading many of the posts on this group, etc. In the long run I need to learn VBA inside and out based on my professional goals so I am trying balance between learning the basics of the language and working through examples. So I have two questions. 1.) What is the best online reference for the VBA language for Access 2007? 2.) In my test database a have several tables and made a query selecting the information I want based on my criteria for a special report that I developed. The ...

MS Money 2005
I would like to know if it is possible to print more than one invoice a a time. I have entered my information into the program and into th "Accounts Receivable," but can find not way to print more than on invoice at a time. I would also like to print these invoices using template I have created through the Invoice Designer. Please ca someone help me -- vm ----------------------------------------------------------------------- vmo's Profile: http://www.msusenet.com/member.php?userid=422 View this thread: http://www.msusenet.com/t-187097451 It's not possible to batch pr...

RELATIONSHIP & QUERIES
QUESTION . First I will enter info into TBL Radio Receipt, from there I want the information to jump to TBL Radio Info and prompt me to complete this table. Then it will jump to either TBL New Radio Information OR TBL Repair Information. This will need to be completed. How does this happen, I mean-- How do I create the relationships to reflect this path? I tryed making a one to many but it will not assocate. PS I have a snap shot of problem Hi, Not sure what information you have for your radio repair business, here is a starting idea for 2 tables. tblRadios is related One to Many t...

Excel data in one cell, need to copy specific values to another worksheet in different cells
I am attempting to copy financial data from one worksheet to another. Unfortunately, the worksheet that I am copying from is formatted as one column of data in column A. The data appears as follows in column A: DISTRICT: Adams County/Ohio Valley Local COUNTY: Adams IRN: 061903 ADM, VALUATION & MILLAGE (1983-1984) SOURCE: SF12 Line 3 Basic ADM 4,916 Assessed value 480,468,317 Total ADM 5,769 Valuation/Basic ADM 97,736 Voted millage (Incl JVS) 20.80 Class1 eff tax rate 20.14 BASIC STATE AID (1983-...

multiple windows open
When I d-click (open) and excel document 2 windows automatically ope and are labled Title.xls:1 and Title.xls:2. When I close one windo both close. When I re-open the file 2 windows are opened again. I kno how to add more windows, but do not know how to remove them. Any ideas Coudn't find anything under F1. Thank you! -Je -- Message posted from http://www.ExcelForum.com Just select then close one of the windows using the "x" on its window. Do not close using the Application "x". Save the workbook. Gord Dibben Excel MVP On Fri, 18 Jun 2004 12:18:37 -0500, jlklafin...

Convert rows to one single long column?
If I have a bunch of rows with say 5 columns each, is there an easy way to convert it to one big long column, just adding each row below the information from the previous row after being converted to a column? Copy and then Paste Special Transpose doesn't quite do it since it makes multiple columns. -- Spalding ------------------------------------------------------------------------ Spalding's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8634 View this thread: http://www.excelforum.com/showthread.php?threadid=486720 You can use a formula, assuming that...

query help 03-18-10
I need some help from you the experts: I am having an issue with my access query... I have a parameter asking for a start and end date (see below) when I enter one date 2/14/2010 thru 2/14/2010 it shows the record but, if I enter a range 02/14/2010 - 3/28/2010, I seem to get everything that starts with a 2 or 3.. HELP???? SELECT PatientInformation.MedicalRecordNumber, ([PatientLastName] & ", " & [PatientFirstName]) AS [Patient Name], PatientInformation.PatientPhoneNumber, PatInfo.Comments, PatInfo.DateOfVisit, PatInfo.DXDescription, PatInfo.Physician, PatInfo...

Multiple Companies 11-19-07
We have a CRM server installed and our companie has 2 brands. Both brands use the same CRM install. There is one Mother BU and the 2 brands are 2 child BU Both brands have their own contacts and Accounts. What is an easy way of splitsing these accounts so it is very clear to wich brand the contact or account belongs to? As a quick solution if that's how you want it to be done, you can set the users for each BU to: 1. The users for Brand 1 in "Brand 1 BU", and users for Brand B in "Brand 2 BU" 2. only have a Parent-Child privileges HTH, Ian Salgado "Deeske&q...

Multiple Sessions
Can I have multiple sessions of Outlook running at the same time on one machine. I would like to be able to have multiple mailboxes open in multiple sessions. I know I can remove /recycle from the target and that will allow me to open additional sessions, but it opens the same mailbox by default. No, this isn't possible. Are you connecting to an Exchange server? If so, you could add the other mailboxes to your folder list in the advanced tab in the Exchange server service properties in Outlook. CC wrote: > Can I have multiple sessions of Outlook running at the > same time on...

Table of incrementing by one numbers
I would like to be able to enter a number in the first cell and have it populate a table - 6 columns by 30 rows - incrementing each cell by one. For ex, if I enter 100 in the first cell, the rest of the row would be 101,102,103,104,105. The next row would be 106, 107, 108, 109, 110, 111. Etc. Is there a easy way to do this? Thanks, Someone who rarely uses Excel -- brbarto ------------------------------------------------------------------------ brbarto's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29755 View this thread: http://www.excelforum.com/showthre...

lstBox Record Set won't change
With the code below I'm attempting to change the row source for lstRNnotesLU (a list box) based on a value entered on the frmVisitNewEdit field called ReasonForVisitTest. For some reason it is not working. Then record set is not changed by the code. It continues to use the underlying query for the rowsource rather than the rowsource based on the code below. Your help is greatly appreciated. Thanks Rob ****************************************************************************************************************** Dim T_Visit As String T_Visit = Nz(Forms!frmPtDemographicNe...

One contact with multiple parent accounts
I have a scenario where the contact holds positions wth two accounts and is my primary lead for both of them. Any comments/ideas? Contacts can be Primary Contact of multiple Accounts but not have more than one Parent Account set. Jason Hunt Technical Product Manager Microsoft Business Solutions CRM This posting is provided "AS IS" with no warranties, and confers no rights. "Didg" <Didg@discussions.microsoft.com> wrote in message news:16DACFB9-732B-423B-A404-81C9CB9AC9C9@microsoft.com... > I have a scenario where the contact holds positions wth two accounts and...