Snapshot tables

[olorin]

Hi there,
First let me thank all who have helped me build a functional Sales/Shipping/Orders database.
I could not have done it without the generous help from people on this forum, and a couple of good books.

Presently;
I have one main table, where the day to day data-entry is entered, "tblShipping". There are a number of other tables also, but they are mainly to store Organization names, or AddressTypes, Products etc etc.
Then I have quite a few different queries based on the main tblShipments that I generate reports from.
As tblShipments gets larger, then the reports etc take longer to generate.

My query is this;
Would it be beneficial to do MakeTable Queries that would make tables, (or append records once the tables have been made), based on certain criteria?
For example; I thought maybe I could make tables based on certain customers, certain products, hauliers, etc. etc. and then base the queries that generate reports and do calculations etc. on those tables, instead of the main data entry table. Then every time a new record is added to the main table I could have the data appended to the relevant tables, and as the tables would not actually be linked to anything and also reside in the FrontEnd, as opposed to the BackEnd on the server, (which they are at present), things may speed up a bit.
Having read this post through before posting it, it sounds a reasonable thing to do, but as yet I haven't done anything to the database without first checking in here to see what the experts think.

Your time, as always, is apreciated

 

[spoole]

You could do, however given time your new tables will become larger aswell then you are back to the same problem. Perhaps some form of Archiving the main table to limit the amount of data in it at one time?

 

[olorin]

Thanx Spoole,
I had thought of that, but I am unsure of how I would collate the data needed in reports. Say, for shipments made to a specific customer, or for outstanding orders.
Outstanding orders for example would have to collect data from what was shipped against a particular order, from when the order was generated to the present. If you or anyone reading this knows of any web-links or books that would enlighten me on this subject I would be grateful.

 

[WayPay]

Hi Olorin,

some well-placed indexes can speed up your queries. Also, having the data in local tables in an .mdb file on your local drive can help if your network is a bit slow. I use this sometimes:

Sub CopyLinkedTableToLocal()
    ' copies all linked tables to local tables with the same name
    Dim oTableDef As TableDef
    Dim sSourceTableName As String, sTargetTableName As String
    DoCmd.SetWarnings False
    For Each oTableDef In Application.CurrentDb.TableDefs
        ' skip system and hidden tables
        If Left(oTableDef.Name, 4) <> "MSys" And Left(oTableDef.Name, 1) <> "~" Then
            ' only copy linked tables
            If oTableDef.Connect <> "" Then
                sTargetTableName = oTableDef.Name
                sSourceTableName = "~" & sTargetTableName
                ' rename the original linked table name
                oTableDef.Name = sSourceTableName
                ' copy linked table to local table with original tablename
                DoCmd.CopyObject , sTargetTableName, acTable, sSourceTableName
                ' delete the linked table link
                DoCmd.DeleteObject acTable, sSourceTableName
            End If
        End If
    Next
    DoCmd.SetWarnings True
End Sub

to copy the backend data to a local database (our network is VERY slow) for historical (and otherwise gargantuan) queries.

 

[olorin]

Hmmm, there's more than one way to skin a cat. as someone once said.
I like the sound of that WayPay, I'll give it a try.
Thanx for your advice

 

[WayPay]

Hmmm, there's more than one way to skin a cat.

Too true. I'm relatively new to Access, having successfully avoided it for years in client-server-land and some things that would kill performance on "big" database-servers work amazingly well in Access (and vice-versa). It's a matter of trying what works and what doesn't.

It can also help to limit your data sets. My guess is your tblShipping is huge. If you had to do a report on the past two months, it might be faster to do this:

1. create a query

SELECT * FROM tblShipping 
WHERE ShippingDate > (Date()-60)

2. save it as ShippingLast60Days
3.

SELECT * FROM ShippingLast60Days INNER JOIN table2 ON.. LEFT JOIN table3 ..

than to do this

SELECT * FROM tblShipping INNER JOIN table2 ON.. LEFT JOIN table3 ..
WHERE ShippingDate > (Date()-60)

(adapt general idea to personal filtering requirements.)

 

[olorin]

Great!
Isn't it funny how while looking for solutions to problems, we tend to overlook the simplest.
I hadn't thought of that, yet now you mention it, it's the obvious thing to do.
Thanx a million WayPay