Output data from tables into a directory or phonebook (1 Viewer)

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