UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Manually Replicate What Table Analyser Does, Any Version    
 
   
jackjsmith88
post Sep 21 2019, 03:49 AM
Post#1



Posts: 49
Joined: 28-October 18



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 called (sales orders) 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
Go to the top of the page
 
jleach
post Sep 21 2019, 07:11 AM
Post#2


UtterAccess Editor
Posts: 10,142
Joined: 7-December 09
From: St Augustine, FL


You can try a make-table query. It's been a long time so I don't know the syntax offhand, but look at some SELECT INTO examples.

Typically for data imports, I'd like the data into a staging table, then use a series of queries to clean everything up and get the clean data moved to their final resting place. I also tend to create the final tables before doing any migration to them, so instead of SELECT INTO (the make-table method), I can do an INSERT INTO instead. I find this to be more rerunnable than a make table query, which is nice for testing and fine tuning.

Hard to say though without seeing any of the data or structure. Maybe if you can grab a screenshot of your setup (pre-import and desired completed) we can pick out a better way.

hth

--------------------
Go to the top of the page
 
projecttoday
post Sep 21 2019, 07:45 AM
Post#3


UtterAccess VIP
Posts: 11,055
Joined: 10-February 04
From: South Charleston, WV


If you're going from report into Access and then normalizing there, splitting it into tables, why not just use the SAP tables as the source of your import? Do you have an ODBC connection to SAP so you can link the tables?

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Sep 21 2019, 07:59 AM
Post#4


UA Admin
Posts: 35,841
Joined: 20-June 02
From: Newcastle, WA


I agree with both Jack and Robert.

First, if you can obtain direct access to the underlying SAP tables, then that route would be quite preferable, IMO. That way you don't have to massage data as it journeys through the report and into your Access Relational Database Application. I realize, though, that you may not be able to get that direct link to the source tables.

If you want to control the data better, import the report data into a temporary table in Access. From it, you can then use a series of Append queries to copy that data into your properly normalized Access tables. Once you've done this a time or two manually, you can then set up VBA to automate that process, executing the original import into the temp table and the subsequent Append queries in order. That leaves you the final task of QA on those resulting tables.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
isladogs
post Sep 21 2019, 08:57 AM
Post#5


UtterAccess VIP
Posts: 1,680
Joined: 4-June 18
From: Somerset, UK


Cross posted at https://www.access-programmers.co.UK/forums...1&p=1641313. Usual link about etiquette of cross posting provided at the other forum

--------------------
Go to the top of the page
 
projecttoday
post Sep 21 2019, 09:18 AM
Post#6


UtterAccess VIP
Posts: 11,055
Joined: 10-February 04
From: South Charleston, WV


VBRK is the billing document header table. VBRP is the billing document item table. billing document = invoice. Of course, there are other tables, like the customers table. But if you want to report from Access, once you link to the tables, you should be good to go.

--------------------
Robert Crouser
Go to the top of the page
 
PatHartman
post Sep 21 2019, 09:28 AM
Post#7



Posts: 64
Joined: 20-February 03
From: Stratford,Ct USA


I've never worked in an environment where they let me link directly to the SAP tables. The SAP schema was considered too complex plus the DBA can't control the queries we might run so they always insist on exports.
This post has been edited by jleach: Sep 21 2019, 01:21 PM
Reason for edit: removed unnecessary comments
Go to the top of the page
 
projecttoday
post Sep 21 2019, 09:44 AM
Post#8


UtterAccess VIP
Posts: 11,055
Joined: 10-February 04
From: South Charleston, WV


Header table / items table. It isn't more complex. It's the same as many an Access billing db. There are other tables which support that but there are other tables in Access dbs, too. Some Access db's might be even more complex than SAP from that standpoint. SAP does have more lines of code than most custom-software, though.

Let's hope Jackjsmith encounters less resistance.

--------------------
Robert Crouser
Go to the top of the page
 
PatHartman
post Sep 21 2019, 10:52 AM
Post#9



Posts: 64
Joined: 20-February 03
From: Stratford,Ct USA


Apparently SAP has changed since I last worked with it. At the time the schema was several hundred tables and all the column names were still abbreviated to 8 characters and the abbreviations were from German words so the schema was actually mystifying to the average English speaker without a concordance.
Go to the top of the page
 
projecttoday
post Sep 21 2019, 01:41 PM
Post#10


UtterAccess VIP
Posts: 11,055
Joined: 10-February 04
From: South Charleston, WV


Well, you've probably worked with SAP more recently than I have. SAP does have a great many tables. But most of these tables are "bells and whistles". Many of these bells and whistles aren't being used by your company at all. I wouldn't call SAPs schema more complex just because there are more tables. The core of the the whole billing document function is the same header/items combination.


--------------------
Robert Crouser
Go to the top of the page
 
jackjsmith88
post Sep 22 2019, 01:26 AM
Post#11



Posts: 49
Joined: 28-October 18



I'd just like to thank everybody for their input today.

wasn't aware of any etiquette issues regarding Cross-posting, so i'll cover my tracks better next time 😂

unfortunately, humungus corporate company, they'd never let me anywhere near the linked data 😂 and i wouldn't know where to start.

i've managed to save a variant and a layout in SAP and can manage this data to be exported automatically, its the automation in access im struggling with, because as mentioned, the analyser splits all the data perfectly, then wants to overwrite it all with corrections.

with your feedback i can now sucessfully write append queries to split the data, so i've learn't something, and its helped me greatly!

massive thanks to all those who provided answers and feedback.

Jack
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th October 2019 - 10:34 AM