Grouping

  • Thread starter Thread starter nhdee
  • Start date Start date
N

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-
 
Can you provide some actual field names and records? It's difficult to
differentiate records in your samples.

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


Duane Hookom said:
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 said:
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
________________________________________
Click to expand...
Click to expand...
 
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


nhdee said:
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-


Duane Hookom said:
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 said:
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-
Click to expand...
Click to expand...
Click to expand...
 
Back
Top