Slow concat crosstab query

[dhewwn]

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]

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]

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.

 

[June7]

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.

 

[CJ_London]

if you want help, please provide the information requested

 

[dhewwn]

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.