Sorting and Grouping a Report from Checkboxes

J

jbassett

Hello all:

I am looking for some help with a complex report. I am putting
together a database for a commercial real estate firm with the
following tables:

ProjInfo
BldgInfo
SuiteInfo

These tables are set up with one to many relationships between each.
Therefore a Project can have several buildings associated with it and a
building can have several suites.

The report I need to create contains information on all available
listings (the report's query returns only available listings). The
report needs to be organized first by City (contained in the Project
Info Table), then by project type, examples of which can be industrial,
office, retail, etc., followed by sorting if the project is for sale or
for lease.

So far I have a report which groups on city and project type and then,
in the detail section, lists all projects. I need to break the detail
section into For Sale and For Lease within EACH city for EACH project
type with a project which is both for sale and lease appearing in BOTH
sections.

Example of successful report would be:

City#1 Industrial For Sale (Header info)
Project Details here

City#1 Industrial For Lease (Header info)
Project Details here

City#1 Office Projects For Sale (Header info)
Project Details here

City#1 Office Projects For Lease (Header info)
Project Details here

City#2 Industrial Projects For Sale (Header info)
Project Details here

City#2 Industrial Projects For Lease (Header info)
Project Details here

Etc...

Is this possible?

In my table structure I have two checkboxes; one For Sale and one For
Lease. I considered using a single field in the table structure with
three values "For Sale"; For Lease"; "For Sale / Lease". However, this
would give me a report broken into three sections when I only need it
broken into two sections.

When I set another level of grouping based on the For Sale check box my
report breaks the detail section in two categories For Sale = True and
For Sale = False. I have figured out, via VBA in the OnFormat section
of the report, how to not display the category where For Sale = False
However, I cannot figure out how to show For Lease at this same level
of grouping.

This is the crux of my problem. How do I add a level of sorting based
off the two checkboxes and which may show a project in both the For
Lease section and the For Sale section if the project is for sale and
lease?

Any and all help appreciated.

Thank you
Jeff Bassett
 
M

Marshall Barton

I am looking for some help with a complex report. I am putting
together a database for a commercial real estate firm with the
following tables:

ProjInfo
BldgInfo
SuiteInfo

These tables are set up with one to many relationships between each.
Therefore a Project can have several buildings associated with it and a
building can have several suites.

The report I need to create contains information on all available
listings (the report's query returns only available listings). The
report needs to be organized first by City (contained in the Project
Info Table), then by project type, examples of which can be industrial,
office, retail, etc., followed by sorting if the project is for sale or
for lease.

So far I have a report which groups on city and project type and then,
in the detail section, lists all projects. I need to break the detail
section into For Sale and For Lease within EACH city for EACH project
type with a project which is both for sale and lease appearing in BOTH
sections.

Example of successful report would be:

City#1 Industrial For Sale (Header info)
Project Details here

City#1 Industrial For Lease (Header info)
Project Details here

City#1 Office Projects For Sale (Header info)
Project Details here

City#1 Office Projects For Lease (Header info)
Project Details here

City#2 Industrial Projects For Sale (Header info)
Project Details here

City#2 Industrial Projects For Lease (Header info)
Project Details here

Etc...

Is this possible?

In my table structure I have two checkboxes; one For Sale and one For
Lease. I considered using a single field in the table structure with
three values "For Sale"; For Lease"; "For Sale / Lease". However, this
would give me a report broken into three sections when I only need it
broken into two sections.

When I set another level of grouping based on the For Sale check box my
report breaks the detail section in two categories For Sale = True and
For Sale = False. I have figured out, via VBA in the OnFormat section
of the report, how to not display the category where For Sale = False
However, I cannot figure out how to show For Lease at this same level
of grouping.

This is the crux of my problem. How do I add a level of sorting based
off the two checkboxes and which may show a project in both the For
Lease section and the For Sale section if the project is for sale and
lease?
Click to expand...


I think your issue is that you need two records for the
entroes that have both sale and lease checked. This is
relatively easy to accomplish by using a UNION query as the
report's record source.

SELECT thisfield, thatfield, . . ., 1 As SaleLease
WHERE forsale
UNION ALL
SELECT thisfield, thatfield, . . ., 2 As SaleLease
WHERE forlease

Now you can group on the SaleLease field and the records
that are in both categories will appear in both groups.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sometimes sub report shows twice in detail section 2
Sorting report by grand total from report footer 2
Report - Sorting and Grouping 1
Switching off grouping *and sorting* in a report? 2
Access Sorting and grouping using multiple tables 0
Report Headers Mis-Matched from detail on report 1
Multi level grouping in a report 2
Combine lines in a Report? 4

Top