Combine data in two records from same field

[Scott]

I have a database of family names and data. I need a summary query or report
that combines the husband and wife names on one line with their phone number.
The fields in the table are PersonID, FirstName, LastName, Phone, Address,
City, Zip, Family ID. Each individual has a separate record. The head of
household has a Family ID of 1, the spouse a Family ID of 2, and the children
a Family ID of 3, 4, 5, etc. In a report or query I need to combine the
record for Head of Household and Spouse for each family by combining the name
with a Family ID of 1 with Family ID 2 (the head of household and spouse)
into one line on a query or report. So the record for John Doe and the
record for Jane Doe would be combined into one record reading John & Jane Doe
with John Doe's Phone number. How can I accomplish this?

 

[joecosmides]

FullName: [SalesFirstName] & " " & [SalesLastName]

Enter the above into a query. In other words, add this to the same
area where the fields go into your query. Like when you double click a
field and it pops up into the slots below when building a query. Use a
blank slot and paste the code above it.

The code above grabs data from the 2 fields called [SalesFirstName]
and also [SalesLastName] and puts them into a new field (created from
the query) called FullName.

 

[joecosmides]

FullName: [SalesFirstName] & " " & [SalesLastName]

Enter the above into a query. In other words, add this to the same
area where the fields go into your query. Like when you double click a
field and it pops up into the slots below when building a query. Use a
blank slot and paste the code above it.

The code above grabs data from the 2 fields called [SalesFirstName]
and also [SalesLastName] and puts them into a new field (created from
the query) called FullName.

I think in your case you could do something like:
Family: [HusbandFirstName] & " " & [WifeFirstName] & " " &
[HusbandLastName]

 

[joecosmides]

FullName: [SalesFirstName] & " " & [SalesLastName]

Enter the above into a query. In other words, add this to the same
area where the fields go into your query. Like when you double click a
field and it pops up into the slots below when building a query. Use a
blank slot and paste the code above it.

The code above grabs data from the 2 fields called [SalesFirstName]
and also [SalesLastName] and puts them into a new field (created from
the query) called FullName.

I think in your case you could do something like:
Family: [HusbandFirstName] & " " & [WifeFirstName] & " " &
[HusbandLastName]

I forgot to add the "and" so it should look like this:
Family: [HusbandFirstName] & "and" & [WifeFirstName] & " " &
[HusbandLastName]

 

[Scott]

Will that work where the HusbandFirstName is in a different record than the
WifeFirstName?

FullName: [SalesFirstName] & " " & [SalesLastName]

Enter the above into a query. In other words, add this to the same
area where the fields go into your query. Like when you double click a
field and it pops up into the slots below when building a query. Use a
blank slot and paste the code above it.

The code above grabs data from the 2 fields called [SalesFirstName]
and also [SalesLastName] and puts them into a new field (created from
the query) called FullName.

I think in your case you could do something like:
Family: [HusbandFirstName] & " " & [WifeFirstName] & " " &
[HusbandLastName]

I forgot to add the "and" so it should look like this:
Family: [HusbandFirstName] & "and" & [WifeFirstName] & " " &
[HusbandLastName]

 

[Klatuu]

Nope, sorry that isn't going to work.

FullName: [SalesFirstName] & " " & [SalesLastName]

Enter the above into a query. In other words, add this to the same
area where the fields go into your query. Like when you double click a
field and it pops up into the slots below when building a query. Use a
blank slot and paste the code above it.

The code above grabs data from the 2 fields called [SalesFirstName]
and also [SalesLastName] and puts them into a new field (created from
the query) called FullName.

I think in your case you could do something like:
Family: [HusbandFirstName] & " " & [WifeFirstName] & " " &
[HusbandLastName]

 

[Steve Schapel]

Scott,

You will need a function to concatenate here. Have a look at the
Generic Function by Duane Hookom at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Having said that, it seems to me that the fields you have listed are not
sufficient. You will also need a field to identify each family group,
where the entry will be the same for each member of any given family,
i.e. John and Jane and all the little Does will have the same ID, which
is different from the IDs of members of other families... so we know
which husband goes with which wife.

 

[Steve Schapel]

No it won't.

 

[Klatuu]

Your FamilyID field should actually be to identify the family each person
belongs to. Then you need a field to identify the person's relationship to
the family - father, mother, child.

Now you can use a query that uses two instances of the table joined on the
FamilyID. Filter the first instance on the relationship being father and the
second being mother.

Then you assign aliases to the field names so you can construct the output
fields you want that will combine the two records into one.

 

[Klatuu]

Scott,
joe's suggestion is not correct. It is kind of him to offer a suggestion,
but either he doesn't understand your question or he doesn't unerstand
relational databases.

Will that work where the HusbandFirstName is in a different record than
the
WifeFirstName?

On Oct 22, 3:05 pm, (e-mail address removed) wrote:

FullName: [SalesFirstName] & " " & [SalesLastName]

Enter the above into a query. In other words, add this to the same
area where the fields go into your query. Like when you double click
a
field and it pops up into the slots below when building a query. Use
a
blank slot and paste the code above it.

The code above grabs data from the 2 fields called [SalesFirstName]
and also [SalesLastName] and puts them into a new field (created from
the query) called FullName.

I think in your case you could do something like:
Family: [HusbandFirstName] & " " & [WifeFirstName] & " " &
[HusbandLastName]

I forgot to add the "and" so it should look like this:
Family: [HusbandFirstName] & "and" & [WifeFirstName] & " " &
[HusbandLastName]

 

[joecosmides]

You need to have another ID field in your table so that you can assign
all of the family members the same exact ID. At first I thought that
was what FamilyID was for but you said that FamilyID is used for 1=
father, 2= mother, etc. In this case you need another ID field that is
the same for everyone in that family. Let's call it HouseHoldID.

If there are 5 family members which all have HouseHoldID=443 and you
create a query that shows only family members with the same
HouseHoldID then you would get a list of the 5 family members and all
their data. You would have to use an If statement in your query to
combine them properly but I'm not sure if it would work like you are
wanting. In other words, too much programming and it might not work
100% the way you want. I would suggest:

In your table, create field names for the whole family like:

FamilyID (autonumber/primary key) (this is the entire family's ID)
FatherFirstName (text field)
FatherLastName (text field)
MotherFirstName (text field)
MotherLastName (text field)
Child1FirstName (text field)
Child1LastName (text field)
Child2FirstName (text field)
Child2LastName (text field)
Child3FirstName (text field)
Child3LastName (text field)
Child4FirstName (text field)
Child4LastName (text field)
Child5FirstName (text field)
Child5LastName (text field)
Child6FirstName (text field)
Child6LastName (text field)

This is the easiest solution and you could eaily build queries and
reports with ease. You could also create Phone numbers and address
fields for each person as well. You'll have quite a few fields but at
the same time I've seen databases with waaaaaaay more fields in one
table that were quite fast.

 

[joecosmides]

Scott,
joe's suggestion is not correct.  It is kind of him to offer a suggestion,
but either he doesn't understand your question or he doesn't unerstand
relational databases.

Will that work where the HusbandFirstName is in a different record than
the
WifeFirstName?

On Oct 22, 3:10 pm, (e-mail address removed) wrote:
On Oct 22, 3:05 pm, (e-mail address removed) wrote:
FullName: [SalesFirstName] & " " & [SalesLastName]
Enter the above into a query. In other words, add this to the same
area where the fields go into your query. Like when you double click
a
field and it pops up into the slots below when building a query. Use
a
blank slot and paste the code above it.
The code above grabs data from the 2 fields called [SalesFirstName]
and also [SalesLastName] and puts them into a new field (created from
the query) called FullName.
I think in your case you could do something like:
Family: [HusbandFirstName] & " " & [WifeFirstName] & " " &
[HusbandLastName]
I forgot to add the "and" so it should look like this:
Family: [HusbandFirstName] & "and" & [WifeFirstName] & " " &
[HusbandLastName]- Hide quoted text -

- Show quoted text -

Yes, disregard my two earlier posts. My last post would be the easiest
thing to get this working and also make it easier for you to create
reports without having to use more complex ways to get the data you
desire. In other words, if you're a novice at using MS Access, maybe
you could do it this way. It's not the best way but it will work for a
novice like a charm.

 

[Wayne-I-M]

H Scott

I have just looked at the example that was suggested by Steve Schapel.
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16
It seems to be exactly what you are looking for


Following on from Dave's idea of a familyID - if you don't have too many
people in your table you could just type these in - if not an update query
based on the zip code "may" be an idea - unless more than 1 family live at
the same zip code - such as a block of appartments

 

[Steve Schapel]

Joe,

With all due respect, this is not about speed. It is about taking
advantage of the features of a database. In my opinion, the "flat
file"/spreadsheet-like idea that you have suggested will make querying
and reporting extraordinarily awkward, and I don't think it is "the
easiest solution".

 

[Klatuu]

Sorry, joe, but it is becoming obvious you don't understand relational
databases. I suggest you get a good book in relational database design and
study up on it.

What you are describing here is a spreadsheet, not a relational data model.

 

[joecosmides]

Sorry, joe, but it is becoming obvious you don't understand relational
databases.  I suggest you get a good book in relational database designand
study up on it.

What you are describing here is a spreadsheet, not a relational data model.










- Show quoted text -

I understand them but I question this gentleman's skill level with MS
Access. If he's a newbie, it could be quite difficult for him to do
the things you all have mentioned.

 

[Klatuu]

Okay, but I don't think giving instructions on bad design really helps.

Sorry, joe, but it is becoming obvious you don't understand relational
databases. I suggest you get a good book in relational database design and
study up on it.

What you are describing here is a spreadsheet, not a relational data
model.










- Show quoted text -

I understand them but I question this gentleman's skill level with MS
Access. If he's a newbie, it could be quite difficult for him to do
the things you all have mentioned.