Grouping

[nhdee]

I have a home inspection database created. In the report for the Interior
section, which comes from a query, I want to be able to group all the
interior door results together, all the exterior door results.... It is
filtered by client. The way the database is set-up is a new record is
created if there is a second door. The report shows all the information I
want, now I just need to group the information correctly.

This is the current view:
Entry Door:
Location: By Driveway
Condition: 1-Appears Serviceable
Interior Door:
Location: Living Room
Condition: 1-Appears Serviceable
Exterior Door:
Type: Tempered glass
Condition: 1-Appears Serviceable
---------------------------------------
-- Entry Door:
Location: Garage
Condition: 1-Appears Serviceable
Interior Door:
Location: Back
Condition: 1-Appears Serviceable

What I want is:
Entry Door:
Location: By Driveway
Condition: 1-Appears Serviceable

Location: Garage
Condition: 1-Appears Serviceable
________________________________________
Interior Door:
Location: Living Room
Condition: 1-Appears Serviceable

Location: Back
Condition: 1-Appears Serviceable
________________________________________
I have tried Grouping but it isn't working and I don't know why. Hopefully
I have provided enough information for someone to help me.....
-nhdee-

 

[Duane Hookom]

Can you provide some actual field names and records? It's difficult to
differentiate records in your samples.

Duane Hookom
MS Access MVP

 

[nhdee]

The pk is ID and filtered by Client (Client's name). EntDoor(Entry Door);
DoorsC (Entry Door Condition); DoorLoc (Entry Door Location);IntDoor
(Interior Door); IDoorLoc (Interior Door Location); IDoorC (Interior Door
Condition); IDoorCom (Interior Door Comment); EDoor (Exterior Door); EDoorC
(Exterior Door Condition); EDoorC (Exterior Door Condition); EDoorLoc
(Exterior Door location); EDoorCom (Exterior Door Comment)..... Location
(combo box), Condition & Comments listboxes and each lookup from respective
tables.

 

[Duane Hookom]

This information is no where near complete enough for me to provide an
answer. I don't know what is a field name or a value.

 

[nhdee]

I have a form “Interior†based on a table “Results - Interiorâ€. On the form
the user selects the client and from list or combo boxes the inspection
results go to the fields in the table. A query was created to join the
â€Results - Interior†table and contacts table. The report is based on the
query.

I am getting what I want for results - just need to group the information
differently:

What I want is each record for Entry Door for instance grouped with Entry
Door..... Rather than all record # 1 grouped together than record #2....
Entry Door:
Location: By Driveway (table field name: DoorLoc)
Condition: 1-Appears Serviceable (table field name: DoorsC)
-------2nd record for Entry Door, the same client (ID), same as
above---------
Location: Garage
Condition: 1-Appears Serviceable
________________________________________
Interior Door:
Location: Living Room (table field name: IDoorLoc)
Condition: 1-Appears Serviceable (IDoorC)
-------2nd record Interior Door, for the same client (ID), same as
above---------
Location: Back
Condition: 1-Appears Serviceable
________________________________________

 

[Duane Hookom]

I think I understand now. It seems your table structure resembles a
spreadsheet with names of items as field names rather than values in a field
named "Item".

You might need to create a normalizing union query to fix your structure.
SELECT ID, Client, "Entry Door" as InspectPoint, EntDoor InspResult
FROM [Results - Interior]
UNION ALL
SELECT ID, Client, "Entry Door Condition", DoorsC
FROM [Results - Interior]
UNION ALL
SELECT ID, Client, "Entry Door Location", DoorLoc
FROM [Results - Interior]
UNION ALL
'--- etc ---
Once your data is normalized, you can group a report on [InspectPoint]

Duane Hookom
MS Access MVP

 

[nhdee]

Hi Duane,

Thanks for your input but either I am doing something wrong or still haven't
given you enough information.

Where you have "EntDoor InspResult" is that a new query I should be
creating? I don't understand that....

My table = "Results - Interior" (that is where the information from the
forms is stored) and my query = "Interior Results" and that joins the table
with Contacts-Extended.

I have tried substituting [Results - Interior] as you had with [Interior
Results]. The error message I am receiving is object invalid or no longer
set????

A lot of the fields in the table are multi-valued. Can I normalize still
with a union query?

If I can normalize this way, how do you end the union query with just Union
All? I got syntax error in query, incomplete query clause at one point.

Thanks again for your help!

-nhdee-

I think I understand now. It seems your table structure resembles a
spreadsheet with names of items as field names rather than values in a field
named "Item".

You might need to create a normalizing union query to fix your structure.
SELECT ID, Client, "Entry Door" as InspectPoint, EntDoor InspResult
FROM [Results - Interior]
UNION ALL
SELECT ID, Client, "Entry Door Condition", DoorsC
FROM [Results - Interior]
UNION ALL
SELECT ID, Client, "Entry Door Location", DoorLoc
FROM [Results - Interior]
UNION ALL
'--- etc ---
Once your data is normalized, you can group a report on [InspectPoint]

Duane Hookom
MS Access MVP

I have a form “Interior†based on a table “Results - Interiorâ€. On the
form
the user selects the client and from list or combo boxes the inspection
results go to the fields in the table. A query was created to join the
â€Results - Interior†table and contacts table. The report is based on the
query.

I am getting what I want for results - just need to group the information
differently:

What I want is each record for Entry Door for instance grouped with Entry
Door..... Rather than all record # 1 grouped together than record #2....
Entry Door:
Location: By Driveway (table field name: DoorLoc)
Condition: 1-Appears Serviceable (table field name: DoorsC)
-------2nd record for Entry Door, the same client (ID), same as
above---------
Location: Garage
Condition: 1-Appears Serviceable
________________________________________
Interior Door:
Location: Living Room (table field name: IDoorLoc)
Condition: 1-Appears Serviceable (IDoorC)
-------2nd record Interior Door, for the same client (ID), same as
above---------
Location: Back
Condition: 1-Appears Serviceable
________________________________________

 

[Duane Hookom]

As I suggested, you have created an un-normalized table structure. I
suggested you create a normalizing union query. I'm not sure how/why you
are using multi-value fields. I don't believe in using this functionality.

Do you understand when I state your structure is un-normalized and why? If
not, search the web on "database normalization". I would set up an
inspection application much like At Your Survey found at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3&SID=dfed6797436b881b7b3d59e73f4c83c1.

A union query would transform your table and would end with the FROM clause

FROM [Results - Interior];

--
Duane Hookom
MS Access MVP

Hi Duane,

Thanks for your input but either I am doing something wrong or still
haven't
given you enough information.

Where you have "EntDoor InspResult" is that a new query I should be
creating? I don't understand that....

My table = "Results - Interior" (that is where the information from the
forms is stored) and my query = "Interior Results" and that joins the
table
with Contacts-Extended.

I have tried substituting [Results - Interior] as you had with [Interior
Results]. The error message I am receiving is object invalid or no longer
set????

A lot of the fields in the table are multi-valued. Can I normalize still
with a union query?

If I can normalize this way, how do you end the union query with just
Union
All? I got syntax error in query, incomplete query clause at one point.

Thanks again for your help!

-nhdee-

I think I understand now. It seems your table structure resembles a
spreadsheet with names of items as field names rather than values in a
field
named "Item".

You might need to create a normalizing union query to fix your structure.
SELECT ID, Client, "Entry Door" as InspectPoint, EntDoor InspResult
FROM [Results - Interior]
UNION ALL
SELECT ID, Client, "Entry Door Condition", DoorsC
FROM [Results - Interior]
UNION ALL
SELECT ID, Client, "Entry Door Location", DoorLoc
FROM [Results - Interior]
UNION ALL
'--- etc ---
Once your data is normalized, you can group a report on [InspectPoint]

Duane Hookom
MS Access MVP

I have a form “Interior†based on a table “Results - Interiorâ€. On the
form
the user selects the client and from list or combo boxes the inspection
results go to the fields in the table. A query was created to join the
â€Results - Interior†table and contacts table. The report is based on
the
query.

I am getting what I want for results - just need to group the
information
differently:

What I want is each record for Entry Door for instance grouped with
Entry
Door..... Rather than all record # 1 grouped together than record
#2....
Entry Door:
Location: By Driveway (table field name: DoorLoc)
Condition: 1-Appears Serviceable (table field name: DoorsC)
-------2nd record for Entry Door, the same client (ID), same as
above---------
Location: Garage
Condition: 1-Appears Serviceable
________________________________________
Interior Door:
Location: Living Room (table field name: IDoorLoc)
Condition: 1-Appears Serviceable (IDoorC)
-------2nd record Interior Door, for the same client (ID), same as
above---------
Location: Back
Condition: 1-Appears Serviceable
________________________________________

--
-nhdee-


:

This information is no where near complete enough for me to provide an
answer. I don't know what is a field name or a value.

--
Duane Hookom
MS Access MVP


The pk is ID and filtered by Client (Client's name). EntDoor(Entry
Door);
DoorsC (Entry Door Condition); DoorLoc (Entry Door Location);IntDoor
(Interior Door); IDoorLoc (Interior Door Location); IDoorC
(Interior
Door
Condition); IDoorCom (Interior Door Comment); EDoor (Exterior Door);
EDoorC
(Exterior Door Condition); EDoorC (Exterior Door Condition);
EDoorLoc
(Exterior Door location); EDoorCom (Exterior Door Comment).....
Location
(combo box), Condition & Comments listboxes and each lookup from
respective
tables.
--
-nhdee-


:

Can you provide some actual field names and records? It's difficult
to
differentiate records in your samples.

Duane Hookom
MS Access MVP

I have a home inspection database created. In the report for the
Interior
section, which comes from a query, I want to be able to group all
the
interior door results together, all the exterior door results....
It
is
filtered by client. The way the database is set-up is a new
record
is
created if there is a second door. The report shows all the
information I
want, now I just need to group the information correctly.

This is the current view:
Entry Door:
Location: By Driveway
Condition: 1-Appears Serviceable
Interior Door:
Location: Living Room
Condition: 1-Appears Serviceable
Exterior Door:
Type: Tempered glass
Condition: 1-Appears Serviceable
---------------------------------------
-- Entry Door:
Location: Garage
Condition: 1-Appears Serviceable
Interior Door:
Location: Back
Condition: 1-Appears Serviceable

What I want is:
Entry Door:
Location: By Driveway
Condition: 1-Appears Serviceable

Location: Garage
Condition: 1-Appears Serviceable
________________________________________
Interior Door:
Location: Living Room
Condition: 1-Appears Serviceable

Location: Back
Condition: 1-Appears Serviceable
________________________________________
I have tried Grouping but it isn't working and I don't know why.
Hopefully
I have provided enough information for someone to help me.....
-nhdee-