Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

Microsoft Query Hangs when adding table

Sage 50 general help forum - Free help and support for all general issues
Post Reply
Guy Parker
User
User
Posts: 5
Joined: 18 Dec 2006, 11:53

Microsoft Query Hangs when adding table

Post by Guy Parker » 07 Jan 2007, 17:54

Hi,

I have a problem with Sage Link to Excel and MSQuery

I open the Excel Query Wizard and Add columns from the INVOICE table
I then edit the query in Microsoft Query

In Microsoft Query I press the 'Add Table(s)' button and select the INVOICE_ITEM table.

When I press the 'Add' button MSquery hangs.

It appears to only hang when selecting the INVOICE and INVOICE_ITEM tables.

Is it because of some relationship between these tables?

I am running Sage Line 50 Financial Controller V11.01.0030

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4664
Joined: 28 Feb 2006, 09:56
Sage Version: v30 UK/Europe/Africa

Post by brucedenney » 08 Jan 2007, 10:15

The operative word in your question is "appears", I suspect that you already have an idea that it is taking just a long time, which is what I also suspect is happening.

What you are doing is adding an unlinked table, so as there is no link every record in the second table is included for each record in the first table.

Say you have 100 invoices and each one has 10 items so you have 1000 items. This is a very small number but it results in 100,000 records returned!

I think that query is running fine, it is just going to take ages to run because there is so much data.

What you need to do is to link the tables before you execute the query.

To do this you need to turn off auto query before you add the second table, create the link between the two table and then either manually refresh the query or switch auto query back on.

I like to leave auto query switched off.

(there is a button for auto query on the main toolbar in MS Query, it is the exclamation mark with the circular arrows)
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

Guy Parker
User
User
Posts: 5
Joined: 18 Dec 2006, 11:53

Post by Guy Parker » 08 Jan 2007, 13:50

Hi Bruce,

You were right - it wasn't hanging - it was just taking a very long time.

Turning off auto query in MS Query did the trick.

Thanks

Post Reply

Who is online

Users browsing this forum: Bing [Bot], Google [Bot], Semrush [Bot] and 14 guests