You can certainly create a database such as you describe using Access, if that is what you want to do. I don't see any reason why it would not be feasible. If you want to include other relationships (tribe/clan/family), make those part of your logical data model at the start.
"Perfectly normalized" is a relative term. Normalize according to what you are trying to accomplish. 5th normal form probably isn't going to help in a reporting database where denormalized happens to be what is needed. What you need is a data model that matches what you plan to do with it. Define the entities and relationships in terms of the problems you need to solve. Normalize where needed, and denormalize where needed.
MS Access is OK, I guess, as a front-end GUI builder, if your expectations aren't too great. I've never worked with an Access application that was really well done. A few of them have looked impressive, but they were hard to use. I'm not saying that it is not possible to develop a good one, but I suspect that a lot of people give up before they arrive there.
Whenever I have tried to develop using Access I have run into an endless series of restrictions on what I could do. The SQL dialect itself has many limitations, and the VB programming environment is full of pitfalls. I haven't worked with it now for some years, and I don't know if it has moved on to VB.Net since then, but working with "On Error" in the versions I used was awful. If you are going to do everything in Access then you are probably going to be stuck doing GEDCOM imports and refreshes in VB.
On the other hand there are many things you can do with the forms features, if you know them well. If you don't, you may waste a lot of time trying to do things and failing because you didn't know the particular trick for that task. I say this as somebody that doesn't know all the tricks.
I would like to be able to import my data into a SQL database as well, in order to do queries against it that typical genealogy software does not do. I came across your question while looking to see what might already be out there.
I would be inclined to use SQL Server, because SQL Server development is what I do for a living. If you don't know it well, it can present a steep learning curve. But importing, refreshing, and exporting GEDCOM data is straightforward, if not especially easy, using Integration Services, standard reports can be created using Reporting Services (or even Report Builder), and I might even see possible applications for attribute management using Master Data Services. This is all on top of a powerful database engine, and all of these components are available in the Developer Edition for about the cost of a genealogy software package, if you don't count the time to develop the application, which could be quite a lot.
Personally, I don't have time to develop something like this right now, and I will keep on looking. Good luck.
In Access, this is more about code than design, unless you decide instead on an autonumber, which provides the required unique field and is much easier and safer. Otherwise, in https://stackoverflow.com/questions/11949603/access-vba-find-max-number-in-column-and-add-1/11950647#11950647, you will find the following notes:
This requires a reference to the Microsoft ActiveX Data Objects x.x Library
Sequential Numbers
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim lngResult As Long
Dim strCon As String
lngResult = 0 'assume fail
strCon = "Provider=" ''Connection to back-end
cn.Open strCon
rs.CursorType = adOpenKeyset
rs.LockType = adLockPessimistic
rs.CursorLocation = adUseServer
''Where BEInfo is a single line table
strSQL = "SELECT ASeqNumber FROM BEInfo"
rs.Open strSQL, cn, , , adCmdText
'Note this is ADO, so no rs.Edit
rs!ASeqNumber = rs!ASeqNumber + 1
rs.Update
lngResult = rs!ASeqNumber
''This should not happen, but just to be sure
If DCount("ASeqNumber", "Table", "ASeqNumber=" & lngResult) > 0 Then
lngResult = 0
End If
Best Answer
The problem is most likely some
NULL
values. Unfortunately,NULL LIKE 'anypattern'
will not result inFALSE
but inUNKNOWN
andNOT UNKNOWN
is stillUNKNOWN
. So any row where one or more of the values is Null, will not be in the result list becauseWHERE
filters all rows that the condition is anything butTRUE
(FALSE
andUKNOWN
get rejected).Try this change: