Output data from tables into a directory or phonebook (5 Viewers)

[ElijahNZL27]

Hi Everyone, I am new here.

I have designed a church database with two main tables: tblHousehold and tblPersonal.

tblHousehold contains data specific to a household i.e. family surname, family address, family email, family phone.
tblPersonal contains data specific to the individual persons of the household i.e. individual name, mobile, email, membership status and so on.

The two tables are joined on the field ID_Household. This means that in tblHousehold I can see the subdata - that is - all the individuals within the Household. It's great.

However, any query results in the output of each individual member including their household name in a separate row.

I'm wanting what the Report does - grouping of individuals within the Household name - in a format I can export and then merge into a Word document or something like that.

Does anyone know how I might be able to achieve this please?

Thank you in advance.

Gene

 

[DHookom]

Is it possible to provide a sample of your desired output?

 

[ElijahNZL27]

Here is sample data in the format I currently have....

The output I need will be in the form of...

Household Name (Doe), Household Address, Household Phone, Adult1 (John), Adult2 (Jane), Child1 (Bambi), Child2 (upto Child6)


Does this make sense?

Thank you for your interest.

Gene

 

[MarkK]

Are you talking about needing a hierarchical view? Like, do you want something like...

Doe
    John
    Jane
    Bambi
Smith
    Metal
    Gold
    Aero

If so, look into grouping data in a report. You write a query that returns...

Doe, John
Doe, Jane
Doe, Bambi
Smith, Metal
Smith, Gold
Smith, Aero

...and then you tell the report to group by household, and it adds a group header section. Then for each header, the report prints multiple detail sections, as many as exist in the data.
hth

 

[Gasman]

You can use @theDBguy 's function. https://www.accessmvp.com/thedbguy/codes.php?title=simplecsv
Alternatively Allen Browne's http://allenbrowne.com/func-concat.html

 

[ElijahNZL27]

Are you talking about needing a hierarchical view? Like, do you want something like...

Doe
    John
    Jane
    Bambi
Smith
    Metal
    Gold
    Aero

If so, look into grouping data in a report. You write a query that returns...

Doe, John
Doe, Jane
Doe, Bambi
Smith, Metal
Smith, Gold
Smith, Aero

...and then you tell the report to group by household, and it adds a group header section. Then for each header, the report prints multiple detail sections, as many as exist in the data.
hth

Hi. Yes. Like your top one.

Is there a way of exporting the data in the report that has the grouping like the top example please?

 

[BlueSpruce]

Hi. Yes. Like your top one.

Is there a way of exporting the data in the report that has the grouping like the top example please?

https://support.microsoft.com/en-gb/office/export-data-to-excel-64e974e6-ae43-4301-a53e-20463655b1a9

 

[arnelgp]

SELECT tblHousehold.ID_Household,
tblHousehold.[Family Surname] AS [Household Name],
tblHousehold.[Family Address] AS [Household Address],
tblHousehold.[Family Phone] AS [Household Phone],
(select top 1 Person_First From tblPersonal Where ID_Household = tblHousehold.ID_Household And Person_Category = "Adult1") AS Adult1,
(select top 1 Person_First From tblPersonal Where ID_Household = tblHousehold.ID_Household And Person_Category = "Adult2") AS Adult2,
(select top 1 Person_First From tblPersonal Where ID_Household = tblHousehold.ID_Household And Person_Category = "Child1") AS Child1,
(select top 1 Person_First From tblPersonal Where ID_Household = tblHousehold.ID_Household And Person_Category = "Child2") AS Child2,
(select top 1 Person_First From tblPersonal Where ID_Household = tblHousehold.ID_Household And Person_Category = "Child3") AS Child3,
(select top 1 Person_First From tblPersonal Where ID_Household = tblHousehold.ID_Household And Person_Category = "Child4") AS Child4,
(select top 1 Person_First From tblPersonal Where ID_Household = tblHousehold.ID_Household And Person_Category = "Child5") AS Child5,
(select top 1 Person_First From tblPersonal Where ID_Household = tblHousehold.ID_Household And Person_Category = "Child6") AS Child6
FROM tblHousehold;

see query1.

 

[Ken Sheridan]

Here is sample data in the format I currently have....
View attachment 122320

To create an address book report you will need to create a parent report, and two subreports, one for adults, the other for children.

Create the parent report with the Households table as its RecordSource property. Order the report by HouseholdName . Place the relevant controls to return the household name, address etc. in the report's detail section.

Next create the adults report with, as its RecordSource, a query which returns the necessary columns from the People table, restricting the query to WHERE Person_Membership <> "Child". In the report place controls bound to the relevant columns in your desired layout in the detail section. Embed this report as a subreport in the households parent report's detail section below the household controls, linking the subreport to the parent report on ID_Household.

Create the children subreport in the same way, this type restricting the query to WHERE Person_Membership = "Child". Embed this report as a subreport in the parent report's detail section below the adults subreport.

When you open the parent report the households will be listed alphabetically. For each household the adults will be listed below the household data, and the children will then be listed below the adults data. There is no limit to the number of children who will be listed. It will be as few or as many per household as necessary on the basis of the data.

 

[RonPaii]

One thing to remember about exporting Access reports to Word is that any graphics lines, boxes etc. are lost in the transfer.

 

[Ken Sheridan]

One thing to remember about exporting Access reports to Word is that any graphics lines, boxes etc. are lost in the transfer.

In situations like this I'd usually output a report to a PDF file.