ODBC

[gazmoz17]

Hi Bruce,

Following on from the last topic that you've helped me with....but its about ODBC in general.

Looking at youtube vids now to learn query....but Microsoft query itself keeps hanging/not responding. If my data table joins are wrong would I just get a warning or return no data....or would this cause it to hang as well.

I’ve downloaded 64bit connector to match my operating system….but read an article where connecting to 1 company only (that’s all we have) the article referenced 32 bit only?

attach 1.png

I think the link is working as occasionally I get as far as previewing data when building my query, but its constantly crashing/not responding.

attach 2.png

attach 3.png

Started excel in safe mode, checked office for updates and done a full internet repair. Also uninstalled & re-installed odbc driver 64 bit.

Find it crashes slightly later if I don’t use the wizard.

Is it crashing because I’ve got join editor open in sage looking at the joins to match?

Thanks

[gazmoz17]

attach 4.png

[brucedenney]

If you are using Excel 32 bit you need to use the 32 bit odbc divers.
If you are using Excel 64 bit you need to use the 64 bit odbc divers.

The operating system, doesn't really matter.

============================================================================================

When you add tables in MS Query, then there is initially no join between the two tables.

So for each record in the first table, every record in the second table will be shown and so on.

If you 2 tables with 1000 record each that is 1,000,000 record that will be returned. This will take a long time to do and so it may appear that MS Query has hung.

To prevent this you need to turn off the Auto Query button and only press query or return, after you have joined the tables.

Screenshot 2022-02-09 115400.jpg

If you have a lot of fields and a lot of records in your query and you are running it over a network it is going to be taking a long time to get all that data from the server and then filter it down to just what you want.

There have been a couple of bad fields in odbc in the past, memo fields that crash ODBC.

There are also some Pseudo tables that don't really exist, they are made up from other tables, so take ages to load.

The projects table links to lots of other tables so queries on it are exceptionally slow.

Diving deeper, if none of the above helps, then knowing what your query is would be useful alone with the number of records (Help About will give you that for the main tables).

If you open your query in MS Query and click on the SQL button, you can copy and paste that into a reply.

Finally, it is possible that there is a problem with your sage data files, backup, run check data, then reindex, compress and check data again.

[gazmoz17]

Hi,

Appreciate your time there ....makes sense what you said & why crashing prob too big with unlinked tables and it auto querying.

This is the Sage report I'm trying to replicate:

Bruce eg.report

Purpose of this report really is to keep track of individual invoice profit. Will eventually pivot on all these fields.

Mkt: we always manually put this in sales ledger analysis one in customer record. Everybody forgets so added it to this report, so can eventually pivot on ROOFING customers and GRP customers. Used to filter Mkt for blanks and then go in each indiv corresponding inv and check see what market the inv is by nature of products they take. Added RVAL expression and conditional formatting so if report contains a roofing prod = ROOFING else GRP. Put barcodes in background of roofing products to trigger this. Doesn't work 100% because some products are taken by both customer types (catalyst & chopped strand matt etc). If salesledgeranalysis1 is blank then the returned mkt is green....I then filter on green and import all the market types in customer tab for new customers or ones they may have been missed prev (but on top of it now so green will always be new customer between current run date and when last ran this overall report). Mkt type = red...if report is stating that the invoice is GRP but already contains ROOFING in customer tab, most likely ROOFING customers whove took a cross over product (catalyst etc) so I'll change them back to what should be on my sheet. Really should just change report to mark them red but pull through sales ledger analysis 1 field, so all im doing is changing text colour rather than Mkt type.

Main Purpose of report

Realise there standard sage inv reports, but imposs factor in diff between carriage charged to customer and our indiv carriage charge for that inv.A lot of customers get free carriage but obvs we take a hit transporting that carriage. So we want to see the true net profit on the job after carriage. Do this currently by receiving a Transport Supplier Inv each week on excel. Use a VBA macro to format it how I want. We use Sales Order number for the ref booking jobs on Transport portal so I can pivot on this field to allocate my transport cost and deduct returned value against charged carriage....giving true net profit. Prob is we also put other refs in addition to sales order number, so was manually text to columns to extract SO no. until made the macro. Ideally want to use power Query to do this if poss and actually pull in the file from Transport company.

Back to this query

Current SQL statement:

SELECT INVOICE.INVOICE_DATE, INVOICE.NAME, INVOICE.INVOICE_NUMBER, INVOICE.ORDER_NUMBER, INVOICE.ANALYSIS_1, INVOICE.FOREIGN_ITEMS_NET, INVOICE.FOREIGN_CARR_NET, INVOICE.INVOICE_TYPE
FROM INVOICE INVOICE, INVOICE_ITEM INVOICE_ITEM, SALES_LEDGER SALES_LEDGER, STOCK STOCK
WHERE INVOICE.INVOICE_NUMBER = INVOICE_ITEM.INVOICE_NUMBER AND INVOICE_ITEM.STOCK_CODE = STOCK.STOCK_CODE AND INVOICE.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF

*But returns multiple lines for the same inv instead of just one?

Once my query is exported how do I then make further edits/formatting in power Query?
...Videos Ive watched they just double click the query, but my query's showing blank and only as a connection?

How do I pull through my expressions (from report designer) which are a calculation referencing other expressions? Do I just add a column with formulas in next to exported query columns and it will auto-update?

[gazmoz17]

Sort of process demo of what doing now.

Wish to replace this with Power Query and Pivot from there.

Current Process flow.xlsx

[brucedenney]

I think I get the gist of what you are trying to do. Not sure what TPT means.

The biggest issue is linking the carriage charge to the invoice so you can work out the overall profit of the sale.

Looking at your Sheet the Reference col O seems to be the most useful data for this.

If you were to embed that into the sales order/invoices using the courier and consignment note number then we would have something we can cross reference to find the charge.

If the Invoice from pallet line/royal mail, was split in sage and the reference was entered on each line (extra ref), then we could extract everything we needed from Sage and do it all automatically rather than needing to make a lookup for each pallet line invoice in excel.

The odbc query is going to return a lot of records, many of which are not returned.

I would split it up into several queries.

An invoice item query that needs to bring through the items.
A stock query that gets us the cost prices form each stock item.
An invoice header query to get the invoices we need.
A pallet line query get the invoice details from pallet line account

The invoice item will lookup the cost price from the stock table and multiply that by the qty to get the cost.
The invoice table will sum the cost prices of the items and the sale price of the items and sales less cost give us the GP>
The invoice table will lookup the Pallet line charge and add on the 16.5% surcharge to get the shipping cost.
You can now calculate the profit/loss on shipping items and the whole shebang.

I hope this helps.

[gazmoz17]

Hi,

Thats great Bruce thanks, I'll revisit this today.

Never thought of putting the Column O reference into Sage.

Thanks

[gazmoz17]

Power Query in General:

As my query stands now why do i only see a connection and not the actually query to double click & edit in power query....as Ive seen on youtube. Also is there a way to stop it duplicating lines for the same invoice?

Thanks

[brucedenney]

I think what you are seeing are the audit_headers being repeated for each audit_split.

You can prevent this by removing the audit_split table form the query