Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-13-2019, 10:33 AM   #1
dhewwn
Newly Registered User
 
Join Date: Jan 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
dhewwn is on a distinguished road
Slow concat crosstab query

Hi
I am using allen browne's concatenate function. But it takes some time to load. Is there any way to speed up the process?
I am using a crosstab query that looks something like this where xx is the invoice number.

Name Project 2016 2017 2018
ABC 1 xx xx
CDF 2 xx xx
CDF 3 xx

I am a beginner here so please explain in more detail.
Thanks

dhewwn is offline   Reply With Quote
Old 01-13-2019, 11:21 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,533
Thanks: 40
Thanked 3,404 Times in 3,297 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Slow concat crosstab query

do you mean Allen's concatRelated function? if so, not sure why you need it if doing a cross tab for a single invoice per month

Can't explain in more detail as you haven't provided any detail to work with.

Best guess is what you mean is the concatrelated function is being used to provide multiple invoice numbers, not a single invoice per your attached.

with regards speed - depends on your db design - relationships, indexes and criteria in particular - and volume of data.

At the very least provide a screenshot of your relationships window and the sql to your crosstab query
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 01-14-2019, 06:05 AM   #3
dhewwn
Newly Registered User
 
Join Date: Jan 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
dhewwn is on a distinguished road
Re: Slow concat crosstab query

Yes, I am using allen's concatrelated function.
There could be more than 1 invoice per month. There are only 2 tables in the file. I make sure that the data is always for 1 year only, so its a small file.
One contains the 'Name' and their relevant particulars, such as ID.
The other is a list of amount, name, project, invoice number, invoice date and the month.

One name will have many projects with multiple invoice number within the same month.
I have a attach a copy of what I wanted. As I read that I need to use crosstab query separately for amount and project before I can combine them together, so I am creating a crosstab query for the invoice number and amount separately. But the crosstab query for invoice number takes some time for it to load.
Attached Files
File Type: xlsx Sample Concat.xlsx (8.9 KB, 13 views)

dhewwn is offline   Reply With Quote
Old 01-14-2019, 06:13 AM   #4
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,107
Thanks: 0
Thanked 260 Times in 260 Posts
June7 will become famous soon enough
Re: Slow concat crosstab query

I don't find that Excel useful to analyze your issue without sample raw data and the code used. Why don't you just provide db?

However, I doubt process using ConcatRelated in conjunction with CROSSTAB can be speeded up. Might have to use alternative approach, such as writing records to a 'temp' table.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 01-14-2019 at 01:38 PM.
June7 is online now   Reply With Quote
Old 01-14-2019, 01:06 PM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,533
Thanks: 40
Thanked 3,404 Times in 3,297 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Slow concat crosstab query

if you want help, please provide the information requested
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 01-15-2019, 06:47 AM   #6
dhewwn
Newly Registered User
 
Join Date: Jan 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
dhewwn is on a distinguished road
Re: Slow concat crosstab query

Quote:
Originally Posted by June7 View Post
I don't find that Excel useful to analyze your issue without sample raw data and the code used. Why don't you just provide db?

However, I doubt process using ConcatRelated in conjunction with CROSSTAB can be speeded up. Might have to use alternative approach, such as writing records to a 'temp' table.
Due to the confidentiality of the info, there is a lot data that needs to be deleted. The time taken for the query to load will be shorten considerably. It's ok then, thank you for your help.

dhewwn is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Allen Browne's Concat module - how do I order by a field not in the query? bigalpha Queries 12 04-18-2015 01:10 AM
Crosstab design is slow when opening and saving in design mode mikecro Queries 6 10-08-2014 06:22 AM
Concat two text fields into a memo field in query? itgoeson General 3 09-06-2014 12:35 AM
Difference between Access SQL Concat and T-SQL Concat Rx_ Code Repository 0 09-11-2013 12:05 PM
Send email with concat emails using sendobject from query with filters (parameters) RPettinger Modules & VBA 1 08-16-2013 08:11 PM




All times are GMT -8. The time now is 08:59 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World