Re: Query is not fast

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

It's not quite clear how you doing your grouping.

are you saying that you actually have one column, and an entry in the column
looks something like

00005838xx0-Funds Transfer
00006674xxx-Address Change
000100000042xxx-Account Maintenance
0001000000889xx-Funds Transfer

etc.?

I assume your goal is to get a count of say how many "funds transfer"
accounts do I have?

How could you possibly do be doing a grouping and sorting on that data as
above? There is absolutely no way you can to get any kind of reasonable
sorting and counts on the above data. there going to have to work today to a
little bit before you attempt to do a grouping and sorting query.

If the data is as above like one column, to get any kind of decent
performance in terms of grouping and sorting, I would suggest that you do
some type of pre processing on the data, before you run the queries. what we
wanted do is pull out that text party information, into a separate column,
then we can use high speed indexing (remember, you can't use sorting and
grouping on part of a text string in of the single column, you have to have
individual text values that can be grouped by.

Hencem I would create a new column called "accounttype."

I would then run a process to split out the above information into two
columns, eg:

CheckInfo AccountType
00005838xx0 Funds Transfer
00006674xxx Address Change
000100000042xxx Account Maintenance
0001000000889xx Funds Transfer

Thus we want to have the above two columns, then you can do a query to Group
by each account type, and we'll be able to use high speed indexing for this
purpose.

So, our first goal than is to take that original column, and split it into
two columns. in fact, just looking at the data, it seems it's also possible
to pull out the cheque number also. How ever since we don't need that other
check number, and just be the account type, then I think we should just
split this into two data collums....

To create the account type column, we just going to the table design mode
and create the AccountType field (make it a standard text field). make sure
an indexes on this this collum (and of course, the index will have to allow
duplicates as your above shows there will be many of each type).

The next step would then be to run an update query to move the account type
from our original column or into this account that column.

we could use:

update nameOfTable set AccountType = mid([checkinfo], instr([checkinfo],"-")
+ 1))

After we run the above update query (I'm assuming we're going to do this on
a backup copy, because those update queries can be pretty nasty, and if you
do this wrong you can mess up the whole table of data).

Once you've achieved the above, we have a 2nd collum here. (you can take a
look at the table after the query...and see if it looks ok). once you have
the second column the way you want, then it's very easy to use a query to
give you a breakdown in summary count of how many acount types you have of
each.

SELECT Sum(1) AS AccountCount FROM nameofTable
GROUP BY AccountType.

The above query should run quite fast, ...I would guess WELL under one
minute to do this....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx



.

Relevant Pages

  • Create a multiple parameter search form for a query
    ... I need to figure out how to create a user interface to search a query, ... but here's the bad part...I need to account for criteria on at least 7 ... opened" and an "account type" to search by, or maybe they want to see ...
    (comp.databases.ms-access)
  • Re: Adding Information to a Table
    ... Add two fields to SCT to contain the new data. ... Use an update query to populate the new fields that looks like the following ... == Join Account number to Account number ... I took all the account numbers from this table and looked up their "account types" and "account type descriptions" in another system. ...
    (microsoft.public.access.queries)
  • Re: Adding Information to a Table
    ... Use an update query to populate the new fields that looks like the following ... UPDATE SCT INNER JOIN MQ ... == Join Account number to Account number ... types" and "account type descriptions" in another system. ...
    (microsoft.public.access.queries)
  • Re: Same Columns Different Queries
    ... I got it to work but the sorting is a problem! ... sort this? ... ID Name Account Number ... Query 1 ...
    (microsoft.public.access.formscoding)
  • Re: Grouping and Sorting question
    ... It looks like you only need to sort/group by Client ascending and then Value ... Keep in mind that sorting in your query is worthless. ... One client can have multiple account #'s. ...
    (microsoft.public.access.reports)