Melanie-
You need a query something like this:
SELECT Directory.*, Directory2.*
FROM Directory LEFT JOIN Directory As Directory2
ON Directory.Family = Directory2.Family
WHERE Directory.FamilyRole = <the code for head of household>
AND Directory.ID <> Directory2.ID;
That will get you a recordset with the data from the head of household joined
with the data from all household members - one row per household member with the
head of household info repeated on each row.
Use that in an Access report and create a group on head of household. Put the
head of household info in the group header and put the columns from the members
in the detail section. Note that I joined the table to itself to get this to
work.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of melaniesauter74
Sent: Friday, November 04, 2011 2:59 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Creating a Directory from Access 2010 table
John,
Thank you for your response. I have a table with all individuals listed with
their personal information, including Family Role, which does include head of
household. I created and titled a column called FAMILY that relates indiviual
in that specific family to each other in order to create the family as one
entity in the directory. The directory will list the family with an address and
main contact phone number followed by the individuals and personal email and/or
cell number. I am sure this can be done in some capacity, I am just having
trouble making it in Word, as it doesn't like my table, and I cannot locate any
places in Access to even begin the directory without creating Contacts which
will not pull my fields. UGH! lol
Thank you!
Melanie
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Dear Melanie: (name correct?)
>
> It would help to know more about how your tables are designed. Do you have a
> separate table to define each Family? Do you have a field that defines "head
of
> household"? And what do you want in your directory report?
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of melaniesauter74
> Sent: Thursday, November 03, 2011 8:39 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Creating a Directory from Access 2010 table
>
> I am rather new to Access (haven't used since 2000 version), so I am able to
set
> up the basics, but now I have run into some trouble. I have not been able to
> find an actual program to gather all of the information needed for our group
of
> church members into one usable place, so I have decided to create it on my own
> through Access. So far, my queries, reports, etc. seem to be coming out as
> desired. However.... I also want to create our yearly church directory using
> the information I have input. Each individual is input individually, but I
have
> set a family "code" for each family to share. I am now trying to create a
> directory in which I can use this information, without have to re-input each
> time, to share "family" information for contact. Am I all wet? Or could
> someone PLEASE send me a lifeline and point me in the right direction? All my
> thanks!!!
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar