Updating Data Table from Other Tables

[Guest]

Just so you know, I am a beginner when it comes to Access and have no formal
training whatsoever in programming, or the like...I've seen similar posts
here, but not exactly what I'm looking for.

That said, I've been tasked to build a database for my work to contain what
are essentially just facts on a variety of topics, in a time series fashion.
My main table has four fields: ID (autonumber), KEY (abbreviation of item
name, ie - ABS_ONT for Employment Absences in Ontario), DATE, and VALUE.
This table has about 45k records.

Much of this data are best organized into smaller tables, with the KEY's as
the column headers (this is how I find them on the internet). It would be
easiest for me to store these tables as I find them and run some sort of
query each time the database starts up to accumulate them all together in the
main table. This would allow me to copy and paste new data into the smaller
tables - the easiest way for me to update. I doubt this is possible.

The next easiest thing to do would be to generate crosstab queries, then
(from what i've read here) create dual recordsets (not sure what this means),
and update from here. Unfortunately, I've no idea what this means or how to
do it.

I realize there is no way to update crosstab queries because of aggregation
functions - yet i have no need for aggregation in xtb queries i run. The
only reason I run them is to display data in nicer form (call it
spreadsheetitus if you want, I'm happy with this disease, as is everybody
else i work with). In one post, Tom Ellison mentioned it is possible to
generate and modify crosstabs and extrapolate from them into original table,
but I dont know how to do this.

I am the only one updating these tables. Doubt I'll see a reply to this,
but any help would be greatly appreciated. If not, I'll just update item by
item...

 

[strive4peace]

"Doubt I'll see a reply to this, but any help would be
greatly appreciated" ...

oh, ye of little faith!

Firstly, you should name your fields so they names are not
ambiguous and you do not use reserved words for your names.

ID -- what kind of ID? ie: TopicID
Key -- this is a reserved word
Date -- you must specify what kind of date since DATE is a
reserved word. ie: DatePosted
Value -- this is also a reserved word -- what kind of value
is it? Pick a different name or qualify value as to its type.

After you modify your fieldnames, post back what they are.

"Much of this data are best organized into smaller tables"

no, you should have it all in one table since the structure
is the same. We can help you with making a form to quickly
locate/filter your records.

As you are capturing, why do you think it is easier to be in
smaller tables? What is your process for this?

Crosstab queries are for reporting, not for adding or
changing records. You would not want your structure to be
cross-tabbed anyway as it will make things much harder for
you down the road.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Guest]

Basically, I copy and paste tables I find on the internet on various topics
of interest to my business into excel, reformat them so they are in the four
field form (instead of having date as a row heading and name of item as a
column heading), and paste them into my database.

It would be easiest for me to view and update them in these smaller,
crosstab like, tables since that is how they are found, and all the items in
the smaller tables are regarding the same topic, say workplace absences in
Canada 1990-2005.

I tried having a large flat file with date as field name, or the various
items as field names, but Access wont allow that many fields.

Anyways, I've renamed my fields as follows: ID became VALUEID, KEY became
ITEMKEY, DATE became REFDATE, and VALUE became THENUMBER (since the thing
stored in this field is nothing but anumber, regardless of whether is a
dollar value, percentage, number, etc....

 

[strive4peace]

Why not paste them into Excel and save files in the same
directory?

Periodically, run an Access program to loop through the
directory, get the data from Excel, put it into the Access
structures, and move the source files to a backup directory
after you read them. You could even set the program up to
run automatically each time you open the database.

Just because it is convenient to capture data in a crosstab
format does not mean that is the best way to store it
....you can always crosstab when you need to for reporting

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Guest]

Crystal, thanks for the reply. I've since decided to keep the tables in
excel and link them in Access. I'm now trying to write the code to convert
them to the proper structure and update only new records when i run it, but
this is proving difficult.

 

[strive4peace]

Hi Polisci,

is it working ok to link it? You had said you wanted to be
able to add columns... if this is what you are doing, is
Access accepting the new Excel columns?

Can you post an example of what the data looks like when you
capture it?

Since you are linked, I assume you may want to use APPEND
queries

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Guest]

The tables as I recieve them typically follow this structure

DATE itemname1 itemname 2 itemname3
12/1/2000 # #
#
etc.

I'm trying to write code to transform them all into one table with this
format:

ID# ITEMNAMES DATES VALUES
1 itemname1 12/1/2000 #
2 itemname1 12/1/2001 #
etc.


This is what I've got so far:


Dim newtbl As Recordset
Dim rs As Recordset
Dim db As Database
Dim tblnme As String
Dim fldnme As String
Dim rwsrce As String
Dim dt As Variant
Dim vl As Variant
Dim intloop As Integer
Dim tbl As TableDef



Set db = CurrentDb

For Each tbl In db.TableDefs
tblnme = tbl.Name

Set rs = db.OpenRecordset(tblnme)
Set newtbl = db.OpenRecordset("Trial")


For intloop = 1 To rs.Fields.Count - 1

Do Until rs.EOF = True

With newtbl
.AddNew
!Key = tblnme & "_" & rs.Fields(intloop).Name
!Date = rs!Date
!Value = rs.Fields(intloop).Value
.Update
End With

rs.MoveNext
Loop

rwsrce = rwsrce & fldnme
rs.MoveFirst

Next intloop

Next tbl

Problem is, it is cycling through the invisible system objects I can't see
and I dont know how to stop it. Also, I'm not sure how to proceed once I've
created the main data table (called 'trial' here) and only want to update it
when new records are added in excel.

Thanks so much for your attention!

 

[Gary Walter]

The tables as I recieve them typically follow this structure

DATE itemname1 itemname 2 itemname3
12/1/2000 # #
#
etc.

I'm trying to write code to transform them all into one table with this
format:

ID# ITEMNAMES DATES VALUES
1 itemname1 12/1/2000 #
2 itemname1 12/1/2001 #
etc.


This is what I've got so far:

First, I'm going to assume you changed
field names from Access reserved, i.e.,
Key --> AKey
Date --> ADate
Value --> AValue

To do what I *think* you want to do,
make a copy of table "Trial" and call
it "tblTemp"

Make a query (say "qryNotInTrial")

SELECT
tmp.AKey ,
tmp.ADate,
tmp.AValue
FROM
tblTemp As tmp
LEFT JOIN
Trial As t
ON
tmp.AKey = t.AKey
AND
tmp.ADate = t.ADate
AND
tmp.AValue = t.AValue
WHERE
t.AKey IS NULL;

depending on your data, I think
that may be enough, or you may
need to add following to end of query:

AND
t.ADate IS NULL
AND
t.AValue IS NULL;

then another query (say "qryappNotInTrial")

INSERT INTO Trial
VALUES ("AKey", "ADate", "AValue")
SELECT
q.AKey,
q.ADate.
q.AValue
FROM
qryNotInTrial As q;

When you want to run this process

'empty tblTemp
CurrentDb.Execute "DELETE * FROM tblTemp", dbFailOnError
'fill tblTemp with subroutine below
GetNewExcelData
'append new data to table Trial
CurrentDb.Execute "qryappNotInTrial", dbFailOnError

where here be some adjustments to your code:

Public Sub GetNewExcelData()
On Error GoTo ErrorHandler
'use DAO to disambiguate your objects
Dim newtbl As DAO.Recordset
Dim rs As DAO.Recordset
Dim db As DAO.Database

Dim tblnme As String
Dim fldnme As String
Dim intloop As Integer

Dim tbl As DAO.TableDef
Dim strCnn As String

Set db = CurrentDb

Set newtbl = db.OpenRecordset("tblTemp")

For Each tbl In db.TableDefs
tblnme = tbl.Name

'process only Excel linked tables
strCnn = db.TableDefs(tblnms).Connect & ""
If InStr(1,strCnn,"Excel",vbTextCompare) > 0 Then

Set rs = db.OpenRecordset(tblnme)



For intloop = 1 To rs.Fields.Count - 1

Do Until rs.EOF = True

With newtbl
.AddNew
!AKey = tblnme & "_" & rs.Fields(intloop).Name
!ADate = rs!Date
!AValue = rs.Fields(intloop).Value
.Update
End With

rs.MoveNext
Loop

rs.MoveFirst

Next intloop

rs.Close
Else
'not Excel linked table
End If

Next tbl

newtbl.Close

ErrorHandlerExit:
Set rs = Nothing
Set newtbl = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub

 

[strive4peace]

Thank you for picking this up, Gary! I have been very busy
the last few days.

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*