Can you out perform ChatGTP?

[Redstick]

I don't know if this is allowed and I'll understand if I'm asked to leave the form. Let me explain.

I am looking for somebody to help me build an Access Database that has brought ChatGPT to its knees. Let me explain. I’m a 72-year-old man who is trying to keep his grey cells working. To this end I decided to try something I’ve never done before and I came up with the idea of building, what to me, seems like a complex database.

Because I’ve never done anything like this before, I started watching You Tube videos, and I bought ‘A Dummies Guide to Access’. However, I was struggling to understand them and because they weren’t ‘interactive’ I decided to try ChatGPT, and I have spent hundreds of hours over the last month working with it but still haven’t got a fully working Database.

So, I’m looking for somebody who is willing to help, but I must tell you I’m surviving on a pension so I’m not able to pay for professional help. I am hoping to find somebody who likes a challenge, has time to spare and is happy to be rewarded with the knowledge they helped an old man out and did something ChatGPT couldn’t.

At my age I’m not looking to become an Access master and will probably never use it again, but I would like to see this database finished.

If any of you are interested, I’ll give you a full breakdown of what I want.

 

[Uncle Gizmo]

This site has helped many such as you...

Post your breakdown here, and it will be reviewed by at least 10 experts, who will help you hone it into a specification. .

From there, it will evolve into a fully functioning application ....

 

[Uncle Gizmo]

Database that has brought ChatGPT to its knees.

In your chat GPT thread, tell chatty that you want to hand the database development off to a forum to seek some professional help.

Ask chatty to provide a summary of your database, explain the issues you are struggling with, and I'm sure chatty will produce a nice start for your engagement on this forum.

 

[Redstick]

Here is the outline information for you - I'll ask ChatGPT for a summary and post that next.

What I’m looking for is a database for a club where the members are unique – each member has an individual First Name (no first name is duplicated) but because they may be member of a family, their Last Name can be duplicated.

Each member record will contain their First Name, Last name, DOB, Gender, Family Relatives (e.g. Mum, Dad, Brother, Sister, Boy Friend, etc.), Job (including Age when started), Group Membership (including Age when joined), Activity records (including Age when started) and activity partners (including Age when started).

I have included the tables used as an attached file.

Each member can have multiple relatives, they can join different groups and undertake different activities which means each member can join multiple groups and each group can have multiple members. Each member can undertake multiple activities, and each activity can have multiple participants.

The way ChatGPT tackled this was to help me make a member record form containing all the data from the Members Table, and four sub forms to show, their relatives, their jobs (including age when started), the groups they belonged to (including age when joined) and one to show the activities they’d undertaken (including age at the time). This activities sub form also has its own sub form to allow the members activity partner to be entered and their age at the time calculated and displayed.

There is only one member table, and all names (member, relative & activity partner) are drawn from the same table.

If this database can be built and function correctly, I would need the following reports to be created.

Reports

• Number of Members by gender
• 
  
• Member Activity Report – Show all members [FirstName], [LastName] who have undertaken an activity (Don’t show members who haven’t yet undertaken an activity). Report to include Member [FirstName], [LastName], [ActivityName], [ActivityDate], Activity Partner [FirstName], [LastName]
• 
  
• 
  
• Activity Report – Show [ActivityName] and list all members who have undertaken the activity, the [ActivityDate] and Partner [FirstName], [LastName]
• 
  
• Jobs Report – Show all members [FirstName], [LastName]
• 
  
• Groups Report – Show [GroupName] and list all members [FirstName], [LastName] & [DateJoinedGroup]
• 
  
• Member Group Report – Show Members [FirstName], [LastName] and list all groups they have joined [GroupName] & [DateJoinedGroup] (Do not show any members who have not yet joined a group).
• 
  
• Member Full Report – Show [FirstName], [LastName], [DOB], [Gender], All Groups they’ve joined, their Jobs, their activities (including [ActivityName], [ActivityDate] & ActivityPartner [FirstName], [LastName]

 

[Redstick]

This is the summary provided by ChatGPT

I have not included details of the Junction Tables referred to in this summary but will do so if that is needed.

Summary of Your Microsoft Access Database​

Your database is designed to track members, their activities, and their activity partners. The key tables and their roles are:

• DataTableMembers (DM) – Stores member details, including MemberID, FirstName, DOB, etc.
• JunctionTableMembersActivities (JMA) – Links members to activities (MemberID, ActivityID, ActivityDate).
• JunctionTableMembersActivityPartner (JMAP) – Links members to their activity partners (JunctionTableMembersActivityPartnerID, MemberID, PartnerID, ActivityID).
• Various queries and forms – Facilitate data entry, record retrieval, and reporting.

Current Issues & Challenges​

1. Data Entry Errors – The JunctionTableMembersActivityPartner.MemberID field is not automatically populated, causing an error when trying to exit the subform.
2. Query Editing Restrictions – Some queries are not allowing new records to be added or existing records to be edited.
3. Joins and Referential Integrity – The relationship setup between JMA and JMAP is causing ambiguous joins, preventing some queries from functioning as expected.
4. Missing Partner Age Calculation – The PartnerAge field is not correctly updating in the subform.
5. Subform Navigation Issues – While selecting a partner works, exiting the subform leads to errors because required fields are not automatically populated.
6. Complex Query Dependencies – Queries linking JMA, JMAP, and DM must allow proper data entry while ensuring necessary fields auto-populate.

Next Steps​

• Find a way to ensure JMAP.MemberID is automatically populated when selecting a partner.
• Fix query joins so that they allow both data retrieval and data entry.
• Ensure the PartnerAge calculation updates dynamically when selecting a partner.
• Maintain a clean and functional subform layout without adding extra forms.

 

[NauticalGent]

In your chat GPT thread, tell chatty that you want to hand the database development off to a forum to seek some professional help.

Ask chatty to provide a summary of your database, explain the issues you are struggling with, and I'm sure chatty will produce a nice start for your engagement on this forum.

Tony, I don't care what the others say about you, you're 'purt near ...

 

[NauticalGent]

Redstick,

You have instinctively grasped what a lot of other visitors to this forum hasn't. The experts that help out here do not do it for money or glory - they do it because they value Access and want to help others get to where they are. Their time is valuable and you have demonstrated that you understand and respect that.

Some of the members here may seem a little short, but at 72, I'm sure you have developed a thick enough skin to deal with that.

Welcome to AWF!

 

[jdraw]

Redstick,

Richard Rost (Access Learning Zone on Youtube) has a series of videos on Association/Organization/ etc
that starts here. It may be a little overkill for your needs, but Richard is a good teacher and watching the videos may help you with context and approach.

 

[Mike Krailo]

@jdraw I just saw that video recently and it seems like appropriate overview. Especially the part on determining the tables involved.

Interesting title. It does appear that ChatGPT gave a lot of insight into the proper table structure, but what it doesn't do is teach you access. There is just a lot things you have to learn by doing and experimenting. With everyone's help hear, they can clear up any troubles you may be having pretty quickly.

I'm guessing you already have a database you are working with, why not post it in another thread or here so we can all look it over to ensure that you are on the right track. I have high hopes for this one.

 

[CJ_London]

The odd thing to me is no duplicate first names

(no first name is duplicated

Why?

 

[The_Doc_Man]

Your database is similar to my ancestry database. Here is the basic idea as I see your issues...

1. Table of people - everybody is in this table ONCE - and once only. Here is where you keep details of people like birth date, name, address, phone, anything else that is unique to the person and is potentially different in other persons. In my genealogy case, each person has a numeric record number which is the prime key (PK) of the table. That way if it ever happens that you DO get a duplicate first name, the record still depends on the number, not that name. Since names are arbitrary, you never know when a duplicate will pop up. And if someone gets a wild hair and changes their name for some nefarious or other reason, you can change the name in this record and leave the key the same.

2. Table of relationships - you have fields for person A key (number), person B key (number), and TWO relationships. This table is bidirectional so there are wrinkles to it. You have a field for the A-to-B relationship. You have another field for the B-to-A relationship. So samples of the two relationships are husband & wife, father & daughter, paramour and cheating wife, etc. This is what is called a junction table because it joins two people together and defines the reason they are joined. If a person has 10 children in the club, they will have 10 records (or 11 if the wife is also a member). If a person is single and childless, no records here.

You aren't doing genealogy, so one of my tables might not matter to you. I have a family table that corresponds to a marriage. Relationships such as parent and child are associated with a family number. If the parents divorce and marry someone else, they get new family numbers. The family number is a field in the table of relationships.

3. Table of possible activities - you list the things people can do. Tennis, Bridge, Running, Chess, Book Club, etc. Usually, this would also have a numeric code key, perhaps even autonumbered.

4. Table of activity members - a simple junction table that lists a person ID and an activity ID. There would be one record for the combination of a person and an activity. This allows persons to have multiple activities and an activity to have multiple participants.

Your reports now can be based on several sources to "drive" the data gathering.

For lists of people, you report based on the people and can sort that table by the key, the first name, or <last name, first name> or ... heck, on eye color if that floats your boat. If you want the person and the list of relationships to that person, you JOIN the person table to the person A field of the relationships table and you list the relationships for the A-to-B case. Here you would do a GROUP BY of the person ID to keep the relationships together. To include the people's names for the relationship you might have to develop a sub-query or a layered query, but it is not that hard.

For lists of people in a particular activity, you JOIN the activity table to the person table with a GROUP BY to show who is doing what. For another example, you could do a filtered JOIN of the persons/relationships tables looking only at the relationship of "paramour" to show who is doing whom. (Just kidding - but it IS meant to be illustrative.)

This is similar to what works for me and my 2200-member family tree that goes back to England, Ireland, France, Spain, and Canada and in a couple of cases, as far back as early 1600s. Though I have some issues at the moment that I think are data related from getting some bad data sources, I can explore and traverse my family tree. Actually, this is for my grandkids to know where THEY come from. But you get the idea.

You have to understand that sometimes, in the scheme I have, a given list will be empty - like the list of relationships for a person who has no spouse or children - and so must take into account a possible null return grouping. Which means that your LEFT JOIN cases that link to activity or family-group options MIGHT have nulls somewhere. Therefore, when dealing with LEFT or RIGHT joins, you will sometimes have to recognize that the partial answer to your grouping is "no members."

 

[Redstick]

The odd thing to me is no duplicate first names

Why?

The no duplicate first names comes from a previous project using excel where last names weren't used and I just carried that forward when I started my database.

 

[Redstick]

Your database is similar to my ancestry database. Here is the basic idea as I see your issues...

1. Table of people - everybody is in this table ONCE - and once only. Here is where you keep details of people like birth date, name, address, phone, anything else that is unique to the person and is potentially different in other persons. In my genealogy case, each person has a numeric record number which is the prime key (PK) of the table. That way if it ever happens that you DO get a duplicate first name, the record still depends on the number, not that name. Since names are arbitrary, you never know when a duplicate will pop up. And if someone gets a wild hair and changes their name for some nefarious or other reason, you can change the name in this record and leave the key the same.

2. Table of relationships - you have fields for person A key (number), person B key (number), and TWO relationships. This table is bidirectional so there are wrinkles to it. You have a field for the A-to-B relationship. You have another field for the B-to-A relationship. So samples of the two relationships are husband & wife, father & daughter, paramour and cheating wife, etc. This is what is called a junction table because it joins two people together and defines the reason they are joined. If a person has 10 children in the club, they will have 10 records (or 11 if the wife is also a member). If a person is single and childless, no records here.

You aren't doing genealogy, so one of my tables might not matter to you. I have a family table that corresponds to a marriage. Relationships such as parent and child are associated with a family number. If the parents divorce and marry someone else, they get new family numbers. The family number is a field in the table of relationships.

3. Table of possible activities - you list the things people can do. Tennis, Bridge, Running, Chess, Book Club, etc. Usually, this would also have a numeric code key, perhaps even autonumbered.

4. Table of activity members - a simple junction table that lists a person ID and an activity ID. There would be one record for the combination of a person and an activity. This allows persons to have multiple activities and an activity to have multiple participants.

Your reports now can be based on several sources to "drive" the data gathering.

For lists of people, you report based on the people and can sort that table by the key, the first name, or <last name, first name> or ... heck, on eye color if that floats your boat. If you want the person and the list of relationships to that person, you JOIN the person table to the person A field of the relationships table and you list the relationships for the A-to-B case. Here you would do a GROUP BY of the person ID to keep the relationships together. To include the people's names for the relationship you might have to develop a sub-query or a layered query, but it is not that hard.

For lists of people in a particular activity, you JOIN the activity table to the person table with a GROUP BY to show who is doing what. For another example, you could do a filtered JOIN of the persons/relationships tables looking only at the relationship of "paramour" to show who is doing whom. (Just kidding - but it IS meant to be illustrative.)

This is similar to what works for me and my 2200-member family tree that goes back to England, Ireland, France, Spain, and Canada and in a couple of cases, as far back as early 1600s. Though I have some issues at the moment that I think are data related from getting some bad data sources, I can explore and traverse my family tree. Actually, this is for my grandkids to know where THEY come from. But you get the idea.

You have to understand that sometimes, in the scheme I have, a given list will be empty - like the list of relationships for a person who has no spouse or children - and so must take into account a possible null return grouping. Which means that your LEFT JOIN cases that link to activity or family-group options MIGHT have nulls somewhere. Therefore, when dealing with LEFT or RIGHT joins, you will sometimes have to recognize that the partial answer to your grouping is "no members."

You're right there are a lot of similarities. I also have a large family tree (40k +) which is stored on Ancestry and in my Family Tree Maker files, but I must admit I've not done any research for a couple of years now. I will be posting a copy of my database on here shortly so you will be able to see how far me and ChatGTP have got.

 

[Redstick]

Attached is a zip file of my database

 

[Mike Krailo]

The biggest glaring error I see in the tables is DataTableMembers is missing the ParentID field that should be linking to DataTableMembers_1. The only purpose of that copied table is for establishing the ParentMemberID or simply the ParentID. There should be no other links to that copied table. Much more to come as others come to your rescue.

 

[Redstick]

The biggest glaring error I see in the tables is DataTableMembers is missing the ParentID field that should be linking to DataTableMembers_1. The only purpose of that copied table is for establishing the ParentMemberID or simply the ParentID. There should be no other links to that copied table. Much more to come as others come to your rescue.

View attachment 118902

There is no ParentID used it is PartnerID which is used to identify the member's partner in the activities.

 

[Mike Krailo]

There is no ParentID used it is PartnerID which is used to identify the member's partner in the activities.

You can name it whatever you like, the point is how you are supposed to use it. The FK PartnerID should be in the main DataTableMembers table. The ID in the copy of your main table links back to that PartnerID in the main table. This is only one aspect of your table structure that I'm pointing out. There are many more to come I'm sure.

If there is only ever going to be one and only one partner and they will always reside in the same residence, then you can forgo the hierarchical design and put that info right into the main members table. I'm guessing that you would want that flexibility though. Can a Partner live in a different address? Do you want to track family members?

 

[AccessBlaster]

Redstick, in the upper left corner of ChatGPT, there is a button labeled "Explore GPTs." When you click that button and scroll all the way to the bottom, you will find a programming section. There are many AIs designed for coding. If you are coding in Access VBA, you need to specify that to the AI.

Hope this helps.

 

[Redstick]

You can name it whatever you like, the point is how you are supposed to use it. The FK PartnerID should be in the main DataTableMembers table. The ID in the copy of your main table links back to that PartnerID in the main table. This is only one aspect of your table structure that I'm pointing out. There are many more to come I'm sure.

If there is only ever going to be one and only one partner and they will always reside in the same residence, then you can forgo the hierarchical design and put that info right into the main members table. I'm guessing that you would want that flexibility though. Can a Partner live in a different address? Do you want to track family members?

View attachment 118904

I will accept your superior knowledge and experience. The Tables and their relationships are the way ChatGPT told me to build them. The partner is somebody who undertakes an activity with another member, they are not always family members and usually do reside at a different address. Members are tracked as individuals, but the use of their Last name can be used to identify families.

 

[Redstick]

Redstick, in the upper left corner of ChatGPT, there is a button labeled "Explore GPTs." When you click that button and scroll all the way to the bottom, you will find a programming section. There are many AIs designed for coding. If you are coding in Access VBA, you need to specify that to the AI.

Hope this helps.

Thanks for the advice but VBA, SQL or any form of coding is not part of my skill set and just confuses me more than I already am.