I'm creating a swim meet management project (using Base), I need help with making relationships between certain values

[zulujive]

Okay, so here’s the idea:

• The data about what lane, heat and event are entered about a certain swimmer.
• I want this data to go into the table meant for time entry (so when the swimmer finishes, all you have to do is enter their ID number and the information that already exists is automatically entered)
• How do I do this?

I’ve tried relationships but they don’t like to work for some reason and I’d appreciate using plain english here (please explain something if you don’t think somebody who only knows about three HTML tags). I’d also like to know how to make the database automatically count up for me when I enter a swimmer’s information. For example, if there are 6 lanes, then the 7th swimmer will be put in the 2nd heat. This would be incredibly helpful as I’m thoroughly confused right now but I also have hope because I was able to make it this far and while this system would work, it would be incredibly inefficient. If you’d like to see my files, they’re on this github page GitHub - zulujive/MySwimDB: MySwimDB is a database dedicated to managing swim meets. It's free to use by anyone, and requires only some basic technical knowledge,

I know I’m a script kitty in every sense, but I feel like this should be something simple and I just don’t have the technical experience with databases.

[Ratslinger]

Hello,

With a short look, it appears you will need to expand upon this.
There is no method to have this used on different dates unless you wipe out previous data and start all over.
Would think that for continued use an additional table for events, one for people/entrants/teams (so no duplicate entry needed) and probably a lot more.
I would see this process as having a form where you select an event for a date then add entrants to this event. When event is finished, another form to select the event and add the results.
.

@zulujive

I want this data to go into the table meant for time entry (so when the swimmer finishes, all you have to do is enter their ID number and the information that already exists is automatically entered)

This is very unclear as from what is shown, and with you stating to just enter an ID, how is the time entered or the DQ?
.
There is nothing in the sample you have needing relationships but the expansion as vaguely outlined will require this.
.
Need more detailed steps on how this is to work for your use.

[zulujive]

Okay, so all I’m trying to do here is to essentially mark a record with an ID (just as the primary key). So, as a shortcut, somebody who has the ID can just enter an ID into the form and the form automatically fills out the rest of the data. Similar to using variables in code to represent data, an ID would just represent a record that can be put in with the ID number rather than copying the data onto the form. For clarity here, in swim meets there are time cards and all the swimmer’s information is on them. Somebody will grab those time cards and take them to the booth where they’re recorded. If I can just have the ID on the time cards, it’ll make the process a lot faster where you can simply search for the ID and the corresponding data on one table is copied on a new record in another table.

There are two tables:

1. The events table where a list of all the entrants and their corresponding events, heats and lanes are
2. The time table, where times, event, name and DQs are stored.

The reason I have two tables is so that the event table can act as a template for the time table. Only about half the information is shared between the tables, the rest is only specific to that table. For example, my events table will have more detailed information about lanes and heats while the time table doesn’t need that because rankings are based on events and nothing else. The ID acts as a kind of copy-paste sort of thing where the already existing information is copied onto the time entry form and only the time has to be recorded. DQs will be recorded on another table because that table will have a more detailed explanation as to why the swimmer was disqualified. I’m also curious how I would say “if x swimmer is put into the DQ table, then they will be marked as DQ on the time table”. Again, I just need to know how to use the primary key to copy over the data (which are EVENTID, and name) to another table to make things easy rather than having to reenter the data that already exists and is organized by a unique ID.

[Ratslinger]

When you talk of “copying” records, first this is duplicating data (let’s ignore this part) but most importantly it would require coding macros which is a different undertaking.
.
Sorry, but I think there is a great deal more to be done. I see an Events table (no entrants here), an entrants table (no events here) and at least another table for heats (maybe even a linked table for event date) where pre-race this is created and after the finish just complete the remaining data. No copying/duplication of data. Events & entrants are placed in a ‘heat’ table using list boxes so spelling is not an issue.
.
Again, think this is just the surface.

[zulujive]

I’m not asking about whether or not I can do it, I’m asking how to. I shouldn’t have even provided the details, it’s obvious that people aren’t capable of helping me without getting distracted about the small and insignificant details. All I’m asking is how do I make some macros that takes one record and copies it into another. Also, I figured it out on my own no thanks to you. I just combined it into one table, it was literally that simple. You ask how I enter a DQ, how is that even relevant? As if I’m not being clear enough, you literally said I should just ignore the most important thing that I was asking about. After a while it just gets exhausting talking to people who don’t seem to get that I’m just looking for somebody to walk me through how I go about taking two respective fields and copying one over to the other. I’m not even going to try asking here again, you all don’t seem to understand how to be helpful.

[James_Zimmerman]

@zulujive, please don’t give up on using a database. It can work for you much better than your “one table” solution.

What you need to do is split up your data into separate tables and then set up a relationship between the tables. Once that is done you can create an input Form that connects everything together and makes data entry easy.

Here are some suggestions for tables and columns in each:
Swimmer: SwimmerID, SwimmerName
Meet: MeetID, MeetDescription, MeetDate
Event: EventID, EventDescription
Lane: LaneID, LaneNumber
Contest: ContestID, MeetID, EventID, SwimmerID, LaneID, FinishTime, Place, DQ

You will have very few records in Swimmer, Meet, and Event, but LOTS of records in Contest. The columns in each table begin with an “ID” field which is the PrimaryKey for that table. When you see an “ID” field that is not listed first, it is a Foreign Key field, which means that record relates to one of the other tables with a matching PrimaryKey.

The Form you create will be based on the Contest table and in order to fill in the MeetID, EventID, LaneID, and SwimmerID you will create a ListBox field that shows the names or descriptions, but records only the foreign key in the Contest table. At this time I don’t think this will require any Macro code.

This example is rather crude since I just “thought it up” while writing, but it gives you an idea how to make your data entry task go a lot faster by using a database rather than a single table (spreadsheet?). Later you can create queries and reports that show things like individual Swimmer records, or list the winners of each race (Contest).