A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

do I need more indexes?



 
 
Thread Tools Display Modes
  #11  
Old July 10th, 2009, 04:41 PM posted to microsoft.public.access.tablesdbdesign
Steve[_72_]
external usenet poster
 
Posts: 190
Default do I need more indexes?

I think the best method would be to restructure your data - something that
is implied by your use of the
union query to normalize the data you have. John Spencer Queries
newsgroup

John concurs with what I am trying to tell you!!

You did not post qSampleReview in the queries newsgroup.

If I am to help you, once again, please give a detailed description of what
you are trying to do in your database, What are ID, Strata and Sample in
tSample? What are the other fields in tSample? What are ReviewKey, Key,
A1,A2,B1,B2 in tReview? Are there other fields in tReview?

And by the way, if you are going to trust what Jeff Boyce is telling you
when he said nothing about the repetitive fields in tReview, then ignore all
the above because I won't help you!!!

Steve

"jmoore" wrote in message
...

Thanks for your offer. I posted the SQL for the queries in the queries
group. Each field in tReview is a rating of that criteria for a sample set
of
cases from tSample. There are a large number of cases in tSample that will
not be used. Each case in tReview is a row, and contains all of the
information for the complete review. A report for each county with a
summary
of their cases is produced during the year. These work fine. At the end
of
the year, an annual aggregate report is needed. It gives the information
we
need, but is very slow. I realize this is designed like a spreadsheet,
but
this has been in use for a year and I don't know how to change it, or
transfer all of the data to a new design. I hope this is enough detail. I
appreciate any advice you have.

"Steve" wrote:

You can significantly improve performance by a redesign of your tables.
Without knowing anything about your database, I can tell you the design
of
tReview is very wrong just looking at what you posted. Your query(s) have
got to be very complex hust to overcome the misdesign of tReview! Please
give a detailed description of what you are trying to do in your
database,
and I will be very glad to give you a suggested redesign of your tables.

Steve




"jmoore" wrote in message
...

After reading several posts here I have changed all of the lookup
fields
to
text fields in my table and deleted the relationships to the lookup
tables.
I came to this area looking for a solution to slow response time.
Again,
after reading several posts, I suspect it is due to the database
design,
but
it is what I have to work with.

Other than the lookup tables, there are only two related tables. If it
would be helpful, I can remove the lookup tables.
tSample (with the major fields listed below)
Key (primary key)
CNTYNAME
ID
Strata
Sample

tReview
ReviewKey (PK)
Key (FK)
A1
A2. . .
B1
B2. . .(in total there are over 60 number fields and 50 memo fields).

I read that creating indexes will increase response time. Will
creating a
unique index from two fields in addition to the PK increase response
time
(e.g., in tSample, a unique key comprised of CNTYNAME and ID)?

All was going well for the individual county reports, but I had
problems
when an aggregate report was needed at year end. I was able to solve
the
problem with advice obtained through this site. First, I created a
union
query (qUnion); a crosstab query based on the union query
(qUnionCrosstab);
and combined qUnionCrosstab and qReviewCountCrosstab into one query
(qCrosstabsCombined) for the aggregate report. The aggregate report is
extremely slow to open and painfully slow in design view.

Will it help to create additional indexes? If not, I can live with
this
report as it is since I only need it once a year. I don't know how to
change
the design and transfer the data.

Thanks for any tips for improvement. I am very grateful for the great
information I have found here.






  #12  
Old July 10th, 2009, 06:02 PM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default do I need more indexes?

"Steve" help_available_at_very_reasonable_rates@contactme .com wrote in
message ...

And by the way, if you are going to trust what Jeff Boyce is telling you
when he said nothing about the repetitive fields in tReview, then ignore
all the above because I won't help you!!!

Steve



oh please make it so. Since your intent has always been only to help
yourself, that is not a big promise.

John... Visio MVP

  #13  
Old July 11th, 2009, 04:23 PM posted to microsoft.public.access.tablesdbdesign
Steve[_72_]
external usenet poster
 
Posts: 190
Default do I need more indexes?

Is this within the code of conduct for an MVP? I think not. You ought to be
proud of yourself! You cost the OP a solution to his problem. BTW, did you
even bother to look at the OP's post in the queries newsgroup?

Steve



"Jeff Boyce" wrote in message
...
Before taking Steve up on his offer, you might want to google him and his
history in these newsgroups.

Remember, not all advice you receive is of equal quality/applicability.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"jmoore" wrote in message
...
Thanks for your offer. I posted the SQL for the queries in the queries
group. Each field in tReview is a rating of that criteria for a sample
set of
cases from tSample. There are a large number of cases in tSample that
will
not be used. Each case in tReview is a row, and contains all of the
information for the complete review. A report for each county with a
summary
of their cases is produced during the year. These work fine. At the end
of
the year, an annual aggregate report is needed. It gives the information
we
need, but is very slow. I realize this is designed like a spreadsheet,
but
this has been in use for a year and I don't know how to change it, or
transfer all of the data to a new design. I hope this is enough detail.
I
appreciate any advice you have.

"Steve" wrote:

You can significantly improve performance by a redesign of your tables.
Without knowing anything about your database, I can tell you the design
of
tReview is very wrong just looking at what you posted. Your query(s)
have
got to be very complex hust to overcome the misdesign of tReview! Please
give a detailed description of what you are trying to do in your
database,
and I will be very glad to give you a suggested redesign of your tables.

Steve




"jmoore" wrote in message
...

After reading several posts here I have changed all of the lookup
fields
to
text fields in my table and deleted the relationships to the lookup
tables.
I came to this area looking for a solution to slow response time.
Again,
after reading several posts, I suspect it is due to the database
design,
but
it is what I have to work with.

Other than the lookup tables, there are only two related tables. If
it
would be helpful, I can remove the lookup tables.
tSample (with the major fields listed below)
Key (primary key)
CNTYNAME
ID
Strata
Sample

tReview
ReviewKey (PK)
Key (FK)
A1
A2. . .
B1
B2. . .(in total there are over 60 number fields and 50 memo fields).

I read that creating indexes will increase response time. Will
creating a
unique index from two fields in addition to the PK increase response
time
(e.g., in tSample, a unique key comprised of CNTYNAME and ID)?

All was going well for the individual county reports, but I had
problems
when an aggregate report was needed at year end. I was able to solve
the
problem with advice obtained through this site. First, I created a
union
query (qUnion); a crosstab query based on the union query
(qUnionCrosstab);
and combined qUnionCrosstab and qReviewCountCrosstab into one query
(qCrosstabsCombined) for the aggregate report. The aggregate report
is
extremely slow to open and painfully slow in design view.

Will it help to create additional indexes? If not, I can live with
this
report as it is since I only need it once a year. I don't know how to
change
the design and transfer the data.

Thanks for any tips for improvement. I am very grateful for the great
information I have found here.







  #14  
Old July 11th, 2009, 05:05 PM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default do I need more indexes?

The truth hurts doesn't it stevie?

Nothing wrong with what Jeff posted. It was a basic caveat emptor.

John... Visio MVP
"Steve" help_available_at_very_reasonable_rates@contactme .com wrote in
message m...
Is this within the code of conduct for an MVP? I think not. You ought to
be proud of yourself! You cost the OP a solution to his problem. BTW, did
you even bother to look at the OP's post in the queries newsgroup?

Steve



"Jeff Boyce" wrote in message
...
Before taking Steve up on his offer, you might want to google him and his
history in these newsgroups.

Remember, not all advice you receive is of equal quality/applicability.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"jmoore" wrote in message
...
Thanks for your offer. I posted the SQL for the queries in the queries
group. Each field in tReview is a rating of that criteria for a sample
set of
cases from tSample. There are a large number of cases in tSample that
will
not be used. Each case in tReview is a row, and contains all of the
information for the complete review. A report for each county with a
summary
of their cases is produced during the year. These work fine. At the
end of
the year, an annual aggregate report is needed. It gives the information
we
need, but is very slow. I realize this is designed like a spreadsheet,
but
this has been in use for a year and I don't know how to change it, or
transfer all of the data to a new design. I hope this is enough detail.
I
appreciate any advice you have.

"Steve" wrote:

You can significantly improve performance by a redesign of your tables.
Without knowing anything about your database, I can tell you the design
of
tReview is very wrong just looking at what you posted. Your query(s)
have
got to be very complex hust to overcome the misdesign of tReview!
Please
give a detailed description of what you are trying to do in your
database,
and I will be very glad to give you a suggested redesign of your
tables.

Steve




"jmoore" wrote in message
...

After reading several posts here I have changed all of the lookup
fields
to
text fields in my table and deleted the relationships to the lookup
tables.
I came to this area looking for a solution to slow response time.
Again,
after reading several posts, I suspect it is due to the database
design,
but
it is what I have to work with.

Other than the lookup tables, there are only two related tables. If
it
would be helpful, I can remove the lookup tables.
tSample (with the major fields listed below)
Key (primary key)
CNTYNAME
ID
Strata
Sample

tReview
ReviewKey (PK)
Key (FK)
A1
A2. . .
B1
B2. . .(in total there are over 60 number fields and 50 memo fields).

I read that creating indexes will increase response time. Will
creating a
unique index from two fields in addition to the PK increase response
time
(e.g., in tSample, a unique key comprised of CNTYNAME and ID)?

All was going well for the individual county reports, but I had
problems
when an aggregate report was needed at year end. I was able to solve
the
problem with advice obtained through this site. First, I created a
union
query (qUnion); a crosstab query based on the union query
(qUnionCrosstab);
and combined qUnionCrosstab and qReviewCountCrosstab into one query
(qCrosstabsCombined) for the aggregate report. The aggregate report
is
extremely slow to open and painfully slow in design view.

Will it help to create additional indexes? If not, I can live with
this
report as it is since I only need it once a year. I don't know how
to
change
the design and transfer the data.

Thanks for any tips for improvement. I am very grateful for the
great
information I have found here.









  #15  
Old July 13th, 2009, 03:04 PM posted to microsoft.public.access.tablesdbdesign
BruceM[_4_]
external usenet poster
 
Posts: 558
Default do I need more indexes?

Your history puts people on guard. You did not specifically try to get
money from the OP *now*, but your past trolling for work leads to the
suspicion that your offer to redesign will morph into a solicitation for
payment.

"Steve" help_available_at_very_reasonable_rates@contactme .com wrote in
message ...
I think the best method would be to restructure your data - something
that is implied by your use of the
union query to normalize the data you have. John Spencer Queries
newsgroup

John concurs with what I am trying to tell you!!

You did not post qSampleReview in the queries newsgroup.

If I am to help you, once again, please give a detailed description of
what you are trying to do in your database, What are ID, Strata and Sample
in tSample? What are the other fields in tSample? What are ReviewKey, Key,
A1,A2,B1,B2 in tReview? Are there other fields in tReview?

And by the way, if you are going to trust what Jeff Boyce is telling you
when he said nothing about the repetitive fields in tReview, then ignore
all the above because I won't help you!!!

Steve

"jmoore" wrote in message
...

Thanks for your offer. I posted the SQL for the queries in the queries
group. Each field in tReview is a rating of that criteria for a sample
set of
cases from tSample. There are a large number of cases in tSample that
will
not be used. Each case in tReview is a row, and contains all of the
information for the complete review. A report for each county with a
summary
of their cases is produced during the year. These work fine. At the end
of
the year, an annual aggregate report is needed. It gives the information
we
need, but is very slow. I realize this is designed like a spreadsheet,
but
this has been in use for a year and I don't know how to change it, or
transfer all of the data to a new design. I hope this is enough detail.
I
appreciate any advice you have.

"Steve" wrote:

You can significantly improve performance by a redesign of your tables.
Without knowing anything about your database, I can tell you the design
of
tReview is very wrong just looking at what you posted. Your query(s)
have
got to be very complex hust to overcome the misdesign of tReview! Please
give a detailed description of what you are trying to do in your
database,
and I will be very glad to give you a suggested redesign of your tables.

Steve




"jmoore" wrote in message
...

After reading several posts here I have changed all of the lookup
fields
to
text fields in my table and deleted the relationships to the lookup
tables.
I came to this area looking for a solution to slow response time.
Again,
after reading several posts, I suspect it is due to the database
design,
but
it is what I have to work with.

Other than the lookup tables, there are only two related tables. If
it
would be helpful, I can remove the lookup tables.
tSample (with the major fields listed below)
Key (primary key)
CNTYNAME
ID
Strata
Sample

tReview
ReviewKey (PK)
Key (FK)
A1
A2. . .
B1
B2. . .(in total there are over 60 number fields and 50 memo fields).

I read that creating indexes will increase response time. Will
creating a
unique index from two fields in addition to the PK increase response
time
(e.g., in tSample, a unique key comprised of CNTYNAME and ID)?

All was going well for the individual county reports, but I had
problems
when an aggregate report was needed at year end. I was able to solve
the
problem with advice obtained through this site. First, I created a
union
query (qUnion); a crosstab query based on the union query
(qUnionCrosstab);
and combined qUnionCrosstab and qReviewCountCrosstab into one query
(qCrosstabsCombined) for the aggregate report. The aggregate report
is
extremely slow to open and painfully slow in design view.

Will it help to create additional indexes? If not, I can live with
this
report as it is since I only need it once a year. I don't know how to
change
the design and transfer the data.

Thanks for any tips for improvement. I am very grateful for the great
information I have found here.







  #16  
Old July 13th, 2009, 04:36 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default do I need more indexes?

Steve

Sorry if you took umbrage at my suggestion.

I merely reminded the poster that not everyone's advice is of equal quality.
I would thing the code of conduct governing MVPs would encourage thoughtful
and informed consideration.

But as long as we're on the topic you raised, code of conduct, how do you
justify ignoring the newsgroup etiquette that precludes folks from
soliciting paid work?

Jeff

"Steve" help_available_at_very_reasonable_rates@contactme .com wrote in
message m...
Is this within the code of conduct for an MVP? I think not. You ought to
be proud of yourself! You cost the OP a solution to his problem. BTW, did
you even bother to look at the OP's post in the queries newsgroup?

Steve



"Jeff Boyce" wrote in message
...
Before taking Steve up on his offer, you might want to google him and his
history in these newsgroups.

Remember, not all advice you receive is of equal quality/applicability.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"jmoore" wrote in message
...
Thanks for your offer. I posted the SQL for the queries in the queries
group. Each field in tReview is a rating of that criteria for a sample
set of
cases from tSample. There are a large number of cases in tSample that
will
not be used. Each case in tReview is a row, and contains all of the
information for the complete review. A report for each county with a
summary
of their cases is produced during the year. These work fine. At the
end of
the year, an annual aggregate report is needed. It gives the information
we
need, but is very slow. I realize this is designed like a spreadsheet,
but
this has been in use for a year and I don't know how to change it, or
transfer all of the data to a new design. I hope this is enough detail.
I
appreciate any advice you have.

"Steve" wrote:

You can significantly improve performance by a redesign of your tables.
Without knowing anything about your database, I can tell you the design
of
tReview is very wrong just looking at what you posted. Your query(s)
have
got to be very complex hust to overcome the misdesign of tReview!
Please
give a detailed description of what you are trying to do in your
database,
and I will be very glad to give you a suggested redesign of your
tables.

Steve




"jmoore" wrote in message
...

After reading several posts here I have changed all of the lookup
fields
to
text fields in my table and deleted the relationships to the lookup
tables.
I came to this area looking for a solution to slow response time.
Again,
after reading several posts, I suspect it is due to the database
design,
but
it is what I have to work with.

Other than the lookup tables, there are only two related tables. If
it
would be helpful, I can remove the lookup tables.
tSample (with the major fields listed below)
Key (primary key)
CNTYNAME
ID
Strata
Sample

tReview
ReviewKey (PK)
Key (FK)
A1
A2. . .
B1
B2. . .(in total there are over 60 number fields and 50 memo fields).

I read that creating indexes will increase response time. Will
creating a
unique index from two fields in addition to the PK increase response
time
(e.g., in tSample, a unique key comprised of CNTYNAME and ID)?

All was going well for the individual county reports, but I had
problems
when an aggregate report was needed at year end. I was able to solve
the
problem with advice obtained through this site. First, I created a
union
query (qUnion); a crosstab query based on the union query
(qUnionCrosstab);
and combined qUnionCrosstab and qReviewCountCrosstab into one query
(qCrosstabsCombined) for the aggregate report. The aggregate report
is
extremely slow to open and painfully slow in design view.

Will it help to create additional indexes? If not, I can live with
this
report as it is since I only need it once a year. I don't know how
to
change
the design and transfer the data.

Thanks for any tips for improvement. I am very grateful for the
great
information I have found here.









  #17  
Old July 13th, 2009, 05:26 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default do I need more indexes?

correction ... "I would think ..."

Jeff

"Jeff Boyce" wrote in message
...
Steve

Sorry if you took umbrage at my suggestion.

I merely reminded the poster that not everyone's advice is of equal
quality. I would thing the code of conduct governing MVPs would encourage
thoughtful and informed consideration.

But as long as we're on the topic you raised, code of conduct, how do you
justify ignoring the newsgroup etiquette that precludes folks from
soliciting paid work?

Jeff

"Steve" help_available_at_very_reasonable_rates@contactme .com wrote in
message m...
Is this within the code of conduct for an MVP? I think not. You ought to
be proud of yourself! You cost the OP a solution to his problem. BTW, did
you even bother to look at the OP's post in the queries newsgroup?

Steve



"Jeff Boyce" wrote in message
...
Before taking Steve up on his offer, you might want to google him and
his history in these newsgroups.

Remember, not all advice you receive is of equal quality/applicability.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"jmoore" wrote in message
...
Thanks for your offer. I posted the SQL for the queries in the queries
group. Each field in tReview is a rating of that criteria for a sample
set of
cases from tSample. There are a large number of cases in tSample that
will
not be used. Each case in tReview is a row, and contains all of the
information for the complete review. A report for each county with a
summary
of their cases is produced during the year. These work fine. At the
end of
the year, an annual aggregate report is needed. It gives the
information we
need, but is very slow. I realize this is designed like a spreadsheet,
but
this has been in use for a year and I don't know how to change it, or
transfer all of the data to a new design. I hope this is enough
detail. I
appreciate any advice you have.

"Steve" wrote:

You can significantly improve performance by a redesign of your
tables.
Without knowing anything about your database, I can tell you the
design of
tReview is very wrong just looking at what you posted. Your query(s)
have
got to be very complex hust to overcome the misdesign of tReview!
Please
give a detailed description of what you are trying to do in your
database,
and I will be very glad to give you a suggested redesign of your
tables.

Steve




"jmoore" wrote in message
...

After reading several posts here I have changed all of the lookup
fields
to
text fields in my table and deleted the relationships to the lookup
tables.
I came to this area looking for a solution to slow response time.
Again,
after reading several posts, I suspect it is due to the database
design,
but
it is what I have to work with.

Other than the lookup tables, there are only two related tables. If
it
would be helpful, I can remove the lookup tables.
tSample (with the major fields listed below)
Key (primary key)
CNTYNAME
ID
Strata
Sample

tReview
ReviewKey (PK)
Key (FK)
A1
A2. . .
B1
B2. . .(in total there are over 60 number fields and 50 memo
fields).

I read that creating indexes will increase response time. Will
creating a
unique index from two fields in addition to the PK increase response
time
(e.g., in tSample, a unique key comprised of CNTYNAME and ID)?

All was going well for the individual county reports, but I had
problems
when an aggregate report was needed at year end. I was able to
solve the
problem with advice obtained through this site. First, I created a
union
query (qUnion); a crosstab query based on the union query
(qUnionCrosstab);
and combined qUnionCrosstab and qReviewCountCrosstab into one query
(qCrosstabsCombined) for the aggregate report. The aggregate report
is
extremely slow to open and painfully slow in design view.

Will it help to create additional indexes? If not, I can live with
this
report as it is since I only need it once a year. I don't know how
to
change
the design and transfer the data.

Thanks for any tips for improvement. I am very grateful for the
great
information I have found here.











  #18  
Old July 14th, 2009, 06:18 PM posted to microsoft.public.access.tablesdbdesign
Steve[_72_]
external usenet poster
 
Posts: 190
Default do I need more indexes?

the newsgroup etiquette that precludes folks from soliciting paid work?

Where specifically is this?



"Jeff Boyce" wrote in message
...
Steve

Sorry if you took umbrage at my suggestion.

I merely reminded the poster that not everyone's advice is of equal
quality. I would thing the code of conduct governing MVPs would encourage
thoughtful and informed consideration.

But as long as we're on the topic you raised, code of conduct, how do you
justify ignoring the newsgroup etiquette that precludes folks from
soliciting paid work?

Jeff

"Steve" help_available_at_very_reasonable_rates@contactme .com wrote in
message m...
Is this within the code of conduct for an MVP? I think not. You ought to
be proud of yourself! You cost the OP a solution to his problem. BTW, did
you even bother to look at the OP's post in the queries newsgroup?

Steve



"Jeff Boyce" wrote in message
...
Before taking Steve up on his offer, you might want to google him and
his history in these newsgroups.

Remember, not all advice you receive is of equal quality/applicability.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"jmoore" wrote in message
...
Thanks for your offer. I posted the SQL for the queries in the queries
group. Each field in tReview is a rating of that criteria for a sample
set of
cases from tSample. There are a large number of cases in tSample that
will
not be used. Each case in tReview is a row, and contains all of the
information for the complete review. A report for each county with a
summary
of their cases is produced during the year. These work fine. At the
end of
the year, an annual aggregate report is needed. It gives the
information we
need, but is very slow. I realize this is designed like a spreadsheet,
but
this has been in use for a year and I don't know how to change it, or
transfer all of the data to a new design. I hope this is enough
detail. I
appreciate any advice you have.

"Steve" wrote:

You can significantly improve performance by a redesign of your
tables.
Without knowing anything about your database, I can tell you the
design of
tReview is very wrong just looking at what you posted. Your query(s)
have
got to be very complex hust to overcome the misdesign of tReview!
Please
give a detailed description of what you are trying to do in your
database,
and I will be very glad to give you a suggested redesign of your
tables.

Steve




"jmoore" wrote in message
...

After reading several posts here I have changed all of the lookup
fields
to
text fields in my table and deleted the relationships to the lookup
tables.
I came to this area looking for a solution to slow response time.
Again,
after reading several posts, I suspect it is due to the database
design,
but
it is what I have to work with.

Other than the lookup tables, there are only two related tables. If
it
would be helpful, I can remove the lookup tables.
tSample (with the major fields listed below)
Key (primary key)
CNTYNAME
ID
Strata
Sample

tReview
ReviewKey (PK)
Key (FK)
A1
A2. . .
B1
B2. . .(in total there are over 60 number fields and 50 memo
fields).

I read that creating indexes will increase response time. Will
creating a
unique index from two fields in addition to the PK increase response
time
(e.g., in tSample, a unique key comprised of CNTYNAME and ID)?

All was going well for the individual county reports, but I had
problems
when an aggregate report was needed at year end. I was able to
solve the
problem with advice obtained through this site. First, I created a
union
query (qUnion); a crosstab query based on the union query
(qUnionCrosstab);
and combined qUnionCrosstab and qReviewCountCrosstab into one query
(qCrosstabsCombined) for the aggregate report. The aggregate report
is
extremely slow to open and painfully slow in design view.

Will it help to create additional indexes? If not, I can live with
this
report as it is since I only need it once a year. I don't know how
to
change
the design and transfer the data.

Thanks for any tips for improvement. I am very grateful for the
great
information I have found here.











  #19  
Old July 14th, 2009, 06:36 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default do I need more indexes?

Are you saying that you'll abide by it if it's written?

"Steve" help_available_at_very_reasonable_rates@contactme .com wrote in
message news
the newsgroup etiquette that precludes folks from soliciting paid
work?

Where specifically is this?



"Jeff Boyce" wrote in message
...
Steve

Sorry if you took umbrage at my suggestion.

I merely reminded the poster that not everyone's advice is of equal
quality. I would thing the code of conduct governing MVPs would encourage
thoughtful and informed consideration.

But as long as we're on the topic you raised, code of conduct, how do you
justify ignoring the newsgroup etiquette that precludes folks from
soliciting paid work?

Jeff

"Steve" help_available_at_very_reasonable_rates@contactme .com wrote in
message m...
Is this within the code of conduct for an MVP? I think not. You ought to
be proud of yourself! You cost the OP a solution to his problem. BTW,
did you even bother to look at the OP's post in the queries newsgroup?

Steve



"Jeff Boyce" wrote in message
...
Before taking Steve up on his offer, you might want to google him and
his history in these newsgroups.

Remember, not all advice you receive is of equal quality/applicability.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"jmoore" wrote in message
...
Thanks for your offer. I posted the SQL for the queries in the
queries
group. Each field in tReview is a rating of that criteria for a sample
set of
cases from tSample. There are a large number of cases in tSample that
will
not be used. Each case in tReview is a row, and contains all of the
information for the complete review. A report for each county with a
summary
of their cases is produced during the year. These work fine. At the
end of
the year, an annual aggregate report is needed. It gives the
information we
need, but is very slow. I realize this is designed like a
spreadsheet, but
this has been in use for a year and I don't know how to change it, or
transfer all of the data to a new design. I hope this is enough
detail. I
appreciate any advice you have.

"Steve" wrote:

You can significantly improve performance by a redesign of your
tables.
Without knowing anything about your database, I can tell you the
design of
tReview is very wrong just looking at what you posted. Your query(s)
have
got to be very complex hust to overcome the misdesign of tReview!
Please
give a detailed description of what you are trying to do in your
database,
and I will be very glad to give you a suggested redesign of your
tables.

Steve




"jmoore" wrote in message
...

After reading several posts here I have changed all of the lookup
fields
to
text fields in my table and deleted the relationships to the lookup
tables.
I came to this area looking for a solution to slow response time.
Again,
after reading several posts, I suspect it is due to the database
design,
but
it is what I have to work with.

Other than the lookup tables, there are only two related tables.
If it
would be helpful, I can remove the lookup tables.
tSample (with the major fields listed below)
Key (primary key)
CNTYNAME
ID
Strata
Sample

tReview
ReviewKey (PK)
Key (FK)
A1
A2. . .
B1
B2. . .(in total there are over 60 number fields and 50 memo
fields).

I read that creating indexes will increase response time. Will
creating a
unique index from two fields in addition to the PK increase
response time
(e.g., in tSample, a unique key comprised of CNTYNAME and ID)?

All was going well for the individual county reports, but I had
problems
when an aggregate report was needed at year end. I was able to
solve the
problem with advice obtained through this site. First, I created a
union
query (qUnion); a crosstab query based on the union query
(qUnionCrosstab);
and combined qUnionCrosstab and qReviewCountCrosstab into one query
(qCrosstabsCombined) for the aggregate report. The aggregate
report is
extremely slow to open and painfully slow in design view.

Will it help to create additional indexes? If not, I can live with
this
report as it is since I only need it once a year. I don't know how
to
change
the design and transfer the data.

Thanks for any tips for improvement. I am very grateful for the
great
information I have found here.













  #20  
Old July 14th, 2009, 08:30 PM posted to microsoft.public.access.tablesdbdesign
Steve[_72_]
external usenet poster
 
Posts: 190
Default do I need more indexes?

Show where it is written and cite the source.


"Jeff Boyce" wrote in message
...
Are you saying that you'll abide by it if it's written?

"Steve" help_available_at_very_reasonable_rates@contactme .com wrote in
message news
the newsgroup etiquette that precludes folks from soliciting paid
work?

Where specifically is this?



"Jeff Boyce" wrote in message
...
Steve

Sorry if you took umbrage at my suggestion.

I merely reminded the poster that not everyone's advice is of equal
quality. I would thing the code of conduct governing MVPs would
encourage thoughtful and informed consideration.

But as long as we're on the topic you raised, code of conduct, how do
you justify ignoring the newsgroup etiquette that precludes folks from
soliciting paid work?

Jeff

"Steve" help_available_at_very_reasonable_rates@contactme .com wrote in
message m...
Is this within the code of conduct for an MVP? I think not. You ought
to be proud of yourself! You cost the OP a solution to his problem.
BTW, did you even bother to look at the OP's post in the queries
newsgroup?

Steve



"Jeff Boyce" wrote in message
...
Before taking Steve up on his offer, you might want to google him and
his history in these newsgroups.

Remember, not all advice you receive is of equal
quality/applicability.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"jmoore" wrote in message
...
Thanks for your offer. I posted the SQL for the queries in the
queries
group. Each field in tReview is a rating of that criteria for a
sample set of
cases from tSample. There are a large number of cases in tSample that
will
not be used. Each case in tReview is a row, and contains all of the
information for the complete review. A report for each county with a
summary
of their cases is produced during the year. These work fine. At the
end of
the year, an annual aggregate report is needed. It gives the
information we
need, but is very slow. I realize this is designed like a
spreadsheet, but
this has been in use for a year and I don't know how to change it, or
transfer all of the data to a new design. I hope this is enough
detail. I
appreciate any advice you have.

"Steve" wrote:

You can significantly improve performance by a redesign of your
tables.
Without knowing anything about your database, I can tell you the
design of
tReview is very wrong just looking at what you posted. Your query(s)
have
got to be very complex hust to overcome the misdesign of tReview!
Please
give a detailed description of what you are trying to do in your
database,
and I will be very glad to give you a suggested redesign of your
tables.

Steve




"jmoore" wrote in message
...

After reading several posts here I have changed all of the lookup
fields
to
text fields in my table and deleted the relationships to the
lookup
tables.
I came to this area looking for a solution to slow response time.
Again,
after reading several posts, I suspect it is due to the database
design,
but
it is what I have to work with.

Other than the lookup tables, there are only two related tables.
If it
would be helpful, I can remove the lookup tables.
tSample (with the major fields listed below)
Key (primary key)
CNTYNAME
ID
Strata
Sample

tReview
ReviewKey (PK)
Key (FK)
A1
A2. . .
B1
B2. . .(in total there are over 60 number fields and 50 memo
fields).

I read that creating indexes will increase response time. Will
creating a
unique index from two fields in addition to the PK increase
response time
(e.g., in tSample, a unique key comprised of CNTYNAME and ID)?

All was going well for the individual county reports, but I had
problems
when an aggregate report was needed at year end. I was able to
solve the
problem with advice obtained through this site. First, I created
a union
query (qUnion); a crosstab query based on the union query
(qUnionCrosstab);
and combined qUnionCrosstab and qReviewCountCrosstab into one
query
(qCrosstabsCombined) for the aggregate report. The aggregate
report is
extremely slow to open and painfully slow in design view.

Will it help to create additional indexes? If not, I can live
with this
report as it is since I only need it once a year. I don't know
how to
change
the design and transfer the data.

Thanks for any tips for improvement. I am very grateful for the
great
information I have found here.















 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.