Help required to speed up a query retrieval

[ashishkarapurkar]

Hi ,



I am a novice in microsoft access.As part of an internal project assignment , i have created an access database by exporting data from 4 different tables with two common keys/Headers.I have written a query to combine all the headers of the 4 databases .however , the query is taking a long time to run and almost gets stuck.all my 4 tables have 60000 records as of now.

Is there any way to speed up the query retrieval which is calling data for almost 30 headers?please guide.

[ranman256]

you said 4 tables, then said 4 databases....which is it or is it both?

are you exporting TABLE via TRANSFERSPREADSHEET?
are you combining all tables into 1 query then exporting?

[Minty]

What is the table structure and what is the output you are trying to get to ?

It sounds as if you're running some sort of cross join, which will get painful with that many records.

[ashishkarapurkar]

Hi ,

I have an excel workbook with 4 worksheets which i am importing into access as 4 different tables into one access database .I am importing using DoCmd.TransferSpreadsheet by writing a macro in vba.
Later on , i am combining all the tables into 1 query. when i used the query wizard to create a new query with all the fields of the 4 tables , i got the required output.However , when i delete the records and rerun a fresh query , it is taking too much time.Hence asking if there is any way to speed up the query output.

[ashishkarapurkar]

What is the table structure and what is the output you are trying to get to ?

It sounds as if you're running some sort of cross join, which will get painful with that many records.

Hi ,

I have an excel workbook with 4 worksheets which i am importing into access as 4 different tables into one access database .I am importing using DoCmd.TransferSpreadsheet by writing a macro in vba.
Later on , i am combining all the tables into 1 query. when i used the query wizard to create a new query with all the fields of the 4 tables , i got the required output.However , when i delete the records and rerun a fresh query , it is taking too much time.Hence asking if there is any way to speed up the query output.

[Minty]

Re-running the query with less records shouldn't take longer... That makes no sense.
What is the Query ? Please open it the designer and go to SQL view, and paste what is there here for analysis.
Or zip your database and post it all here.

[ashishkarapurkar]

Hi Minty,

PLease find attached the link for downloading the access database and excel workbook.instructions also pasted to follow the process.
https://drive.google.com/file/d/1p4t...ew?usp=sharing

Thanks for helping out.Will wait for your reply.