Can you out perform ChatGTP?

[jdraw]

I see it's genealogy rather than association/clubs/membership. So here is another Richard Rost link.

 

[Redstick]

Thanks for the link, but I'm not looking for a Genealogy database, that just came up in another post. What I'm looking for is based on club membership.

 

[LarryE]

I believe you complicating this thing out of proportion. It sounds like:

1. There are multiple Member Types in this club.
2. Each Member Type has multiple Members.
3. Each Member may participate in multiple Activities.
4. Each Activity may have one or more Activity Dates.
5. With each Activity Date there could be one or more Activity Partners.

Can you confirm this?

 

[Redstick]

Not sure what you mean by Member Types. Obviously the club has multiple members. Each member can belong to multiple groups. Each member can undertake multiple activities and all activities will happen on different dates. When undertaking an activity, each member can have one or more activity partners. For example if you think about sports activities. Each member can belong to different groups, e. g. a football team and a cricket team as well as maybe a swimming team and a chess group. Each event will happen on different dates (like football fixtures) Each group (team) will have multiple members. During each activity, the member will have at least one partner (chess) or more likely multiple partners (teammates in football etc).

I hope this makes sense.

 

[Mike Krailo]

I put the MembershipType in the mix assuming there were different types of members. If all members are on the same exact level, then no, you wouldn't need that.

It looks like it's free to become a member as I do not see any dues or fees of any kind in your original example. Mind telling us what kind of membership this is? I see the activities table explains more what the members do. So it's sports/games. My question is how does one become a member? Is there a signup sheet that folks just put their info on, or they request it via email? I assume there are no dues of any kind for this type of membership. So the whole partner thing is based on the type of game being played. Are you tracking scores of the games as well?

This seems more like a game players event planning database to me. So people signup to be a part of the games and then the game events are organized so players are assigned to teams (you call them groups). The teams then play at the events and I assume there would be winners and losers. Do the members play for prizes or money, or is it strictly for fun?

 

[DickyP]

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.

Given your age (youngster compared to lot of us here) I have real sympathy. Having programmed in at least 12 languages for more than 40 years, I forget how confusing how something that is natural to many of us can be. My advice is to start slowly with a simple printed book and practice. Most importantly get thing wrong!

 

[Redstick]

I put the MembershipType in the mix assuming there were different types of members. If all members are on the same exact level, then no, you wouldn't need that.

It looks like it's free to become a member as I do not see any dues or fees of any kind in your original example. Mind telling us what kind of membership this is? I see the activities table explains more what the members do. So it's sports/games. My question is how does one become a member? Is there a signup sheet that folks just put their info on, or they request it via email? I assume there are no dues of any kind for this type of membership. So the whole partner thing is based on the type of game being played. Are you tracking scores of the games as well?

This seems more like a game players event planning database to me. So people signup to be a part of the games and then the game events are organized so players are assigned to teams (you call them groups). The teams then play at the events and I assume there would be winners and losers. Do the members play for prizes or money, or is it strictly for fun?

This is an exercise to help me understand Access databases. It's a way for me to keep my mind active. it isn't a real club, so there are no fees, no membership requirements and no score keeping or prize giving. I had an idea that I thought would challenge me and I started to try and learn by watching the you tube videos that have been mentioned earlier and by reading an Access for Dummies book, but I got so confused I started working with ChatGTP so I could interact and question the progress we were making, but it eventually became clear that I was just going around in circles and my database was getting more and more messed up. I never give up on a project I start and I'm hoping that with the help of this forum, I can see a finished and fully functional database that does what I want it to do.

 

[LarryE]

This is an exercise to help me understand Access databases. It's a way for me to keep my mind active. it isn't a real club, so there are no fees, no membership requirements and no score keeping or prize giving. I had an idea that I thought would challenge me and I started to try and learn by watching the you tube videos that have been mentioned earlier and by reading an Access for Dummies book, but I got so confused I started working with ChatGTP so I could interact and question the progress we were making, but it eventually became clear that I was just going around in circles and my database was getting more and more messed up. I never give up on a project I start and I'm hoping that with the help of this forum, I can see a finished and fully functional database that does what I want it to do.

OK no problem, but let's just imagine that you wanted to construct a database like the one I imagined in post #25. It's pretty simple as ACCESS designs go frankly. The table design could look like this:

Do you see how table relationships work? If you don't have membership types, then you don't include the TypeID Foreign Key in the Member table and don't include the MembershipType table. But you still have members participating in one or more activities in multiple dates with one or more "partners". Get it? Each piece of information is stored in only one location and tables are related to one another using Primary and Foreign Keys. Get those two ideas firmly implanted in your head and you have 99% of relational designs.

Also note that the ActivityPartner table also has a Member Foreign Key just like the Activity table. That's because the ActivityPartner table is a Junction table. It brings together two Foreign Keys from the ActivityDate table and the Member table, so each member can have multiple "partners" on multiple activity dates.

And I'm older than you by the way. Turn a Rod Stewart CD up and have fun!

 

[The_Doc_Man]

Here is some advice to help you learn Access in a more traditional way.

First, you define the problem - before you write code or design a table, form, or report. Access SHOULD NOT BE OPENED ONCE until you have a solid definition of what you want to do. Think of the problem analysis as a type of road map. If you don't know where you are going, how will you EVER know when you got there?

Second, remember this important fact: Access is as DUMB AS A BOX OF ROCKS. The ONLY thing that Access really does well is to build database objects like tables, queries, forms, reports, macros, and modules. And if you TELL it about inter-table relationships, it will remember them when you build queries involving related tables.

Third, Access will NEVER tell you anything you didn't tell it first - either directly or "how to derive it." YOU are the person who knows what you wanted to do. We don't know, and Access is based on an inanimate object. Therefore, if you have to ask "Now where does this data originate" then you are probably on your own.

Fourth, just remember that with Access, you are building a data model of a (potentially) real-world entity. If the real world says X and the data model says Y then odds are very high that the data model is flawed. In chemistry (my original field), when the theory says one thing and the experiment says something else, the theory is wrong. The database cannot correct you on anything except basic math errors. The tail never wags the dog.

When making your mental exerciser, you learn by doing. Every so often, like maybe before you start any real work that would change your DB, make a backup copy. Like, if you have a DB named "A" then make a copy of A to a set-aside folder and rename A to A_03112025 - or something like that. As you progress, you can delete the older backups, but always keep at least a couple of generations handy. Then you won't end up like me with a hairline that as receded so far from hair-tairing you would think that someone gave the order to retreat.

 

[Mike Krailo]

I'll just wait for @Redstick tell us what the parameters of fully functional actually means before going any further.

 

[Redstick]

I'll just wait for @Redstick tell us what the parameters of fully functional actually means before going any further.

I mean that I have a database that is easy to use and does what I want it to do with no errors. Remember I'm completely new to Access so may have to rethink my expectations depending on what I'm told is achievable and what isn't.

 

[The_Doc_Man]

Remember I'm completely new to Access so may have to rethink my expectations depending on what I'm told is achievable and what isn't.

What is achievable is limited by your own limits to your imagination and your own limits on how much you are willing to study to learn the tricks that Access can do. Remember, it isn't sentient. It is far from perfect. But it is incredibly versatile for a product with a nominally dedicated purpose.

 

[gemma-the-husky]

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]

Whoa. Stop right there. It's a minor thing but I doubt anyone would design a system where a given name is unique, or even a given name plus surname is unique. Would you refuse membership to a second Jim? Even if you don't have a duplicate you wouldn't assume you would never have one.

Anyway, I'm sure others have explained- get the data structure right, first and the reports you mention are very easy to do, as are the forms to manage the data.

However give every member a numeric ID, and link the activities to the member ID, not the member name. When (not if) you get a duplicate and Jim Smith introduces his son Jim Smith to the club, your system (and you) need a way to distinguish Jim Smith Sr from Jim Smith Jr and even Jim Smith no relation. It's actually quite hard to do this, which is why SS numbers or similar (even a simple Club ID Card) become so important, because a reliable third party is effectively guaranteeing the correct identification.

 

[Redstick]

I appreciate your comments but the point is this is an exercise to help me learn how to use Access. This not a real club, there will NEVER be two Jim's or two Susan's. Each member is identified by their own unique MemberID (Indexed No Duplicates), so there will never be a problem.

In the database I'm building with ChatGPT, we've got everything working apart from being able to assign partners to members when undertaking activities. Members and Partners are drawn from the same 'MembersTable' with MemberID coming from MembersTable and PartnerID coming from 'MembersTable_1' ChatGPT lost me when it started using sql to rename MemberID from 'MembersTable_1' to PartnerID. It kind of works but there are problems getting Access to assign Partners to multiple activities and with the same member.

 

[gemma-the-husky]

Even if it's academic it would be better to use the numeric ID to manage club members. In practice you would prevent duplicate names by adding a unique index. So if you created an index based on surname plus given name, and made that index unique, then access would report an error if you tried to create multiple Jim Smith persons. But you could create a Jim Smith and a J Smith. You might as well do it properly as not though, so design the system that doesn't prevent you adding two persons named Jim, as it's easier than trying to prevent the duplicate name.

There's two ways of dealing with partners. I take it partner in this sense is an activity partner, and not a relationship partner.

It's actually tricky, as you need a table that stores a partnership ID, an activity code, a partner1 (person ID), and a partner2 (second person ID)
That way for activity 1, you know that the partners are persons 27 and 46 say.
However, the problem is that you need to check both partners to check whether a particular person has been partnered.

Instead you can have the table designed as partnershipID, activity Code, partnerID
But now you have two partnerIDs, for a given partnership. So you can check whether a person is in a partnership for a given activity, but then you need another read to determine who his partner is.

If you store the corresponding partnerIDs IN the persons table it's more limiting as a person can then only have one active partner, and that might not be the correct real world situation.

I don't think there is an easier way to handle this, but there might be.

It comes back to thinking carefully about how the relationships actually connect in your system.

But try different things, and see what works and what doesn't. It just takes some time and experience to get used to designing a system that works how you want it to.

 

[LarryE]

One more thing. AI can't ask the right questions that will set you on the correct path like we can and have. A good consultant always asks the right questions first.

 

[Redstick]

Okay folks, I’ve abandoned ChatGPT and built my database myself and I’m amazed I’m only one step away from finishing, so I’m hoping somebody may be able to help. I’m sure you’ll want to tell me I could or should have done things differently, but what I’ve done so works.

The situation is this. I’ve got a Members Form that contains 3 functioning sub forms, but I’m having difficulties with the fourth one because it also contains its own sub form and I’m having trouble linking the two.

The fourth sub form records the member’s Activities – (Activity Name, Activity Date & member age at Activity Date) – Now all of that works in the Members Form but within that sub form I want to add another sub form to record the member’s Partner details. All I need to record are the Partner’s Name and their age at the activity date. I have built a sub form to do that but the problem I’m having is linking it to the member sub form so that each of the members activities can have multiple partners. I have tried several Master/Child links, but nothing works. The sub forms are built on queries from the appropriate Junction Tables.

If you need any images of anything, let me know what you want to see and I’ll post them

 

[Mike Krailo]

I’m sure you’ll want to tell me I could or should have done things differently, but what I’ve done so works.

Yes, and since it works for you, then there isn't anything you need help with. You got it all figured out.

 

[The_Doc_Man]

each of the members activities can have multiple partners

From your description including but not limited to that statement, I think you need to look up what are called JUNCTION TABLES.

It would assume that a member's partner is ALSO a member. Your junction table will include not less than one slot for the member's ID, one slot for the partner's ID, one slot for an activity code, and MAYBE (if this applies) a slot to make the partnership unique IF the member has more than one partner for a given activity. If you have multi-partner teams (i.e. >2 members) this still works if you have team ID (for example).

 

[GaP42]

I have a different view on the relationships: as stated by Redstick it is not a genealogy database, however associations between members need to be supported to allow recognition of partnerships - meaning generally teams. Family associations between members (may) also need to be recognised. Without going into the detail, the following supports these requirements.

I know that there is no stated requirement for attendance at an event / GroupActivity. Just threw it up as there is nothing to say because you are a member of the team and the team played that a particular member participated on that occassion. A similar arrangement is used to track attendance at scheduled meetings.