1. Add "group by month" to query 2. Append new/

[westjelly]

Hey all. Part of me writing this out here is getting an idea of where I am in the process. As always, I apologize for the inital "dump" of text. Hoping that it makes some sense

Outline of below:

1 - Add group by month
2 - Update a master table (Update/Append query)


Presently, a query is run on a SQL database. From there, 4 spreadsheets are exported, giving a look at the data in Monthly, QTD, YTD, and rolling 12-month format. These four files are linked into another spreadsheet, which also links from other spreadsheets. The web of spreadsheets, while perfectly functional, requires too much time to properly maintain.

I'm going to cut out out at least some, if not all, of the intermediate steps by giving a more functional export from the main database or, if and when possible, run the SQL query (or queries) directly from an Access App.

1.

The area I'm looking at right now deals with invoices that have posted between a start_mo and end_mo. Each month, spreadsheets are exported:

YYYYMO - Monthly.xls
YYYYMO - QTD.xls
YYYYMO - YTD.xls
YYYYMO - Rolling12.xls
(ex. 200603 - Monthly, 200603 - QTD.xls, etc.)

Here is the gist of what is contained in each:

Method - Contractor - Specialty - Charges - Hours - Days

Then tabs for Method:

ATM: Contractor - Speciality - Charges
Barter: etc.

Method
ATM
Barter
Charge
Debit

Contractor
Abe
Bob
Charlie
Dennis

Specialty
Aerospace
Butcher
Carpentry
DeepseaDiving



Anyway, what I would like to do is take a single set of data each month (rather than the 4 spreadsheets exports) and just extrapolate Monthly, QTD, YTD, and rolling 12-month from there.

The output would add a "group by month", to give:


Month - Method - Contractor - etc

So the SQL query would need to look at the Invoice post field, and group everything by month. This data goes into a master table in the Access app.

Which brings me to:

2.


I'm planning to have a command button to update the master table. Everytime the update is run, a new record is added to a "UpdateHistory" table, giving me a uniqueID for the date/time/user of the update. Then, when the update is run again, it would import any truly "new" records.

So, if I have, for a query in February:

Month   - Method - Cont - Specialty    - Charges - Hours - Days - UpdateID
200601  -  ATM   - Bob - Carpentry     - $1000    -  10  -   1  -   23
200601  - Barter - Bob - Carpentry     - $2000    -  20  -   2  -   23
200601  -  ATM   - Abe - DeepSeaDiving - $1500    -  15  -   1  -   23

Then, say in March, something gets changed in the January data, so that I would get:

Month   - Method - Cont - Specialty    - Charges - Hours - Days - UpdateID
200601  -  ATM   - Bob - Carpentry     - $1000    -  10  -   1  -   23
200601  - Barter - Bob - Carpentry     - $2000    -  20  -   2  -   23
200601  -  ATM   - Abe - DeepSeaDiving - $1500    -  15  -   1  -   23
200601  -  ATM   - Bob - Carpentry     - $1500    -  15  -   1  -   24
200602  -  ATM   - Bob - Carpentry     - $800     -  10  -   1  -   24
200602  - Debit  - Dennis - Butcher    - $400     -  10  -   1  -   24

Note the 200602 entry for Bob. Charges is higher, so the record is appended to the table

From there, I can work with the most recent version of the Month/Method/Contractor, OR, I can cross-reference the UpdateID to recreate reports done at an earlier date.

So, I want the update query to:

1. Compare data from main database to master table
2. If Month, Method, Contractor, Specialty and all other fields (totals of charges, hours, days, etc) are identical --> IGNORE
3. If any of the fields are different --> APPEND to the master table, using the current UpdateID


I'm looking for pointers, ideas, questions for each of these 2 things. If it rings a bell or sounds intriguing (or if you just think I'm off my rocker), let me know? Will be happy to help clarify places where I'm not giving enough information.

 

[mdmilner]

I'll start with, I'm not sure I really understand everything you're describing.
However, I do have some questions and I think suggestions.


You mentioned your process means you run (A) query and then export data into 4 spreadsheets. Are you talking about a manual process with you possibly editing the query each time you want to export something?

Under #2, you're talking about an UpdateHistory table and updating a master table. Have you thought about using flagfields to define actions?

In your source table, there is a field that is by default unchecked. After some action is done, such as appending the data to your master table, that value is checked (set to true) which tells you your action was performed. Alternatively, you could use a string value that could be descriptive of the action/actions done with it.


Under the export question. Have you thought about building a form in the access database that includes the built in calendar controls (allowing you to specify a date range) - and/or buttons to select categories or results, and then have code that generates querydef objects (Queries) and then export them into Excel?

The embedded is a live example from just such a form that I setup that references various form controls. Obviously, you may not be using the same names and I'll leave off many declarations.

Dim qdf As DAO.QueryDef

strSQL = "SELECT * FROM " & strSeries & "." & Me.lboTerminalName.Value & "3 " 'edit here
strSQL = strSQL & "WHERE dteDate >= #" & Me.CalendarBegin.Value & "# "
strSQL = strSQL & "AND dteDate <= #" & Me.CalendarEnd.Value & "#"
If ObjectExists("Query", strSeriesName3) Then
  DoCmd.DeleteObject acQuery, strSeriesName3
End If
Set qdf = dbs.CreateQueryDef(strSeriesName3, strSQL)

Call ImportExport("acExport", strSeriesName3, strPath)


Public Function ImportExport(ByVal Ltype As String, ByVal Tname As String, _
                                 ByVal TLoc As String) As Long
Dim intCnt As Integer
ErrorResume:
On Error GoTo Err_handler
Debug.Print Ltype & " File " & TLoc

Select Case Ltype:
    Case "acImport":  Ltype = 0
    Case "acExport":  Ltype = 1
    Case "acLink":    Ltype = 2
End Select
DoCmd.TransferSpreadsheet " " & Ltype, 8, Tname, TLoc, True, ""

Exit Function

Err_handler:

 Select Case Err.Number:
  Case 2391:
    'Debug.Print TLoc & " Has too many fields"
    'Call RemoveXLSColumns(TLoc)
    intCnt = intCnt + 1
    If intCnt < 2 Then          ' Only attempt to remove columns once
      GoTo ErrorResume
    End If
  Case 3051:
    ' Somebody is in the table
  Case 3274:
    ' External table is not in the expected format
    ' Usually means corrupted spreadsheet
    Debug.Print Err.Number & " " & Tname & " " & Err.Description
  Case Else
    Debug.Print Err.Number & " " & Err.Description
 End Select
ImportExport = Err.Number
Err.Clear
End Function

I've left in some of the error handling with comments as to what they usually meant. In the dynamically generated SQL statement, you can see where I'm referencing appropriately named calendar controls.

You should be able to Google or Search in these forms for the code for ObjectExists posted by myself or others.

Mike

 

[westjelly]

Mike,

Thanks, first of all, for confirming my fears. I ramble incoherently.

Secondly, thanks for the the code. It will, no doubt, help when I get into the output/report phase.

Ok, so for the first item, the present process is:

1 - Query (A) is run on main database
2 - Data is manipulated/exported into 4 spreadsheets
3 - These 4 spreadsheets are emailed, saved into an archive folder and then linked into the current master spreadsheet

What I'm wanting to do is bypass the step where the 4 spreadsheets are created, and make the first step a bit easier, or at least more direct. I have a large SQL query (it's the basis for the 4 spreadsheets so has everything I'm looking for -except- a grouping by month) that will grab the data if I can run it straight from Access, otherwise I would have a single file exported from the first Query (A) and link to it, then run an update query to look at the Query (A) results and append new/changed records to a table (B) in my database. Basically wanting to replace the master spreadsheet with a database.

1 - Query run on main database (orig)
Then either:
2a - Export to a spreadsheet I would link to --> 3. Update query that checks linked file/table against table (B), appending new/changed records
-or-
2b - Directly run Query (A) as an update query that checks existing table (B) (in Master Database) and appends new/changed records.

I'm dealing with paring down probably 20-linked files to a single database with as few extraneous/redundant files as possible. This one is probably the biggest, or at least tackling it provides the best groundwork to move forward from.

Any more clear on the first point?


Ok, for #2, and the Updatehistory

I did think about a "Changed" field that would be checked when something was done. The use I came up for it would be a query down-the-line that lists values that have changed, such as different charges for a given person in a given month. Some sort of "reconciliation" history that allows a user to go in and give an explanation for the differences in values. (Audit history is high up on the importance list)

I'm going to kick around the idea of flagfields a bit. Ultimately, I would be looking at around 30 fields that "could" be different. If any of them would be different for a given Month, Contractor, Speciality, etc, I would append them to the Master Table. It would make sense to throw an X in a "Changed" field.


As far as exports go, I don't need to create the 4 spreadsheets I reference above (if that's what you were talking about). I am definitely going to build a form with date ranges and such for the many different outputs they are used to seeing under the present system.

 

[mdmilner]

On change history - I'll bet if you look around - google, search here or other programming sites, you'll find several examples of how to do this.

I think the property you want is OldValue in the syntax of Me.controlname1.OldValue or Me.fieldname1.OldValue

You can use events to capture the OldValue & Value (current value) and then use the data to append the changes to a changelog table.

There are a number of approaches - really it's whatever you want.
-
Here's a question: Do you even need Excel at all?
Good answers are commonly things like "this is what the end users have" or "I'm using charts & graphs to review data".

I do not do a lot of work from the Excel perspective - you may want to browse that forum or repost a portion of your question there (or just make a post and link to this thread).

Have you ever used MSQuery? Did you know you can use it to link to a database?

What I'm thinking is - you may even be able to bypass the need to generate queries in Access. If you can write a Query in Access, you can use MSQuery to directly access the database to use your spreadsheet to re-present the data/charts/graphs/whatever.

Alternatively, you could code a loop that generates your base queries, then use a simple query in MSQuery (Select * from queryName1) and do it that way.

The choice here is either have the complex query in Access or in Excel.

Mike

 

[westjelly]

Thanks for the thoughts, Mike.

The process of tracking changes, right now, is twofold.

1. The master table keeps all new records and appends changed records. This is where I'm brainstorming the UpdateID. I don't have info on how often data would be changed retroactively, but I'd like them to have the option as a way to explain changes seen on YTD data.

2. All tables that will be changeable by users will have tables that track changes using BeforeUpdate and AfterUpdate on the forms involved. Given the number of fields (around 35), my thought is to just capture the whole record if something is different, rather than grab the new and old values.

Do I really need Excel? I'm only on the project temporarily, so the thought is to keep things as normal as possible, and the users, across the board, are more familiar with Excel.

Problems with the current system include data being hardcoded directly over formulas, formulas being dragged around, and too many linked files.

I'm looking to provide a datastore that doesn't essentially change, but with separate tables to store adjustments and other things that would, otherwise, be hardcoded manually. This gives me as close to the original data as possible, as well as safely allowing changes/additions. From there, I do plan on investigating the use of MSQuery, but I want to give all the possible data they'd want first, mimicking the current links by going directly to the original sources and tying them together. At the very least, I'll combine things together and then provide a "Select *" dump that provides what they currently base things off of, but with much more functionality and stronger data integrity. Ideally, I can give them everything and more. Ah, that pie in the sky.

Finding out today what sort of direct information and data pulls I can get.