Query Key

[miloann2002]

I have to run a query to join two queries. The key is 16 character long. It ran forever to get the result and never ended to export to Excel.

Does it have a way to create a shorter key based on the original key? Any other way that can get this done faster is appreciated.

Thanks

 

[Bob Barrows]

I have to run a query to join two queries. The key is 16 character
long. It ran forever to get the result and never ended to export to
Excel.

The key? You mean the field you are using to join the two queries? Why do
you think its length is causing the problem? Far more likely culprits would
be:
- the number of records returned by each query
- lack of indexes on fields used in joins and filter criteria in each query
- an incorrect join causing a cartesian result
- references to external tables

I could go on and on. The point is, your question is impossible to answer
without your providing much more information

Does it have a way to create a shorter key based on the original key?

You mean, on the fly? Your query is already taking too long to run and you
want to give it more work to do?

There is nothing keeping you from adding new columns to the source tables
and running update queries to populate them if you think that will help ...

A better solution might be to create temporary tables to receive the results
of the two queries, since I assume the two queries run in an acceptable
amount of time. Create indexes on the fields used to join the two resulting
tables and then join the tables for the export. But again, Im only throwing
this out there. I'm in the dark as to what the actual solution should
actually be.

 

[John W. Vinson]

I have to run a query to join two queries. The key is 16 character long. It ran forever to get the result and never ended to export to Excel.

Does it have a way to create a shorter key based on the original key? Any other way that can get this done faster is appreciated.

Thanks

Post the SQL of the queries.

The problem is NOT the 16 bytes in the field - that's very small. Access can
join on 255 byte fields.

The problem is either in the design of the queries, or - more likely - the
lack of indexing on the join field.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[miloann2002]

I have to run a query to join two queries. The key is 16 character long. It ran forever to get the result and never ended to export to Excel. Does it have a way to create a shorter key based on the original key? Any other way that can get this done faster is appreciated. Thanks

Thanks a lot. I used MAKE TABLE, set Primary Key, and it worked perfectly.