I need to group by and count (1 Viewer)

icedearth

Registered User.
Local time
Today, 10:02
Joined
Oct 10, 2008
Messages
24
In the form i have here there 4 columns the first one i have to count and group by branch_number. I have put group by and count in those coilums and take to long. the coulmn branch number has this criteria CDbl(Right([DBA_INC_LOAN_BANK]![BRANCH_NUMBER],3)) how i can put this because i want to know how many accounts i have in a branch.
 

Attachments

  • example3.JPG
    example3.JPG
    91.2 KB · Views: 81

MSAccessRookie

AWF VIP
Local time
Today, 09:02
Joined
May 2, 2008
Messages
3,428
In the form i have here there 4 columns the first one i have to count and group by branch_number. I have put group by and count in those coilums and take to long. the coulmn branch number has this criteria CDbl(Right([DBA_INC_LOAN_BANK]![BRANCH_NUMBER],3)) how i can put this because i want to know how many accounts i have in a branch.


Unless you have an unusually large number of records to process, this should not take so long to process that is creates issues. Can you show the SQL Code that is running so slowly?
 

icedearth

Registered User.
Local time
Today, 10:02
Joined
Oct 10, 2008
Messages
24
is a very large number of records
 

MSAccessRookie

AWF VIP
Local time
Today, 09:02
Joined
May 2, 2008
Messages
3,428
is a very large number of records

How many records are we talking about? That style of query should be able to handle a relatively large number of rows and still not slow down too much, but if the problem is in the query instead of the recordcount, then even smaller numbers could create problems. That is why I asked to see the query, because I am trying to imagine why a query like that would have such issues.
 

Rabbie

Super Moderator
Local time
Today, 13:02
Joined
Jul 10, 2007
Messages
5,906
There doesnt seem to be any connection shown between "Nombres sucursales" and your other tables this means that Your query will generate a huge number of records depending on how many records you have in each table.
 

MSAccessRookie

AWF VIP
Local time
Today, 09:02
Joined
May 2, 2008
Messages
3,428
This is the query


The table [Nombre Sucursales] is not related to any other table in this image, therefore accessing the field [BRANCH NUMBER] will create a Cartesian Join (also known as a FULL JOIN), which is usually VERY Slow.

Relate the table to something (I suspect relating [Nombre Sucursales].[BRANCH NUMBER] to [DBA_INC_LOAN_BANK].[BRANCH NUMBER] might work). You will need to decide if that is correct.
 

Users who are viewing this thread

Top Bottom