Slow concat crosstab query (1 Viewer)

dhewwn

Registered User.
Local time
Today, 17:38
Joined
Jan 13, 2019
Messages
13
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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:38
Joined
Feb 19, 2013
Messages
16,553
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
 

dhewwn

Registered User.
Local time
Today, 17:38
Joined
Jan 13, 2019
Messages
13
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.
 

Attachments

  • Sample Concat.xlsx
    8.9 KB · Views: 73

June7

AWF VIP
Local time
Today, 01:38
Joined
Mar 9, 2014
Messages
5,423
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.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 09:38
Joined
Feb 19, 2013
Messages
16,553
if you want help, please provide the information requested
 

dhewwn

Registered User.
Local time
Today, 17:38
Joined
Jan 13, 2019
Messages
13
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.
 

Users who are viewing this thread

Top Bottom