Append records to existing database (1 Viewer)

AndyL63

New member
Local time
Today, 11:56
Joined
Mar 30, 2020
Messages
4
Upfront - I am a newbie to Access (so be gentle!).

I am converting an flat excel file containing sale and purchase transactions to an Access relational database - 12 out of the 37 columns have repeating values. I have created individual tables for these values and the base table of transactions is linked to them. I can query the data, so everything seems to work correctly, but now I need to add data. The input comes from excel in the same flat file format.

I have tried a number of queries, but they don't append correctly. I can't share the data as it is confidential, but will work up a simple example. In the meantime, if anyone has any pointers - I would appreciate it!
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:56
Joined
Jan 23, 2006
Messages
15,361
Welcome to the forum.
I recommend that you describe in simple terms what the spreadsheet represents in a little more detail.
You mention 37 fields --what are these in terms of business subjects/entities? A graphic showing your tables and relationships would be helpful. (or post a copy of the evolving database in zip format)
As for adding new data, search the forum for importing data from csv file for some ideas.
 

AndyL63

New member
Local time
Today, 11:56
Joined
Mar 30, 2020
Messages
4
Thanks jdraw. Unfortunately I can't share the data as it is confidential but I have created a simple example of what I am trying to do. I have searched, but I can't find what I am looking for, probably because I am missing something!

Attached is a very simple database. The [Original Data] table is the original excel file imported. The TransactionDatabase is the database version - created by analyzing the [Original Data] table - so has a link to the TransactionType table. I want to append the [New Data] table (another imported excel file) to the TransactionsDatabase.
 

Attachments

  • Simple append.zip
    23 KB · Views: 79

jdraw

Super Moderator
Staff member
Local time
Today, 04:56
Joined
Jan 23, 2006
Messages
15,361
A few points for consideration:
-do not use field names with embedded spaces -they will lead to syntax issues
use names like firstName, transactionType or similar
-avoid using lookups at the table field level --Instead create a separate Lookup table and join the fields in the related tables as necessary

You should be developing with a database with "generic data"/a mock up.
Consider Porky Pig, Joe Bloggs, Central City, Process2, Ima Student, ProductX, EmployeeWW for the type of data values suited to testing and sharing.

There is nothing in your design, nor your test data that would/should be so important/commercially valuable that you can't share a sample database illustrating an issue or concept with those who are willing to assist you.
 

AndyL63

New member
Local time
Today, 11:56
Joined
Mar 30, 2020
Messages
4
Thanks for the response. The example database is essentially a generic version - just much simpler, i.e. less columns and only one that can be referenced via a separate table. I have updated names as per you comments.

It may be my naivety not understanding exactly what you mean - I was not intending to create lookups in the tables, they were just an outcome of the Analyze Table in Access. I ran the Analyze Table on the imported file into tblOriginalData and this produced the new tables, Transactions and TransactionTypes.

I am not going to be entering any data directly into the Transactions database - updates will come via importing a new excel sheet, e.g. tblNewData. I can just append this to the original data, and re-run the analyzer - but that is additional effort especially if I scale up to the full dataset and have 12 lookups. What I want to have happen is append the new data to the Transactions table so that I can run a consistent set of queries (to be developed).

To append new data it needs to be formatted as per the Transactions table i.e. with the lookup for TransactionType instead of the text that reflects the TransactionType. It could be that I am looking at this from totally the wrong perspective and I should be doing it in a totally different way.
 

Attachments

  • Simple append v2.zip
    50.2 KB · Views: 89

jdraw

Super Moderator
Staff member
Local time
Today, 04:56
Joined
Jan 23, 2006
Messages
15,361
Perhaps this might work. I emptied your Transactions table, then made the ID primary key.
Then added the original data to Transactions, then added NewData.

Load your original data into Transactions QLoadOrigData
Code:
INSERT INTO Transactions (
    TransactionRef
    ,Quantity
    ,TransactionTypes_ID
    )
SELECT [tblOriginal Data].TransactionRef
    ,[tblOriginal Data].Quantity
    ,TransactionTypes.ID
FROM TransactionTypes
INNER JOIN [tblOriginal Data]
    ON TransactionTypes.TransactionType = [tblOriginal Data].TransactionType;

Query to append new data QAppendNewData
Code:
INSERT INTO Transactions (
    TransactionRef
    ,Quantity
    ,TransactionTypes_ID
    )
SELECT [tblNewData].TransactionRef
    ,[tblNewData].Quantity
    ,TransactionTypes.ID
FROM TransactionTypes
INNER JOIN [tblNewData]
    ON TransactionTypes.TransactionType = [tblNewData].TransactionType;

RESULTS of the above queries

Transactions

IDTransactionRefQuantityLookup to TransactionTypes
5​
IMS-001
1​
Purchase
6​
IMS-002
2​
Sale
7​
IMS-003
3​
Purchase
8​
IMS-004
4​
Sale
9​
IMS-004
2​
Purchase
10​
IMS-005
3​
Sale
11​
IMS-006
4​
Purchase
12​
IMS-007
5​
Sale

 
Last edited:

AndyL63

New member
Local time
Today, 11:56
Joined
Mar 30, 2020
Messages
4
Thanks jdraw.

I was so fixated on "changing" what was being appended I totally missed the fact I can get the transaction type ID number directly! Thanks for your help.
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:56
Joined
Jan 23, 2006
Messages
15,361
Glad this is resolved. Happy to help.
 

Users who are viewing this thread

Top Bottom