Is Access Right for me? Building Relationship overview from disjointed sources....

[doppleswan]

Howdy,



Thanks for checking my thread, I hope it is thought provoking, and productive.

First of all, I am new to access. It is available to me at work and I saw a niche that might be helpful to my organization so I started messing around. I have built simple DB's to review some areas but am looking at a larger overall program and don't want to waste my (or my company's) time if there is no need to.

That being said this is the keystone of my issue:

(TL;DR - all of my customer data is kept in separate disassociated systems, should I continue reading simple excel tables with duplicate information and focus on reports, use the imported data to output new tables, or something else?)

I have approximately 1300 clients - some of these clients are related to each other by ownership, but most are not. I would like to build a database that accurately reflects all of these relationships without manually reviewing and inputting the data. Can I link or import excel files that represent the following items (which originate from various disinterested programs), and through any method (which I will whole heartedly dedicate myself to learning) create a DB that accounts for all of these things "as they are now" and properly appends when the source is updated? I have many other reports not listed here I use to validate missing data and look forward to any feedback ye' wise ACCESS GURU"s can provide me.

If you will but lend me your knowledge, I will give you all my gratitude; for any of you who find me worthy of what you are capable of giving shall be rewarded with all that my powers may permit.

System 1 - this system has a unique ID (UID) for every clients internet solution, TIN (there may be 1 or 2 duplicates in this field, but extremely rare) their users (users have their own UID, as some have access to multiple company profiles), their "accounts (which may relate to different TIN's than the one tied to their Unique ID)," and also service modules (on/ off) at company level but not account level. I can also see users access at "account" level, if some users have different relationships within their profile. This data gets sent to me via Excel and usually requires "cleaning" which I have macro'd

(there are 3 additional services which are accessed through system 1, via sso, but are turned on at company level and linked to individual user (UUID).... most likely only way to get this would be a report that will have duplicate rows showing each UUID for every instance of System 1a, 1b, 1c for each account it occurs on)

System 2 (standalone program that tracks TIN-> acct, always up to date with only open account records, does not see relationships between non linked TIN's even if one exists) - shows me all accounts, TIN, name, address, etc. and a couple other indicator's used for billing but otherwise all represented as yes no data in the field. all account numbers are unique but TIN's obviously can be associated with a multitude of accounts. This is pulled from a data warehouse in csv which I have been converting to excel and cleaning for formatting (i.e. short text), it includes the whole "account" universe which is much larger than my 1300 clients. about 120,000 rows.

System 3 (a standalone service, will eventually become System 1d) - shows me "presenter" name - which is arbitrarily assigned and "account" numbers being used by this service (I have been relating it to system 2 to include accounts on this table but not system 2 table) SQL Query -> EXCEL

System 4 (a service, ) - it shows me an account to account relationship, with one master to one sub, many duplicate master's but no duplicate subs available (although a sub in one relationship can be a master to another), not all clients have this service, and some people with this service are not my "groups" clients but we still manage the implementation of this service EXCEL

System 5 (a service) - I can build a table for this directly in access, or keep a excel sheet updated by team and link or import weekly to append (its a proprietary system with no usable reporting function for my purpose), there is one profile name, many users, users limits (3 fields), and accounts - all accounts on profile but may be limited to some users - addition subtraction occurs maybe 2 to 3 times a month,

System 6 (a service) - I can build a table for this directly in access, or keep a excel sheet updated by team and link or import weekly to append (its a proprietary system with no usable reporting function for my purpose), there is one account billed for service, but many accounts included, top level account should point this service to an existing UID, updated very very rarely.

System 7 (a service) - I can have a report built on this service demonstrating what accounts are being billed for it (in grand scheme, I just need to know who has it) - users for each client with access to 3rd part internet access, additional service add on field (primarily a billing issue) (excel or access directly, will have to review 100 pg PDF doc, or an existing EXCEL file that has a billing code field)

[orange]

This may not be what you hoped to hear but,

-Access (database) and Excel(spreadsheet) are built on quite different object models
-If your company is content with multiple Excel-based systems, then what is the rationale for changing?
-If Orders and Customers are not working, or things generally are in some state of confusion, then who is complaining?

-Access is very different than Excel. In fact many will say that knowing Excel may prove a liability when working with a database management system.
-"messing around" is not a prudent strategy for learning database.

I'm going to suggest a tutorial that leads you through a simple business description, to an identification of the"things" involved, to a separation of "things" into Entities and Attributes, then through Normalization(a key concept in database) and then to proper relationships. It comes with details and a solution. It will give you some basic knowledge with which you can decide how/if you should proceed with a database approach.
Tutorial Some related videos and info.
Good luck.

[doppleswan]

Definitely will look at resources you provided. thank you,

This may not be what you hoped to hear but,

1. -Access (database) and Excel(spreadsheet) are built on quite different object models -
2.-If your company is content with multiple Excel-based systems, then what is the rationale for changing?
3.-If Orders and Customers are not working, or things generally are in some state of confusion, then who is complaining?

4.-Access is very different than Excel. In fact many will say that knowing Excel may prove a liability when working with a database management system.
5.-"messing around" is not a prudent strategy for learning database.

Thank you, and although I am new to this forum I don't plan on going anywhere so please be candid with me. I don't want to waste your time nor have mine wasted.

To hopefully clarify my keyboard vomit from earlier and address your points which I numbered for ease of reference:

Overall this isn't about any instance of a sale, either service is ongoing or it is not, and i have been trying to keep report output in perspective when evaluating DB design.

client (top level relationship)
subsidiaries
accounts


1. I Understand this, when I say reports are in excel I mean that the programs (all unique) that support the client-service have had SQL queries run and outputted to excel, as the data warehouse is not complete and I will not have access to raw SQL Tables (I believe this would save me some headache but I am a neophyte here) for some time, if ever. If I can build a working test model from bad excel sheets, which I converted into access tables my data processing team can then clean this up. But no one is going to fund extra FTE's for them to build this from scratch.

2. My company is using different systems because their purposes are unique, I brought the idea up that we could join the information for a comprehensive "client relationship model" that could give us a snapshot of what "is" vs. piecing together the paper trail from onboarding, offboarding, etc. etc. (Again, nothing is actually excel based, some reports i can probably get in different formats)

3. I am complaining. Besides the 1/2 work day I could save EVERY member of a department in minuscule detailed record searching, which sometimes means plowing through poorly named .pdf scans of service agreements or making best guesses, I could also output a real, accurate, and complete outline of every tangible relationship, their inter-connectivity, etc. Additionally the future holds metrics but that is another conversation

4. please explain, i do a lot of work in excel but I am not an expert, although I am always looking for new ways to improve upon my as well as others usage. this might not be important in the grand scheme of things.

5. poor choice of words. Although everything i do involves some form of reverse engineering bloated data sets, using macros to turn excel sheets into something even remotely resembling a valid table.

[orange]

doppleswan,

I used customers/orders to represent "issues in the overall process that causes frustration and waste" (along with customers looking for alternate providers). I have no idea of the business you are in, and just picked one example that we often see in forums. When someone wants to change things and build a database, there's usually a driver/situation/"I've had enough,there's got to be a better way moment". Sometimes it's business problems, dissatisfaction (Customers/Clients and/or Management), loss of business, duplication of effort.. waste.

When you say reporting, that doesn't convey the business generally. It could be that someone/group needs quarter-end reports for project status and financial reporting... Often data is gathered through business processes (Sales/Financing/InventoryControl/ProjectDelivery....) and placed into tables (the database). Then whether end of day, monthly, quarterly or adhoc/regular reports are created based on the data in the database.

Perhaps you could give us an overview of the business generally in plain English. Please carry on with the tutorial, you'll get to see how the pieces fit together and hopefully understand why. It will help you with database design and development.

[doppleswan]

Orange,

I cant state enough how much I appreciate the dialogue, even if I am just a bloody idiot and need to be told so. (so thanks again)

I am in Treasury Management, we administrate tools that help businesses operate, and bill monthly based on active service enrollment. Some things may bill to the account enrolled, some bill for all accounts enrolled to a "master" account. some items are billed per user, etc. I don't need this database to build our billing file, but I did use a abbreviated one to audit our billing file last month.

I tried to E-R D with the Dept. head, who has become excited about this project, and told by our data warehouse team it is the right track to solving our problem. However, when I showed him the difficulty we may be facing he recoiled to a position that I may be in over my head and this could tie up needed resources in development; instead asking for a simplified version. I would like to produce the simplified version with the full version in mind, allowing structure to be added later without damaging Normalization/ RI.

Please tell me if this is helpful -

TM Relationship (this is the top level of relationship we are trying to properly capture, and would need to create a unique TMR serial number once all relationships are properly vetted)
SUB RELATIONSHIPS - ( most often Defined by Internet Portal Profiles)
IPP - has unique ID field in native program, CustomerID
- can only have one TIN and Customer Name
- has unique users (even is real person exists in multiple IPP profiles) UserID
- Can access accounts other profiles access, but sql query has to pull account information from user tables to map
to CustomerID
- Accounts may tie to different tax ID's
- users can all have different account access/ rights (not actually important to MGMT)
- is a sso link to other Services

Accounts - Unique number, no duplicates. I have a csv with all active account numbers and additional fields I requested built into a report I can run daily if needed
- Can belong to multiple IPP, can only belong to one TMR
- One TaxID, One Name
- other fields on report are repetitive info, field 1(yes/ no), field 2 (master, sub, single), field 3 (3 digit account type code, 100 choices),
field 4 (charge, bill, waive), field 5 (status, new, active, closed, etc.)
- Accounts are enrolled in services for billing standpoint,
- Accounts are assigned to authorized users in each Services system



Services - 19 total
- enrollement is sometimes per account basis, sometimes TMR basis with one account being "billed"
- IPP is a service, and shows enrollment for 3 other services
- no other services share access points or customer data, will have to pull information from each
- not all services require users
- some services define specific user rights
- most of the data sets I have for services are a field that is named by native service program and account number, some services may list the same account number multiple times due to multiple locations for client having access to account via service



Users -
- can have multiple services for multiple accounts, multiple IPP profile logins
- can have multiple profiles with different profile id's and limits
- cannot be a TMR
- do not have TIN, DOB, address
- do have multiple phone,
- may have multiple email
- some services are billed on user count



I hope I am not confusing the issue. What has been requested of me.

DB that can be maintained by pulling data from the various systems that control services, usually download a query as a excel file or csv (must be a better way) automate the update of linked files, or recurring imports with saved import options to append? maybe not an issue if I am otherwise wasting my time

DB -> tell me who my TM relationships are, what all accounts are under their umbrella. what all services are active. what accounts have which services (where applicable). who is their primary contact, primary contacts email/phone, who is sales agent.



that was exhausting and I am sure I convoluted the issue, or omitted something key,

[orange]

You are starting the process of identifying the business and business rules. Many people have a hard time describing WHAT their proposed database is about. They tend to get detailed and talk about "HOW" they will or are doing something. As I mentioned earlier, your knowledge and expertise with Excel may be a negative when learning database/Access.

Here is a link to a number of free data models. These are to help people with organizing their data/tables/relationships. These are NOT meant as end products or the ONLY way to do such and such. You may find pieces of 3 or 4 models helpful if you merge them together and add a few more tables and/or relationships of your own. These are meant to be general models to help/assist "database developers" to get a starting point or confirmation of an approach. I note there is NO Treasury Management model -- so maybe look for other terms (Finance/Investment.....etc).

I don't understand what Treasury Management involves. I'm not sure what you mean by IPP or TIN nor where it/they fit in Treasury Management. The first issue is to identify the "things" in you business. I see Account, Service and User ( I prefer singular terms, but that's me), and others. You probably deal with AccountTypes and/or UserTypes.

You will find if you write a definition /description for each of the "things", you'll learn a lot about your business/proposed database. Such definitions/descriptions can be used for documentation/training/communication. They should be sufficiently clear so as not to be guessing whether an X is really an X or sometimes X1 or Y. And they should be in a form that could be given to someone else for development/maintenance etc. You know this stuff better than anyone.

Did you work through the tutorial and the related videos?

Also, I was not trying to be judgmental; I was trying to get you to tell me/readers the rationale for looking to Access as a possible solution to "What??".


I did some googling and found these re Treasury Management (but it hasn't helped me really)

The role of the treasury team has moved far beyond cash management. Organizations now look to treasury to provide detailed financial and economic analysis, and offer deep strategic insight. Treasury banking can no longer be just a functional role, and now needs to be a strategic partner, supporting multiple business units throughout the organization.

Wikipedia definition

[rpeare]

Orange is giving you some excellent advice and direction but I do have a question based on your first post. If all of this information is being tracked:

(TL;DR - all of my customer data is kept in separate disassociated systems, should I continue reading simple excel tables with duplicate information and focus on reports, use the imported data to output new tables, or something else?)

in a separate system. Is it a system that you can simply link the external systems (tables) into your access application and manipulate the data from there, what you're looking at is whether your current software is an ODBC compliant piece of software. Some companies have what are called 'proprietary' back ends (databases) that will not allow you to connect/use the database, mostly because they want you to spend oodles of money on their annual conrtact fees. If you are not in that situation you may be able to shortcut this whole process short.

If the current software you're using is tracking some... but not all of what you want you could build a database around the 'missing' portions and still use the current data from your 'disassociated systems'. Unless I'm misreading this entirely and your 'disassociated systems' are, in fact, the spreadsheets you're talking about.