How to I combine 2 columns from 1 table in a query?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bga5c - 17 Nov 2005 16:11 GMT

I work for a publication that wants to keep track of all our stories, the
category they fall into, and the area of town that they cover. I have created
a simple table with all of the stories, categories, and areas, along with
other information (date, etc.) My problem is that some of the stories fall
into more than one category or area. In my table I have "Area1" and "Area2"
columns. I want to create a report with a pie chart that shoes the division
of the stories for the year by area. This means, I need to create query that
combiens the "Area1" an "Area2" columns into one "Area" column. I know that
some stories in this query would be duplicated. How should I go about this?
I'm brand new to Access, so very detailed help would be GREATLY appreciated!!

Reply to this Message

Amy Blankenship - 17 Nov 2005 16:31 GMT

You need to be asking this question in the tablesDBDesign newsgroup, since
at its heart this is a table design problem.

What you need is something like this

tblStory
StoryID -AutoNumber
StoryHeadline - txt
StoryDate - date
StoryContent - memo

tblAreas
AreaID - AutoNumber
AreaDesc - text

tblCategory
CategoryID - AutoNumber
CategoryDesc - text

tblStoryArea
StoryID - number
AreaID - number

tblStoryCategory
StoryID - number
CategoryID - number

Now you can assign as many categories or areas to a given story as needed.

HTH;

Amy

>I work for a publication that wants to keep track of all our stories, the
> category they fall into, and the area of town that they cover. I have
[quoted text clipped - 13 lines]
> I'm brand new to Access, so very detailed help would be GREATLY
> appreciated!!

Reply to this Message

John Spencer - 17 Nov 2005 17:19 GMT

If for some reason, you cannot redesign your tables. then you can use a
UNION query to normalize the view of the data. You can use this query as
the source for reports

You have to build a UNION query in the SQL Text window, but you can make
life easier by using the query grid to start.

Make a standard query of your table and include all the fields you want to
use, but include ONLY ONE of the Area fields _Area1. Once you have the
query set up, Select View:SQL from the menu
Copy all the text
Move to the bottom of the existing text and type

UNION ALL

Under that paste the text you just copied. In this new section, find all
references to Area1 and replace them with Area2
Click on the run button and see if that gives you what you are looking for.

> You need to be asking this question in the tablesDBDesign newsgroup, since
> at its heart this is a table design problem.

SNIP>

> Now you can assign as many categories or areas to a given story as needed.
>
[quoted text clipped - 19 lines]
>> I'm brand new to Access, so very detailed help would be GREATLY
>> appreciated!!

Reply to this Message