Report 1 record twice

[macarch]

I am trying to create a report from a query. In that query I am showing
individual records representing Architectural Projects (i.e. McDonalds
Remodel, Bank One Remodel, State Farm Office Building, etc.), and each record
has been assigned a main category (i.e. Food Service, Finanical, Office
Planning). In some cases there are records with a secondary category (i.e.
Bank One Remodel is Financial, but also has Office Planning as a secondary
category). I want to have a single report lising all the projects in each
category with the secondary category incorporated into the main catagory
list. Therefore, the single record "Bank One Remodel" would show up in two
locations; the Financial list and the Office Planning list. Right now, I can
only get the single record to show up in the main category list.
Main Category Secondary
Category
Record1: McDonalds Remodel Food Service
Record2: Bank One Remodel Financial Office Planning
Record3: State Farm Office Bldg. Office Planning

The report I want should look like this:

Food Service:
McDonald's Remodel

Financial:
Bank One Remodel

Office Planning:
Bank One Remodel
State Farm Office Bldg.

Any help or direction would be appreciated. Thanks.

 

[KARL DEWEY]

If I understand your table structure correctly then use a union query --
SELECT Project, [Main Category] AS Category
FROM YourTable
SELECT Project, [Secondary Category] AS Category
FROM YourTable;

Then in your report group on Category.

 

[macarch]

Karl,

Unfortunatly I am not too familar with the proper code for a union query. I
have tried several times composing a query based on what you wrote, but I
continue to get a syntex error on the FROM statement. Here is what I wrote:

SELECT Project, [Project Category] AS Category
FROM Project Descriptions 2000
SELECT Project, [Project Category2] AS Category
FROM Project Descriptions 2000;

If I understand your table structure correctly then use a union query --
SELECT Project, [Main Category] AS Category
FROM YourTable
SELECT Project, [Secondary Category] AS Category
FROM YourTable;

Then in your report group on Category.

--
KARL DEWEY
Build a little - Test a little

I am trying to create a report from a query. In that query I am showing
individual records representing Architectural Projects (i.e. McDonalds
Remodel, Bank One Remodel, State Farm Office Building, etc.), and each record
has been assigned a main category (i.e. Food Service, Finanical, Office
Planning). In some cases there are records with a secondary category (i.e.
Bank One Remodel is Financial, but also has Office Planning as a secondary
category). I want to have a single report lising all the projects in each
category with the secondary category incorporated into the main catagory
list. Therefore, the single record "Bank One Remodel" would show up in two
locations; the Financial list and the Office Planning list. Right now, I can
only get the single record to show up in the main category list.
Main Category Secondary
Category
Record1: McDonalds Remodel Food Service
Record2: Bank One Remodel Financial Office Planning
Record3: State Farm Office Bldg. Office Planning

The report I want should look like this:

Food Service:
McDonald's Remodel

Financial:
Bank One Remodel

Office Planning:
Bank One Remodel
State Farm Office Bldg.

Any help or direction would be appreciated. Thanks.

 

[KARL DEWEY]

ERROR on my part --
SELECT Project, [Main Category] AS Category
FROM YourTable
UNION SELECT Project, [Secondary Category] AS Category
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little

If I understand your table structure correctly then use a union query --
SELECT Project, [Main Category] AS Category
FROM YourTable
SELECT Project, [Secondary Category] AS Category
FROM YourTable;

Then in your report group on Category.

--
KARL DEWEY
Build a little - Test a little

I am trying to create a report from a query. In that query I am showing
individual records representing Architectural Projects (i.e. McDonalds
Remodel, Bank One Remodel, State Farm Office Building, etc.), and each record
has been assigned a main category (i.e. Food Service, Finanical, Office
Planning). In some cases there are records with a secondary category (i.e.
Bank One Remodel is Financial, but also has Office Planning as a secondary
category). I want to have a single report lising all the projects in each
category with the secondary category incorporated into the main catagory
list. Therefore, the single record "Bank One Remodel" would show up in two
locations; the Financial list and the Office Planning list. Right now, I can
only get the single record to show up in the main category list.
Main Category Secondary
Category
Record1: McDonalds Remodel Food Service
Record2: Bank One Remodel Financial Office Planning
Record3: State Farm Office Bldg. Office Planning

The report I want should look like this:

Food Service:
McDonald's Remodel

Financial:
Bank One Remodel

Office Planning:
Bank One Remodel
State Farm Office Bldg.

Any help or direction would be appreciated. Thanks.

 

[KARL DEWEY]

This should do it --
SELECT Project, [Project Category] AS Category
FROM [Project Descriptions 2000]
UNION SELECT Project, [Project Category2] AS Category
FROM [Project Descriptions 2000];

Table and field names that have spaces and special characters, ( # % @ ) &
etc., must be enclosed in brackets. Easiest to use underscore instead of
space.
--
KARL DEWEY
Build a little - Test a little

Karl,

Unfortunatly I am not too familar with the proper code for a union query. I
have tried several times composing a query based on what you wrote, but I
continue to get a syntex error on the FROM statement. Here is what I wrote:

SELECT Project, [Project Category] AS Category
FROM Project Descriptions 2000
SELECT Project, [Project Category2] AS Category
FROM Project Descriptions 2000;

If I understand your table structure correctly then use a union query --
SELECT Project, [Main Category] AS Category
FROM YourTable
SELECT Project, [Secondary Category] AS Category
FROM YourTable;

Then in your report group on Category.

--
KARL DEWEY
Build a little - Test a little

I am trying to create a report from a query. In that query I am showing
individual records representing Architectural Projects (i.e. McDonalds
Remodel, Bank One Remodel, State Farm Office Building, etc.), and each record
has been assigned a main category (i.e. Food Service, Finanical, Office
Planning). In some cases there are records with a secondary category (i.e.
Bank One Remodel is Financial, but also has Office Planning as a secondary
category). I want to have a single report lising all the projects in each
category with the secondary category incorporated into the main catagory
list. Therefore, the single record "Bank One Remodel" would show up in two
locations; the Financial list and the Office Planning list. Right now, I can
only get the single record to show up in the main category list.
Main Category Secondary
Category
Record1: McDonalds Remodel Food Service
Record2: Bank One Remodel Financial Office Planning
Record3: State Farm Office Bldg. Office Planning

The report I want should look like this:

Food Service:
McDonald's Remodel

Financial:
Bank One Remodel

Office Planning:
Bank One Remodel
State Farm Office Bldg.

Any help or direction would be appreciated. Thanks.

 

[macarch]

Karl - THANK YOU! I got it working. It is doing exactly what I need.

ERROR on my part --
SELECT Project, [Main Category] AS Category
FROM YourTable
UNION SELECT Project, [Secondary Category] AS Category
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little

If I understand your table structure correctly then use a union query --
SELECT Project, [Main Category] AS Category
FROM YourTable
SELECT Project, [Secondary Category] AS Category
FROM YourTable;

Then in your report group on Category.

--
KARL DEWEY
Build a little - Test a little

I am trying to create a report from a query. In that query I am showing
individual records representing Architectural Projects (i.e. McDonalds
Remodel, Bank One Remodel, State Farm Office Building, etc.), and each record
has been assigned a main category (i.e. Food Service, Finanical, Office
Planning). In some cases there are records with a secondary category (i.e.
Bank One Remodel is Financial, but also has Office Planning as a secondary
category). I want to have a single report lising all the projects in each
category with the secondary category incorporated into the main catagory
list. Therefore, the single record "Bank One Remodel" would show up in two
locations; the Financial list and the Office Planning list. Right now, I can
only get the single record to show up in the main category list.
Main Category Secondary
Category
Record1: McDonalds Remodel Food Service
Record2: Bank One Remodel Financial Office Planning
Record3: State Farm Office Bldg. Office Planning

The report I want should look like this:

Food Service:
McDonald's Remodel

Financial:
Bank One Remodel

Office Planning:
Bank One Remodel
State Farm Office Bldg.

Any help or direction would be appreciated. Thanks.

 

[Klatuu]

What KARL has helped you work around is an incorrect normalisation problem.
You have ignored the "repeating values" rule. That is, having two
categories in the same record. So, now what happens when you get a project
that would be in 3 categories? You go back and modify your table and all
your database objects?

The correct method would be to have a category table. But, because a
project can be in multiple categories and a category may have multiple
projects, you now have a many to many relationship. All many to many
relationships have to be resolved with a junction table. It needs to have
the primary key field values of the two tables it is resolving. That is:

tblProjectCategories
ProCatID - Autonumber - Primary key
ProjID - Long Integer - Foreign key to tblProject
CatID - Long Integer - Foreign key to tblCategory

Then you use queries to build lists however you need them.

Karl - THANK YOU! I got it working. It is doing exactly what I need.

ERROR on my part --
SELECT Project, [Main Category] AS Category
FROM YourTable
UNION SELECT Project, [Secondary Category] AS Category
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little

If I understand your table structure correctly then use a union
query --
SELECT Project, [Main Category] AS Category
FROM YourTable
SELECT Project, [Secondary Category] AS Category
FROM YourTable;

Then in your report group on Category.

--
KARL DEWEY
Build a little - Test a little


:

I am trying to create a report from a query. In that query I am
showing
individual records representing Architectural Projects (i.e.
McDonalds
Remodel, Bank One Remodel, State Farm Office Building, etc.), and
each record
has been assigned a main category (i.e. Food Service, Finanical,
Office
Planning). In some cases there are records with a secondary category
(i.e.
Bank One Remodel is Financial, but also has Office Planning as a
secondary
category). I want to have a single report lising all the projects in
each
category with the secondary category incorporated into the main
catagory
list. Therefore, the single record "Bank One Remodel" would show up
in two
locations; the Financial list and the Office Planning list. Right
now, I can
only get the single record to show up in the main category list.
Main Category
Secondary
Category
Record1: McDonalds Remodel Food Service
Record2: Bank One Remodel Financial Office
Planning
Record3: State Farm Office Bldg. Office Planning

The report I want should look like this:

Food Service:
McDonald's Remodel

Financial:
Bank One Remodel

Office Planning:
Bank One Remodel
State Farm Office Bldg.

Any help or direction would be appreciated. Thanks.