Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-20-2019, 11:52 PM   #1
jackjsmith88
Newly Registered User
 
Join Date: Sep 2019
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
jackjsmith88 is on a distinguished road
Unhappy manually replicate actions of table analyser

Hi all,

wheneever ive created databses, its always from scratch, so no existing data to manipulate and i can easily organise one that has to be populated.

But.. now im creating one that rips reports out of SAP, and imports into Access, so that i can create various form based features and reports with charts etc,

i imported this years wad of data off, that lays out like so:

Call it RawData

Account Number
Account Name
ProjectID
ManualPriceAdjustment
BullingDocumentNumber
DeliveryNoteNumber
SalesDocumentNumber
CostPrice
NetPrice
Tax
MarginalPercentage
MarginNet

and a few more.. the table analyser, correctly reads what data needs to go in what table, but then it creates a shed load of corrections and wants to overwrite all my data with weird replacements

So what i want to do, is split the data myself, by creating one to many relationships in several tables, so that each table has the link button to display a subtable with corresponding info.

I started with SalesorderNumber.

So i wanted to create a table that autopopulates the field based on the data from the other table, and links them both together, and autopopulates based on whats coming from the RawData.

I suspect im going about this the wrong way, and i even played with the thought of an update query? but i don't know what im doing there?

if anyone can give me a gentle nudge in the right direction? :-)

greatly appreaciated.

BR

Jack

jackjsmith88 is offline   Reply With Quote
Old 09-21-2019, 01:12 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,263
Thanks: 40
Thanked 3,649 Times in 3,519 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: manually replicate actions of table analyser

you would use multiple append queries, one for each table. Depending on your requirement you may also require update and delete queries.

Assuming you are appending to tables with autonumber PK (i.e. not coming from the import data), you would first append to the primary table(s). Subsequent appends then need to link the import data to this/these tables on some unique value(s) in the raw data to be able to populate the FK in the secondary tables.

From your data, I would have thought you would want to start with account number as your primary table - one account can have many sales documents.

first append query

Code:
INSERT INTO tblAccounts (AccountNumber, AccountName)
SELECT AccountNumber, AccountName 
FROM RawData
a second one might be
Code:
INSERT INTO tblProjects (ProjectID, AccountFK)
SELECT ProjectID, AccountPK
FROM rawData INNER JOIN tblAccounts on rawData.accountnumber=tblAccounts.accountnumber)
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-21-2019, 03:28 AM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,433
Thanks: 112
Thanked 2,857 Times in 2,603 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: manually replicate actions of table analyser

Agree with CJL.
If this is a one off operation just run the queries or sql statements in turn on a copy of your database (in case of errors)
If its going to be done repeatedly over time, create a procedure to run each query/sql statement in turn with no user intervention needed.

You might find some useful tips in this article on my website Synchronise Data

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 09-21-2019, 04:05 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,384
Thanks: 87
Thanked 1,649 Times in 1,531 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: manually replicate actions of table analyser

Having not worked with SAP before, I have to ask a dumb question. Can you get the schema from SAP for the data it tracks? That might go a long way towards helping you to analyze this wad of data. Reverse-engineering something from a report doesn't always help you clearly design things if there is more than one pathway leading from point A to point B.

If you are doing this on someone's request, you can perhaps use that someone to persuade the managers of SAP to provide the schema or to boost your permissions enough to give you read-only access to that level of data.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 09-21-2019, 05:06 AM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,165
Thanks: 15
Thanked 1,576 Times in 1,498 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: manually replicate actions of table analyser

I import data from other applications frequently and unless i need to keep it for some reason, I generally leave it as is. If you are worried about size due to all the duplicate data, that might be a reason to go through the effort to normalize but maintaining RI is irrelevant and you will be replacing the data frequently so automation would be critical if you decide you want to normalize.

When importing data like this, I always use a template database that my main database links to. That way I don't have to worry about bloat. Each time I start a new import, I delete the current data filled template database and copy a fresh, empty one from the master folder and import into that one.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-21-2019, 05:56 AM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,433
Thanks: 112
Thanked 2,857 Times in 2,603 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: manually replicate actions of table analyser

Cross posted at https://www.utteraccess.com/forum/in...ic=2055201&hl=

Please read this article about the etiquette of cross posting https://www.utteraccess.com/forum/in...ic=2055201&hl=

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Table Analyser and compair versions analyser MickJav Tables 29 11-19-2018 02:16 PM
Replicate ODBC table davea300 Tables 0 02-27-2013 07:26 AM
Link Table manually jneirinckx Tables 5 08-24-2012 07:52 AM
Replicate Excel Table LEXCERM Tables 2 08-24-2012 05:29 AM
[SOLVED] replicate fields to a second table ilostmypants General 6 12-20-2005 01:00 PM




All times are GMT -8. The time now is 05:58 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World