Charts and Queries

[P Fu]

Hi, I'm new at building queries in Access, so I would greatly
appreciate some help..

1. I have in a field for a query which calculates how old something
is--
Age: Year(Date())-[tblFacConstructedParts]![ConstructYear]
The problem here is that, some records have missing data so my
resulting table has '2003' as some of the ages which is clearly not
right. How do I fix this?

2. After I fix this problem, I have to create a chart where I need to
have 'bins' of ages.. like 0-10, 11-20, etc. When I do the chart, I
just get a bunch of lines. I don't know how to create these bins
where I can display how many buildings are in each age category. How
do I do this? Is there any way I can go about this?


Any help would be greatly appreciated, even general guidelines and
tips for creating queries and charts in Access. I keep getting
confused with SQL commands, so I just need help in general. Thanks in
advance!

Patricia

 

[Duane Hookom]

1) You have to tell us what you want for a value rather than 2003. This is
something that we can't guess.
2) You can use the Partition() function to create buckets of ranges. Open
any module window and search Help.

 

[P Fu]

1) I'd like to display "Incomplete Data" or "Unknown" when there is no
data, instead of it displaying 2003.
2) Thanks, I'll check that function out. While I was waiting, I went
the long way around the problem by exporting the data, analyzing in
Excel, then importing back into a report in a chart format. Is this
an ineffective/inefficient method to use? B/c it seems to work so
far..

Thanks!

1) You have to tell us what you want for a value rather than 2003. This is
something that we can't guess.
2) You can use the Partition() function to create buckets of ranges. Open
any module window and search Help.

--
Duane Hookom
MS Access MVP

Hi, I'm new at building queries in Access, so I would greatly
appreciate some help..

1. I have in a field for a query which calculates how old something
is--
Age: Year(Date())-[tblFacConstructedParts]![ConstructYear]
The problem here is that, some records have missing data so my
resulting table has '2003' as some of the ages which is clearly not
right. How do I fix this?

2. After I fix this problem, I have to create a chart where I need to
have 'bins' of ages.. like 0-10, 11-20, etc. When I do the chart, I
just get a bunch of lines. I don't know how to create these bins
where I can display how many buildings are in each age category. How
do I do this? Is there any way I can go about this?


Any help would be greatly appreciated, even general guidelines and
tips for creating queries and charts in Access. I keep getting
confused with SQL commands, so I just need help in general. Thanks in
advance!

Patricia

 

[Duane Hookom]

I would create a lookup table of Year Difference ranges.
tblAgeRanges
================
AgeID
FromAge numeric
ToAge Numeric
Title Text
Then add your range starts and ends as well as the Title you would like to
display in your report.
Add this table to your chart's Row Source query. Add the following under
your Age column
Between [FromAge] and [ToAge]
This allows you to add the Title field to use as your graph headings.

Duane Hookom
MS Access MVP

1) I'd like to display "Incomplete Data" or "Unknown" when there is no
data, instead of it displaying 2003.
2) Thanks, I'll check that function out. While I was waiting, I went
the long way around the problem by exporting the data, analyzing in
Excel, then importing back into a report in a chart format. Is this
an ineffective/inefficient method to use? B/c it seems to work so
far..

Thanks!

"Duane Hookom" <[email protected]> wrote in message

1) You have to tell us what you want for a value rather than 2003. This is
something that we can't guess.
2) You can use the Partition() function to create buckets of ranges. Open
any module window and search Help.

--
Duane Hookom
MS Access MVP

Hi, I'm new at building queries in Access, so I would greatly
appreciate some help..

1. I have in a field for a query which calculates how old something
is--
Age: Year(Date())-[tblFacConstructedParts]![ConstructYear]
The problem here is that, some records have missing data so my
resulting table has '2003' as some of the ages which is clearly not
right. How do I fix this?

2. After I fix this problem, I have to create a chart where I need to
have 'bins' of ages.. like 0-10, 11-20, etc. When I do the chart, I
just get a bunch of lines. I don't know how to create these bins
where I can display how many buildings are in each age category. How
do I do this? Is there any way I can go about this?


Any help would be greatly appreciated, even general guidelines and
tips for creating queries and charts in Access. I keep getting
confused with SQL commands, so I just need help in general. Thanks in
advance!

Patricia