How to normalize contacts in Access

GPGeorge said:
I am not at all sure where in that gray wall of text you think you addressed the critical point about two distinct databases and the lack of Referential Integrity across them. However, it's clear that this is indeed a dank rabbit hole. I'm going to move on to other things. Best of luck with your original project.
Click to expand...
I may be confusing this thread with one where I asked about running a split be at remote locations. Please forgive.
 
Sorry, I can't answer your question. All I can do is to point to the documentation on the MS site. How they implement something internally is not necessarily what they tell us.

Personally, I never use the Yes/No type probably because I don't like the way it works. You can't set it to null, for example. So, I use Integer which works the same whether the BE is Jet/ACE or SQL Server and which I can set to null.

Access assumes the Yes/No data type has two values and only two. That is OK for some data values but if a piece of data is optional, I want it to default to null so I can tell if the user has provided a value. Using the chkbox to display the field, Check is true, blank is false, a black square is null. Another interesting anomaly is that if you add a Y/N field after a table has data, Access doesn't update the existing records. If you search for null, you get nothing. If you search for true you get nothing but if you search for false, all the records are returned. So it is doing something unusual behind the scenes. I know it isn't populating the existing records with false because the results are the same whether you set the default as true or false.
 
cheekybuddha said:
Out of curiosity, why do you need more than one BE?
Click to expand...
Now there's a question I can answer with fact despite George's objections. Back in the 70s and 80s when mainframes and super minis roamed the dp departments (or rather the departments roamed around these pizza ovens), there was a thing called shared computing. This means companies often joined hands to share the cost of these beasts that had 64k of core memory and 30 users.

For twenty years I ran a service bureau that served large corporations with a specific computing and data entry need of claims processing. This was after I did my time at McDonnell Douglas as tech support for the super-mini that they manufactured and sold with the PICK OS/database. Some of my clients were McDonalds, the city of Los Angeles, many car and truck dealerships all having remote computing. I tend to group referential integrity and synchronization together when I talk about these environments, because we had to write the code to do both underneath our applications. I wasn't the only one doing this as McDonalds and Carter Haley Hale had massive distributed systems that all worked together doing the same thing over periodic modem dial-ups to a "main" db and they too wrote their own flavor of referential integrity and synchronization between split back ends. But this very real history is a gray wall to George.

Let's talk about one simple example that was employed in my business. We had about forty clients that contracted with us. The average for each was about a half-billion dollars in processing that balanced to the penny each month; kind of like a bank. For each of them we had a separate account (now called a db file) on our system. In that account was their data, not specifically shared with any other client, (however we did link the accounts together sometimes to pull off some massive cumulative comparisons when the FTC challenged one of our clients with anti-competitive practices). All the clients did share two different databases though. One was for common data like country, state, city, Arbitron/marketing data, etc. and one massive media file that kept track of every radio, TV station, newspaper, magazine, penny saver like publication in the USA and Canada. All those files weren't specific to any one client, It was as we said, universal. We also had another account, very well protected, where all the software was. Lots of time-share, accounting and payroll companies used the same model. Only later did the idiots at Microsoft think it was a good idea to mix data with programming in one place. If I start a payroll or claims processing service here in Colombia in the future, I'll be following that model again as much as possible for fiducial responsibility reasons. For one RFP of the government here, I'm looking at distributed sites that will process the data of 45 million people needing government health care. That's not all going to fit on one ACE or SQL database. However, I do like the idea of using Access as a front end, so that slightly sophisticated users can pound sand all day making queries with the data they are responsible for in a geographic area without bothering me too much.

Not in this thread, but another, did I pointedly ask about distributed processing between different back end dbs in the Access environment. I'm surprised that I've gotten little positive input, given that Access has been around about twenty years longer than our old-clumsy mini/mainframes were at the time.
 
Last edited:
Just because you can do something, doesn't mean you should. I discovered Access in the early 90's when I did a project for Reader's Digest that required getting data from DB2 on their IBM mainframe. I thought I'd died and gone to heaven.

Anyway, Access allows you to link to data from multiple Jet/ACE BE's or SQL Server, Oracle, DB2, etc, as well as Excel and text files. All of these disparate sources are equal as far as a select query is concerned. I can join a DB2 table to one in Oracle and to another in SQL Server. This a wonderful and very useful thing AND the query will be updateable. Is it wise, not really. It all depends on how much data you're pushing around. When the BE is a single RDBMS, Access will send the query to the remote database (Access does its best to "pass" through ALL queries). That lets the database engine do the heavy lifting. But join that RDBMS table to a Jet or ACE table or a .csv file or Excel file and what happens? The RDBMS knows nothing about the local Access BE/.csv/.xlsx so Access does the heavy lifting. it sends a query to the RDBMS to select all the rows (unless there is some criteria that can be used) from the linked table and bring it to memory on the local PC. Then it performs the join with the Jet/ACE/.csv/.xlxs table in memory on the local PC. Works like a champ unless you have too much data. What "too much data" is, is dictated by the amount of memory and disk space and processing power the local PC has.
 
twgonder said:
Now there's a question I can answer with fact despite George's objections. Back in the 70s and 80s when mainframes and super minis roamed the dp departments (or rather the departments roamed around these pizza ovens), there was a thing called shared computing. This means companies often joined hands to share the cost of these beasts that had 64k of core memory and 30 users.

For twenty years I ran a service bureau that served large corporations with a specific computing and data entry need of claims processing. This was after I did my time at McDonnell Douglas as tech support for the super-mini that they manufactured and sold with the PICK OS/database. Some of my clients were McDonalds, the city of Los Angeles, many car and truck dealerships all having remote computing. I tend to group referential integrity and synchronization together when I talk about these environments, because we had to write the code to do both underneath our applications. I wasn't the only one doing this as McDonalds and Carter Haley Hale had massive distributed systems that all worked together doing the same thing over periodic modem dial-ups to a "main" db and they too wrote their own flavor of referential integrity and synchronization between split back ends. But this very real history is a gray wall to George.

Let's talk about one simple example that was employed in my business. We had about forty clients that contracted with us. The average for each was about a half-billion dollars in processing that balanced to the penny each month; kind of like a bank. For each of them we had a separate account (now called a db file) on our system. In that account was their data, not specifically shared with any other client, (however we did link the accounts together sometimes to pull off some massive cumulative comparisons when the FTC challenged one of our clients with anti-competitive practices). All the clients did share two different databases though. One was for common data like country, state, city, Arbitron/marketing data, etc. and one massive media file that kept track of every radio, TV station, newspaper, magazine, penny saver like publication in the USA and Canada. All those files weren't specific to any one client, It was as we said, universal. We also had another account, very well protected, where all the software was. Lots of time-share, accounting and payroll companies used the same model. Only later did the idiots at Microsoft think it was a good idea to mix data with programming in one place. If I start a payroll or claims processing service here in Colombia in the future, I'll be following that model again as much as possible for fiducial responsibility reasons. For one RFP of the government here, I'm looking at distributed sites that will process the data of 45 million people needing government health care. That's not all going to fit on one ACE or SQL database. However, I do like the idea of using Access as a front end, so that slightly sophisticated users can pound sand all day making queries with the data they are responsible for in a geographic area without bothering me too much.

Not in this thread, but another, did I pointedly ask about distributed processing between different back end dbs in the Access environment. I'm surprised that I've gotten little positive input, given that Access has been around about twenty years longer than our old-clumsy mini/mainframes were at the time.
Click to expand...
"...I tend to group referential integrity and synchronization together when I talk about these environments,..."

And that, sir, is the heart of your problem. You fail to grasp the difference, or don't consider it important, or something, because you did all those major projects that way. In writing on and on about the many projects you've been involved in, however, you finally did get around to acknowledging this fundamental concept here in this sentence. Synchronization occurs between two or more different data sources. Referential Integrity is only possible within a single data base.

Synchronization comes into the picture because you are not able to enforce referential integrity when the data comes from different sources. My original point was that. But, now, apparently you have figured it out too.
 
GPGeorge said:
"...I tend to group referential integrity and synchronization together when I talk about these environments,..."

And that, sir, is the heart of your problem. You fail to grasp the difference, ...

Synchronization comes into the picture because you are not able to enforce referential integrity when the data comes from different sources. My original point was that. But, now, apparently you have figured it out too.
Click to expand...
Did you miss the part where I said we had to do both, in at least two different posts? Both means both, as in two. Or do you like to use partial snips of a conversation to try and prove your correctness? George I've understood the difference for over forty years now. However, I do not understand your haranguing attitude. {deleted] I'm just trying to get help from those who've gone before me in trying to do some advanced things with Access.

I'm not sure what you meant by the last statement. Did you mean in the past or present with Access? I have figured out, with the help of others, that Access, despite all the pretty colors and unimaginable lists of properties, doesn't come close to operating well in the distributed environment we had 40 years ago. Hell, there isn't even a command in VBA to pause for a given number of seconds! And all this with powerful desktops, an almost always present Internet (which some people presume will always be available, like when it goes down in my pueblo I can't even get helps for Access), and almost thirty years to fix basic problems with queries on null joined yes/no fields that every first-year college computer student learns about (http://allenbrowne.com/NoYesNo.html). In the last month that I've started again with Access I've read so many articles that say something like, "I can't use this feature in Access because it doesn't work in real-world situations."

The Access Web - Bugs: Index of Articles (mvps.org)
 
Last edited:
twgonder said:
Hell, there isn't even a command in VBA to pause for a given number of seconds!
Click to expand...

Rich (BB code): 
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub test()
    Sleep 5000 ' wait for 5 second
    MsgBox ""
End Sub

And FYI GpGeorge is one of the most intelligent persons I've ever seen. So you may want to re-consider what you just said.
 
Last edited:
twgonder said:
For one RFP of the government here, I'm looking at distributed sites that will process the data of 45 million people needing government health care. That's not all going to fit on one ACE or SQL database
Click to expand...
An Access (ACE) backend is the wrong tool for this job. However, a RDBMS like SQLServer, Postgres, MySQL or Oracle can handle this.

You may need to shard your tables, and use replication for load balancing.

IIRC Facebook runs on MySQL.

But still, it appears you are not designing something on that scale here - how much data are you looking to manage now that won't fit into a single backend?
 
twgonder said:
Did you miss the part where I said we had to do both, in at least two different posts? Both means both, as in two. Or do you like to use partial snips of a conversation to try and prove your correctness? George I've understood the difference for over forty years now. However, I do not understand your haranguing attitude. Are you just an old, PNW-crank having fun being a snot on this site? If so, please just go bother someone else. I'm just trying to get help from those who've gone before me in trying to do some advanced things with Access.

I'm not sure what you meant by the last statement. Did you mean in the past or present with Access? I have figured out, with the help of others, that Access, despite all the pretty colors and unimaginable lists of properties, doesn't come close to operating well in the distributed environment we had 40 years ago. Hell, there isn't even a command in VBA to pause for a given number of seconds! And all this with powerful desktops, an almost always present Internet (which some people presume will always be available, like when it goes down in my pueblo I can't even get helps for Access), and almost thirty years to fix basic problems with queries on null joined yes/no fields that every first-year college computer student learns about (http://allenbrowne.com/NoYesNo.html). In the last month that I've started again with Access I've read so many articles that say something like, "I can't use this feature in Access because it doesn't work in real-world situations."

The Access Web - Bugs: Index of Articles (mvps.org)
Click to expand...
Over the years, I've come to the realization that a tactic often employed to conceal one's lack of understanding of a basic concept is a relentless outpouring of bafflegab meant to distract and deflect from the point at hand.

That's why, when I see a "wall of gray text" in a post, I'm pretty sure that's what is going on. I regret having to be so blunt. I'd hoped early on that by pointing out what Referential Integrity is, I could encourage a bit of stock-taking and re-thinking. Instead it provoked more bafflegab, and for that, I have to admit falling short.

As Pat pointed out in one of her responses (post #45), there is virtually no limit--at least in theory--to the number of data sources one can consume from Access. What one CANNOT do, though, is bypass the very real constraints of the individual databases involved--Referential Integrity being the relevant one here.

By the way, look up the Sleep API and how you can use it "...in VBA to pause for a given number of seconds". You are technically correct, it's not a native VBA command. But a VBA programmer would know about APIs and how to use them to augment what isn't native. There is a point to mentioning this, beyond merely addressing this complaint.

It is that a good developer learns what the limitations of the various tools are, and instead of going on and on about how awful the tools are, sets about trying to find solutions.

I encourage you to move on and take up the task of learning the tools available to you in Access and start addressing the questions David raised for you.
 
NauticalGent said:
Is that another one of those Undocumented Methods that most do not know about?
Click to expand...
I think it's in common use in politics as well. I want to say it originated in the book, 1984, by George Orwell, but that could be wishful thinking.
 
I often refer to the concept as jargon-ese. The poster, in many cases, has acquired the MS Access software, has the latest/fastest hardware (equipped with ample LEDS in various colours) and has not read anything more than the M$ marketing blurbs. "OK Access build me my database. Magically account for requirements not yet identified; make it multi-user and perfect". Jeez M$ screwed up, this Access things doesn't do what I believed it would do (out of the box).
You mean there is an object model that Access adheres to? You mean I have to do some analysis and design? I guess I should have bought FileMakerPro (substitute any other software here)-it can do everything.......
We've all heard this.

Jargon-ese --- the inappropriate combination of semi-technical terms interspersed with malformed database concepts and misspellings.
Also known as bafflegab, gobbledygook, double-talk,rigmarole,song and dance
 
GPGeorge said:
Over the years, I've come to the realization.. blah, blah blah gray wall of your own.
Click to expand...
I am learning the Access "tool" (again for like the third time), and it's limitations. The limitations proved too much in the past, I'm trying yet again. Hence my questions here. Everyone starts from some prior experience. I'm sorry you don't like my explaining what I want to do with past experience and what worked well in enterprise computing before. I am working on and finding solutions (an example of the pause, documented here: https://www.accessforums.net/showthread.php?t=86324&p=497996#post497996 before this thread started).

As to the square peg round hole analogy (if it was another thread, please forgive, I have to write responses when I'm not online), Microsoft is famous for being the square peg during it's whole existence. They would buy or steal some idea (I happened to work at Xerox/MCD on the precursor that Steve J. and Bill G. later appropriated), and then change it just enough that it didn't play well with others. It all started with changing the slash to a backslash in DOS.

As to the comments that I must be new here, and don't know anything about Access, attached are two photos from my first purchase of Access. It's the only manual I found worth keeping over the years. That purchase was many years after having worked at MCD on the initial beginnings of the "Internet" and had already developed a robust RAD and commercial application long before the snotties started crying for integral referential integrity (as if that's the end-all like cloud computing is today {and even then, that's a thirty year-old concept too}). As I said, we had to write it ourselves and it wasn't native to the environments in which we worked. It's okay if something like Access wants to take on the task of integrating these features, just do it correctly please. And fix the bugs when it doesn't work (I know, I'm ranting here.)

If George is such a nice guy (he may be), then maybe he can take stock and stop humping on some portion of an explanation I give others when they ask a question of why (and I'm just trying to give an insight and not some some Access linguistically perfect answer), and focus on helping with the original question instead?

P1110497.JPGP1110498.JPG
 
twgonder said:
and focus on helping with the original question instead?
Click to expand...
Suggestions HAVE been made to which you responded with "back in my day-esque" responses. Rather than try to defend your position, why not try the suggestions of those you solicited?
 
You must log in or register to reply here.

Similar threads

D
Solved Normalization - Struggles In Access
2
Replies
25
Views
777
MajP
MajP
P
How to replicate the Outlook Application's Default Behaviour?
Replies
2
Views
321
prabha_friend
P
DickyP
Mobile phones (Cell phones in US speak) and the 'Why don't you use the app?'
3 4 5
Replies
95
Views
1,852
Isaac
Isaac
K
Teaching AI
Replies
11
Views
496
Isaac
Isaac
L
How to get results in lookup field from another lookup field?
Replies
6
Views
327
mike60smart
mike60smart

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom