Query Wizard Crashes

[Takes2ToTango]

Whenever I attempt to create a query using the wizard the query crashes and goes into a state of not responding. Due to this I can only make small query's. I have attempted to add further tables onto this query later but these just seem to make it crash as well.

Any solutions on why this may be happening constantly?

Thanks

 

[Joe4]

"Query Wizard"? Is this a Microsoft Access question?
I ask because we do have a forum for Access questions (Microsoft Access), but you have posted this in the "Excel Questions" forum.
So it isn't quite clear.

 

[Takes2ToTango]

"Query Wizard"? Is this a Microsoft Access question?
I ask because we do have a forum for Access questions (Microsoft Access), but you have posted this in the "Excel Questions" forum.
So it isn't quite clear.

Hi, it is the one on excel. From 'Data -> Get Data -> Other Sources -> From Microsoft Query'.

Sorry if that wasn't clear.

 

[Joe4]

How many records are in the data tables you are trying to query?
Can you post the query you are building?

 

[Takes2ToTango]

How many records are in the data tables you are trying to query?
Can you post the query you are building?

At the moment I can't show you the query as I can't even enter the records without it crashing! I'm not sure if it is due to the volume (Over 115,000) different part codes.

 

[Joe4]

At the moment I can't show you the query as I can't even enter the records without it crashing! I'm not sure if it is due to the volume (Over 115,000) different part codes.

No, I don't want the results. I want to see the code behind the query, if that makes sense (or what you are entering in to the Query Wizard to build it).
How many tables are involved?
How many records in each of the underlying tables?
Where exactly is the data stored/coming from?

 

[Takes2ToTango]

No, I don't want the results. I want to see the code behind the query, if that makes sense (or what you are entering in to the Query Wizard to build it).
How many tables are involved?
How many records in each of the underlying tables?
Where exactly is the data stored/coming from?

So, overall I am using about 6 different tables each with probably around 75,000 records contained in each one (However, it is more than likely the same data) which I will then join and link to a certain cell so only one result will show.

The data is coming from our external software which we use an ODBC connection to retrieve the data from.

 

[Joe4]

My guess is that the issue could reside with:
1. Too complex a query, i.e. trying to join 6 large tables together, especially if you do not have good data and good joins
2. Your connection to the data is too slow
It is really hard to pinpoint, without having more detail and access to it all.

If possible, it may be more efficient to create the query on the external software side, and then just bring the results across, so all the work is being done on the source side and not on the Excel side.

You may need to look at using alternative methods, i.e. Microsoft Access (which is a relational database software built for this kind of stuff), SQL, or perhaps Power Query in Excel.

 

[Takes2ToTango]

My guess is that the issue could reside with:
1. Too complex a query, i.e. trying to join 6 large tables together, especially if you do not have good data and good joins
2. Your connection to the data is too slow
It is really hard to pinpoint, without having more detail and access to it all.

If possible, it may be more efficient to create the query on the external software side, and then just bring the results across, so all the work is being done on the source side and not on the Excel side.

You may need to look at using alternative methods, i.e. Microsoft Access (which is a relational database software built for this kind of stuff), SQL, or perhaps Power Query in Excel.

Thanks for your help, I will give these solutions a go and see if they have any effect. I know I have used SQL before for certain things so I will give that a try.

Thank you

 

[Joe4]

Thanks for your help, I will give these solutions a go and see if they have any effect. I know I have used SQL before for certain things so I will give that a try.

Thank you

You are welcome.

Yeah, I think it would really help if you can do the query on the source side, and just retrieve the results.