Hi Don,

You'll have to import each company database separately, since each will have its own set of Project IDs.

The project detail amounts are stored in multiple tables, linked to the G/L, for each year.  Each detail entry is linked to a transaction detail line (numAC), and each transaction detail line can have multiple project detail lines (numPJ).  

To report over multiple years, you'll probably need to aggregate the tables from multiple years together.  So, a project that spans 5 fiscal years would have journal entry financial detail data in 5 different tables.  

Have you looked at XLGL from Logicim?   It may do what you need more easily, since much of that multi-table complexity is abstracted away.

Well, this has proven to be a bit of a wild goose chase so far!

I've had a few offline discussions with Richard Ridings as well, and I now have a query that works fine (thanks Richard!) - as long as it is run manually. I can run all three - manually - and get the results I need into Excel and pivot them.

Now the problem:

MS Query (or, for that matter, running directly from an ODC file) cannot seem to understand that SA uses variable ports, not just 13540 which is the port burned into the connection string. So, each time the query runs, or refreshes, I get an ODBC driver error saying that the database can't be found and it redirects to the standard DSN selector...I must then re-select my chosen SAI file manually. There seems to be no way of automating this.

Having done some searching, I can't find a way of:

(a) setting a static port for SA

(b) setting a port range in the odc connection string

(c) querying to find the port number and entering into the connection string as a variable

If I'm correct, it means that using MS Query, or and ODBC connect with SA will not work in an automated fashion. Can this really be so or am I missing something?

Sage?

Well, this has proven to be a bit of a wild goose chase so far!

I've had a few offline discussions with Richard Ridings as well, and I now have a query that works fine (thanks Richard!) - as long as it is run manually. I can run all three - manually - and get the results I need into Excel and pivot them.

Now the problem:

MS Query (or, for that matter, running directly from an ODC file) cannot seem to understand that SA uses variable ports, not just 13540 which is the port burned into the connection string. So, each time the query runs, or refreshes, I get an ODBC driver error saying that the database can't be found and it redirects to the standard DSN selector...I must then re-select my chosen SAI file manually. There seems to be no way of automating this.

Having done some searching, I can't find a way of:

(a) setting a static port for SA

(b) setting a port range in the odc connection string

(c) querying to find the port number and entering into the connection string as a variable

If I'm correct, it means that using MS Query, or and ODBC connect with SA will not work in an automated fashion. Can this really be so or am I missing something?

Sage?

Hi,

(a) you can't get a static port for SA, since the ports are assigned dynamically by the Connection Manager, and passed to the MySQL database instance and the workstation, at login time.  

The assigned port number changes depending on the login order of multiple users, although the initial handshake port is the same. 

(b) If you can use the named ODBC connection that's automatically created on login, it should work. 

MS Query and Sage work together about as well as you would expect, if someone had deliberately tried to make it impossible.  For instance, I've noticed that MS Query refuses to connect if the settings for 'third party access' in Sage 50 are set to 'Read Only'.

I couldn't get multiple, simultaneous ODBC connections to work reliably in LibreOffice either, for that matter, something seemed to get confused during the connection process.  

Seriously, try XLGL.  Connections to multiple databases can be done with a few mouse-clicks.  I haven't tested whether you can have multiple, simultaneous connections, or whether you would still have to switch between them and copy data around the sheet.

Thanks Randy --

Beats me if this is a conscious design choice in the database or not, though I suspect the floating port number is purely Sage. I'm not enough of a DBA to understand why one couldn't configure a static port, then just point at the file and go from there! Rather like assigning a static IP to a machine...one *always* knows where it is. Why not a user-configured static port? (This might, I guess, be a limitation of MySQL?)

It's been odd trying to do this -- a simple MS Query to a SQL Server database takes less than 5 minutes to construct. I can't count the hours I've wasted on this....it makes Sage 50 "difficult". The only real way of breaking the year-end barrier is from outside Sage 50 itself; if I, or my staff has to input the data source each time we do a given query it renders it semi-useless to do so. Might as well just do an Excel dump from within S50.

I've tried XLGL and in many ways I like it. For certain purposes, however, having to split the debits and credits in various accounts to get "input" and "output" totals proved problematic on the accounting side (we would need multiple accounts for different purposes, rather than being able to distinguish based on criteria within the transaction) so it didn't really work for us. (Typical for a funds-based NPO.)

Don

Hi Don,

It probably wouldn't be difficult to have the software *always* assign the same port to a particular company and login ID, instead of assigning them as connections are made.  But unfortunately it doesn't do that, especially by default.  

One of the reasons I've just started using XLGL again, is that it is so much easier to make a connection, enough so that I can build reports once and hand them off, instead of running them for everyone.  

I've had good past success with queries in Access, but less so in the 2014 release, it seems to keep forgetting the saved password.