Creating an index in a query

[Michelle]

Hello, I'm a beginner, so I don't know if this is possible. I have a query
that is pulling data from a read-only table. The two fields that I'm using
are a loan number field and a counter field. There are duplicate loan
numbers, but every record in the counter is unique. I used the Maximum
function to get the maximum count number for each loan. It works, but it goes
very slow.

I thought I could speed it up if I created an index for the loan number.
But, since the table I'm pulling from is read-only, can I do it in the query
instead?

Thanks,
Michelle

 

[Jeff Boyce]

Michelle

As far as I know, indexes exist at the table level, not the query level.

But even if the table is being used as "read-only", what about setting the
index in the table?

Am I missing something?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jerry Whittle]

You can't create an index using a select query. As the table is read only, I
doubt that you could add an index using an alter or create statement either.
You probably need to talk to who owns the table and see if they can.

Where is this table located? Are you getting to it through an ODBC
connection or is it a linked table? It's possible that you might be able to
use a pass-through query if the table is in another database management
system such as SQL Server or Oracle.

How slow is it? Second? Minutes? Hours?

What is the SQL statement for the query? Open the query in design view. Next
go to View, SQL View and copy and past it here. Maybe there's a way to speed
up that query.

 

[Michelle]

I changed the query from a Select Query to a Make-Table Query and created an
index in the new table after running it. I'm now trying to make a second
query that pulls from this update table. Will that work?

It is going much faster now, but now I can't get the Max function to work on
the counter...it's giving me duplicate loan numbers still when I just want
the record for each loan number with the highest count.

If it helps, here is the code for my first query (the Make-Table Query):

SELECT dbo_Q_FieldLogging.Key1, dbo_Q_FieldLogging.Counter,
dbo_Q_FieldLogging.DateChanged, dbo_Q_FieldLogging.FieldID,
dbo_Q_FieldLogging.OldValue, dbo_Q_FieldLogging.NewValue INTO T_Q_FieldLogging
FROM dbo_Q_FieldLogging
WHERE (((dbo_Q_FieldLogging.Counter)>10000000) AND
((dbo_Q_FieldLogging.FieldID)=1253))
ORDER BY dbo_Q_FieldLogging.Counter;

And here is the code for the second query:

SELECT T_LoanID.LoanID, Max(T_Q_FieldLogging.Counter) AS MaxOfCounter,
CDate([DateChanged]) AS Date_Changed, T_Q_FieldLogging.FieldID,
CDate([OldValue]) AS Old_Due, CDate([NewValue]) AS New_Due
FROM T_LoanID LEFT JOIN T_Q_FieldLogging ON T_LoanID.LoanID =
T_Q_FieldLogging.Key1
GROUP BY T_LoanID.LoanID, CDate([DateChanged]), T_Q_FieldLogging.FieldID,
CDate([OldValue]), CDate([NewValue]);

I might not be making them the proper way. I haven't been formally trained
to work with databases, so I just keep trying random things until something
finally works.

Thanks,
Michelle

 

[Michelle]

Also, Key1 is the Field Logging Table's name for Loan ID. I didn't know if
that was made obvious by my SQL or not.

I changed the query from a Select Query to a Make-Table Query and created an
index in the new table after running it. I'm now trying to make a second
query that pulls from this update table. Will that work?

It is going much faster now, but now I can't get the Max function to work on
the counter...it's giving me duplicate loan numbers still when I just want
the record for each loan number with the highest count.

If it helps, here is the code for my first query (the Make-Table Query):

SELECT dbo_Q_FieldLogging.Key1, dbo_Q_FieldLogging.Counter,
dbo_Q_FieldLogging.DateChanged, dbo_Q_FieldLogging.FieldID,
dbo_Q_FieldLogging.OldValue, dbo_Q_FieldLogging.NewValue INTO T_Q_FieldLogging
FROM dbo_Q_FieldLogging
WHERE (((dbo_Q_FieldLogging.Counter)>10000000) AND
((dbo_Q_FieldLogging.FieldID)=1253))
ORDER BY dbo_Q_FieldLogging.Counter;

And here is the code for the second query:

SELECT T_LoanID.LoanID, Max(T_Q_FieldLogging.Counter) AS MaxOfCounter,
CDate([DateChanged]) AS Date_Changed, T_Q_FieldLogging.FieldID,
CDate([OldValue]) AS Old_Due, CDate([NewValue]) AS New_Due
FROM T_LoanID LEFT JOIN T_Q_FieldLogging ON T_LoanID.LoanID =
T_Q_FieldLogging.Key1
GROUP BY T_LoanID.LoanID, CDate([DateChanged]), T_Q_FieldLogging.FieldID,
CDate([OldValue]), CDate([NewValue]);

I might not be making them the proper way. I haven't been formally trained
to work with databases, so I just keep trying random things until something
finally works.

Thanks,
Michelle

You can't create an index using a select query. As the table is read only, I
doubt that you could add an index using an alter or create statement either.
You probably need to talk to who owns the table and see if they can.

Where is this table located? Are you getting to it through an ODBC
connection or is it a linked table? It's possible that you might be able to
use a pass-through query if the table is in another database management
system such as SQL Server or Oracle.

How slow is it? Second? Minutes? Hours?

What is the SQL statement for the query? Open the query in design view. Next
go to View, SQL View and copy and past it here. Maybe there's a way to speed
up that query.

 

[Michelle]

When I open the table in design view, I get a message in the index field that
says: "This property cannot be modified in linked tables." I'm not sure if
there is a way around that.

I think I'll try to do this a different way.

 

[Jerry Whittle]

I'd be surprised if doing the make table query; creating an index; then
running a report would be faster from start to finish unless the data is
static and you don't need to recreate the table.

Make sure that both the T_LoanID.LoanID and T_Q_FieldLogging.Key1 fields are
indexed. They probably are Access automatically indexes any field with ID,
key, code, or num in the field name. In fact you may have more than one index
if either are the primary key or you created an index on them. Having too
many indexes can slow things down also.

Using functions like CDate will really slow thing down. Indexes won't work
on something that has a function in it. Well at least in Access. If it's an
Oracle database holding the table, there are some tricks....

As written, you could return the loan number with the highest count; however
you'll need to get rid of all the other fields in the group by.

Is there a primary key in the T_LoanID field? If so you could use a subquery
to get the info that you want. Even an autonumber field would do the trick.




--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Also, Key1 is the Field Logging Table's name for Loan ID. I didn't know if
that was made obvious by my SQL or not.

I changed the query from a Select Query to a Make-Table Query and created an
index in the new table after running it. I'm now trying to make a second
query that pulls from this update table. Will that work?

It is going much faster now, but now I can't get the Max function to work on
the counter...it's giving me duplicate loan numbers still when I just want
the record for each loan number with the highest count.

If it helps, here is the code for my first query (the Make-Table Query):

SELECT dbo_Q_FieldLogging.Key1, dbo_Q_FieldLogging.Counter,
dbo_Q_FieldLogging.DateChanged, dbo_Q_FieldLogging.FieldID,
dbo_Q_FieldLogging.OldValue, dbo_Q_FieldLogging.NewValue INTO T_Q_FieldLogging
FROM dbo_Q_FieldLogging
WHERE (((dbo_Q_FieldLogging.Counter)>10000000) AND
((dbo_Q_FieldLogging.FieldID)=1253))
ORDER BY dbo_Q_FieldLogging.Counter;

And here is the code for the second query:

SELECT T_LoanID.LoanID, Max(T_Q_FieldLogging.Counter) AS MaxOfCounter,
CDate([DateChanged]) AS Date_Changed, T_Q_FieldLogging.FieldID,
CDate([OldValue]) AS Old_Due, CDate([NewValue]) AS New_Due
FROM T_LoanID LEFT JOIN T_Q_FieldLogging ON T_LoanID.LoanID =
T_Q_FieldLogging.Key1
GROUP BY T_LoanID.LoanID, CDate([DateChanged]), T_Q_FieldLogging.FieldID,
CDate([OldValue]), CDate([NewValue]);

I might not be making them the proper way. I haven't been formally trained
to work with databases, so I just keep trying random things until something
finally works.

Thanks,
Michelle

You can't create an index using a select query. As the table is read only, I
doubt that you could add an index using an alter or create statement either.
You probably need to talk to who owns the table and see if they can.

Where is this table located? Are you getting to it through an ODBC
connection or is it a linked table? It's possible that you might be able to
use a pass-through query if the table is in another database management
system such as SQL Server or Oracle.

How slow is it? Second? Minutes? Hours?

What is the SQL statement for the query? Open the query in design view. Next
go to View, SQL View and copy and past it here. Maybe there's a way to speed
up that query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hello, I'm a beginner, so I don't know if this is possible. I have a query
that is pulling data from a read-only table. The two fields that I'm using
are a loan number field and a counter field. There are duplicate loan
numbers, but every record in the counter is unique. I used the Maximum
function to get the maximum count number for each loan. It works, but it goes
very slow.

I thought I could speed it up if I created an index for the loan number.
But, since the table I'm pulling from is read-only, can I do it in the query
instead?

Thanks,
Michelle

 

[Michelle]

The Make-Table query is actually working much faster and I finally got the
Max function to work, too. Yay! When I queried the read-only table by itself
and ran it without any criteria or functions, it was fast. But, I need all
that criteria and those functions, because there are over 10,000,000 records
and I only need certain records for certain loans in my end query. I used the
CDate function because I use the date as criteria for another date field in
another query down the line. I couldn't get the date to work as it was
without the CDate function.

In the read-only table, the Counter field was indexed, but the Key1 field
wasn't. Which is strange to me...I guess the person who made the table didn't
need that field to be indexed. Anyway, thanks for your help! I always learn a
lot when I ask questions here.

I'd be surprised if doing the make table query; creating an index; then
running a report would be faster from start to finish unless the data is
static and you don't need to recreate the table.

Make sure that both the T_LoanID.LoanID and T_Q_FieldLogging.Key1 fields are
indexed. They probably are Access automatically indexes any field with ID,
key, code, or num in the field name. In fact you may have more than one index
if either are the primary key or you created an index on them. Having too
many indexes can slow things down also.

Using functions like CDate will really slow thing down. Indexes won't work
on something that has a function in it. Well at least in Access. If it's an
Oracle database holding the table, there are some tricks....

As written, you could return the loan number with the highest count; however
you'll need to get rid of all the other fields in the group by.

Is there a primary key in the T_LoanID field? If so you could use a subquery
to get the info that you want. Even an autonumber field would do the trick.




--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Also, Key1 is the Field Logging Table's name for Loan ID. I didn't know if
that was made obvious by my SQL or not.

I changed the query from a Select Query to a Make-Table Query and created an
index in the new table after running it. I'm now trying to make a second
query that pulls from this update table. Will that work?

It is going much faster now, but now I can't get the Max function to work on
the counter...it's giving me duplicate loan numbers still when I just want
the record for each loan number with the highest count.

If it helps, here is the code for my first query (the Make-Table Query):

SELECT dbo_Q_FieldLogging.Key1, dbo_Q_FieldLogging.Counter,
dbo_Q_FieldLogging.DateChanged, dbo_Q_FieldLogging.FieldID,
dbo_Q_FieldLogging.OldValue, dbo_Q_FieldLogging.NewValue INTO T_Q_FieldLogging
FROM dbo_Q_FieldLogging
WHERE (((dbo_Q_FieldLogging.Counter)>10000000) AND
((dbo_Q_FieldLogging.FieldID)=1253))
ORDER BY dbo_Q_FieldLogging.Counter;

And here is the code for the second query:

SELECT T_LoanID.LoanID, Max(T_Q_FieldLogging.Counter) AS MaxOfCounter,
CDate([DateChanged]) AS Date_Changed, T_Q_FieldLogging.FieldID,
CDate([OldValue]) AS Old_Due, CDate([NewValue]) AS New_Due
FROM T_LoanID LEFT JOIN T_Q_FieldLogging ON T_LoanID.LoanID =
T_Q_FieldLogging.Key1
GROUP BY T_LoanID.LoanID, CDate([DateChanged]), T_Q_FieldLogging.FieldID,
CDate([OldValue]), CDate([NewValue]);

I might not be making them the proper way. I haven't been formally trained
to work with databases, so I just keep trying random things until something
finally works.

Thanks,
Michelle


:

You can't create an index using a select query. As the table is read only, I
doubt that you could add an index using an alter or create statement either.
You probably need to talk to who owns the table and see if they can.

Where is this table located? Are you getting to it through an ODBC
connection or is it a linked table? It's possible that you might be able to
use a pass-through query if the table is in another database management
system such as SQL Server or Oracle.

How slow is it? Second? Minutes? Hours?

What is the SQL statement for the query? Open the query in design view. Next
go to View, SQL View and copy and past it here. Maybe there's a way to speed
up that query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hello, I'm a beginner, so I don't know if this is possible. I have a query
that is pulling data from a read-only table. The two fields that I'm using
are a loan number field and a counter field. There are duplicate loan
numbers, but every record in the counter is unique. I used the Maximum
function to get the maximum count number for each loan. It works, but it goes
very slow.

I thought I could speed it up if I created an index for the loan number.
But, since the table I'm pulling from is read-only, can I do it in the query
instead?

Thanks,
Michelle

 

[Jeff Boyce]

Michelle

The hint is in the message ... you are trying to do this on a linked table
from a front-end. You can only change a table when you have that file open.
Go to the back-end to make the change.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.